Performance optimization strategy in Oracle Database design and development stage

Introduction

Oracle is the most widely used large-scale database management system, improve the efficiency of Oracle database system, is the premise and guarantee of the efficient operation of the whole computer information system. There are many factors affecting the performance of Oracle database application system, including software factors, hardware environment, network environment, database management and maintenance factors. The design and development stage of database system is the best stage of Oracle Application optimization, and it is also the active optimization stage, which can achieve the maximum performance gain with minimum cost. By optimizing its logical storage structure and physical storage structure, the time and space overhead performance is optimal under the condition of satisfying the requirement, which can solve the problems such as the gradual descent of performance during the operation of database system and the sudden drop of performance, so as to ensure the excellent performance of the system operation.

Logical structure and physical structure of Oracle database

The logical structure of an Oracle database consists of a number of database objects, such as database table spaces, tables, indexes, segments, views, stored procedures, triggers, and so on. The logical storage structure (table space, etc.) of the database determines how the physical space of the database is used, and the database objects such as tables and indexes are distributed in each table space.

The physical structure of an Oracle database is viewed from the operating system level and is composed of files, which are physically divided into data files, log files, control files, and parameter files. The data file holds all the data information, the log file holds the log information generated during the database running, it is reused, and if it is not archived, the logged information that has been overwritten will not be restored; the control file records the key structure information of the entire database, and if it is corrupted, The entire database will not work and recover; The parameter file has many configuration parameters set up in the Oracle database that will be read when the database is started.

Optimization of logical Structure

The logical structure optimization in layman's terms is to increase the efficiency of the application by increasing, reducing or adjusting the logical structure, the following is to analyze the optimization of Oracle logic structure by the design of basic table and the discussion of index and cluster.

1, Basic table expansion

Database performance includes two aspects of the size of storage space requirements and the length of query response time. To optimize database performance, you need to normalize the tables in your database. Generally speaking, logical database design satisfies the table structure of the third normal form, which is easy to be maintained and satisfies the requirement of practical application. Therefore, the actual application is generally in accordance with the standard of the third normal form, so as to ensure the consistency and integrity of the database, designers tend to design too much of the table association, in order to reduce data redundancy as much as possible. But in practice this practice is sometimes not conducive to the optimization of the performance of the system: such as the process of fetching data from multiple tables caused a large number of connection operations, when the need for partial data to scan the entire table, which consumes the disk I/O and CPU time.

In order to solve this problem, in the design of the table should be considered at the same time to counter the normalization of some tables, there are several methods: first, the partition table. Split table can be divided into horizontal split table and vertical split table two: Horizontal segmentation is to split a table into multiple tables, which can improve the query speed of each table, but query, update to select a different table, when the statistics to summarize multiple tables, so the application will be more complex. Vertical segmentation is a table with a lot of columns, and if some columns are accessed much more frequently than others, you can use the primary key and the columns as a table, and the primary key and other columns as a different table. By reducing the width of the columns, increasing the number of rows per page of data, one I/O can scan more rows, thereby increasing the speed of accessing each table. However, because of the multiple table joins, it should be used when querying or updating columns in different partitioned tables in a relatively small amount of time. The second is to keep redundant columns. When two or more tables often need to be connected in a query, you can add several redundant columns to one of the tables to avoid too many connections between tables, which are typically used in cases where redundant columns of data are infrequently changed. The third is to increase the derived columns. Derived columns are computed from other columns in the table, and adding derived columns can reduce statistical operations, which can greatly shorten the computation time when the data is aggregated.

Therefore, in the design of the database, data should be organized in two categories: frequently accessed data and frequently modified data. For frequently accessed but infrequently modified data, internal design should be physically not normalized. For frequently modified but infrequently accessed data, internal design should be physically normalized. It is sometimes necessary to base the normalized tables on the logical database design, and then physically normalize the data according to the needs of the entire application system. Both the specification and the anti-norm are the constraints based on the actual operational basis, which are not meaningful either. Only by combining the two rationally can we complement each other and play our respective advantages.