Performance Optimization Strategies for Oracle databases in the design and development phase

Oracle Database is now the most widely used large-scale database management system. Only by improving the operational efficiency of the Oracle database system can we ensure the efficient operation of the entire computer's information system. There are many factors that affect the performance of Oracle database application systems, including software and hardware environment, network environment, database management and maintenance.

The database system design and development stage is the best stage for Oracle application optimization, and is also the active optimization stage, which can achieve the goal of maximizing performance gain at the minimum cost. By optimizing its logical and physical storage structure design, the time-space overhead performance is optimal when the requirements are met, it can solve the problem of progressive or sudden performance degradation during database system operation, so as to ensure the excellent performance of the system operation.

Logical and physical structures of Oracle databases

The logical structure of an Oracle database is composed of some database objects, such as the tablespace, table, index, segment, view, stored procedure, and trigger of an Oracle database. The logical storage structure (tablespace, etc.) of the database determines how the physical space of the database is used. database objects such as tables and indexes are distributed in each tablespace.

The physical structure of the Oracle database is viewed at the operating system level and consists of files, which can be physically divided into data files, log files, control files, and parameter files. All data information is stored in the data file. The log file stores the log information generated during database operation and is repeatedly overwritten. If archive is not used, the overwritten log information cannot be restored;

The control file records the key structure information of the entire database. If it is damaged, the entire database will not be able to work and be restored. The parameter file contains many configuration parameters for the Oracle database, this information is read when the database is started.

Logical Structure Optimization

In general, logical structure optimization increases application efficiency by adding, reducing, or adjusting logical structures, the following describes how to optimize the logical structure of ORACLE by designing basic tables, indexing, and clustering.

1. Basic table Extension

Database performance includes the size of the storage space required and the length of query response time. To optimize the database performance, you need to standardize the tables in the database. In general, the logical database design meets the third paradigm. The table structure is easy to maintain and basically meets the requirements of practical applications.

Therefore, in practice, the standards of the third paradigm are generally standardized to ensure the consistency and integrity of the Oracle database. Designers often design too many associations between tables, to minimize data redundancy. However, in practical applications, this approach is sometimes not conducive to the optimization of system operation performance. For example, a large number of connection operations are triggered when the process obtains data from multiple tables, and the entire table needs to be scanned when part of the data is required, this consumes disk I/O and CPU time.

To solve this problem, we should also consider anti-normalization of some tables when designing tables. There are several methods: first, table segmentation. Split tables can be divided into horizontal split tables and Vertical Split tables: horizontal split is divided into multiple tables according to the rows in a table, which can improve the query speed of each table, however, you need to select different tables for query and update, and multiple tables should be summarized during statistics, so the application will be more complex.

Vertical segmentation is used for tables with many columns. If some columns are accessed more frequently than other columns, the primary key and these columns can be used as a table, use the primary key and other columns as another table. By reducing the column width and increasing the number of rows on each data page, I/O can scan more rows at a time (similarly, memory-based page access ), this increases the access speed to each table.

However, due to the multi-Table connection, it is recommended that you query or update columns in different split tables at the same time. The second is to retain redundant columns. When two or more tables need to be connected frequently in queries, you can add redundant columns to one of the tables to avoid too frequent connections between tables, it is generally used when the data in the redundant column is not changed frequently. Third, add a derived column.

A derived column is calculated by multiple other columns in the table. Adding a derived column can reduce the statistical operation. Is it better to reduce the calculation time through periodic carry-over during data aggregation ?).

Therefore, in the database design, data should be organized in two categories: frequently accessed data and frequently modified data. For data that is frequently accessed but not frequently modified, the internal design should be physically nonstandard. For data that is frequently modified but infrequently accessed, internal design should be physically normalized. Sometimes it is necessary to use a standardized table as the basis for the design of the logical Oracle database, and then physically non-standardized data based on the needs of the entire application system.

Both standards and anti-standards are constraints based on actual operations, and they are meaningless. Only by reasonably combining the two can they complement each other and give full play to their respective advantages.

2. indexing and clustering

Creating an index is one of the most effective ways to improve retrieval efficiency. The index maps the logical values in a table to a secure RowID, which can quickly locate the physical address of the data and greatly speed up database query, A database application system with a reasonable index may be dozens of times more efficient than a database application system without an index, but the more indexes, the better, creating a cable reference for data columns that often need to be modified will result in the continuous reorganization of the index B * tree, resulting in a reduction in system performance and a waste of storage space.

Indexes created for a large table sometimes do not improve the data query speed, but affect the performance of the entire Oracle database. This is mainly related to the data management method of SGA. When Oracle performs data block high-speed cache management, index data has higher resident permissions than normal data. In space competition, oracle removes common data first.

When you query data in a large table with an index, the index data may use up all the data block cache space. Oracle has to perform disk read/write frequently to obtain data, after partitioning a large table, you can create a partition index based on the corresponding partition.

Oracle provides another method to increase the query speed, that is, clustering ). In simple words, clustering stores several tables together and stores them in a mixture of public attributes. Clustering stores data from multiple tables in the same Oracle block based on the common code value. Then, when a group of Oracle blocks is retrieved, the data from both tables is obtained, in this way, the Oracle block to be stored can be reduced to improve the application performance.

To optimize the logical structure, table data and index data should be stored in separate tablespaces, respectively using independent tablespaces. If table data and index data are put together, the I/O operations of table data and index I/O operations will affect the system performance, reduce system response efficiency. You can avoid this competition by storing table data and index data in different tablespaces and storing the data files of these two tablespaces on different physical disks.
 

Article by: http://www.programbbs.com/doc/3414.htm