Oracle Database Design and development stage performance optimization strategy

Introduction
Oracle is the most widely used large-scale database management system. It improves the operating efficiency of the Oracle database system and is the prerequisite and guarantee for the efficient operation of the entire computer information system. There are many factors that affect the performance of Oracle database application systems, including both software and hardware environment, network environment, and 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 database tablespace, tables, indexes, segments, views, stored procedures, and triggers. 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-down structure information of the entire database. If it is damaged, the entire database cannot work and be recovered; many Oracle database configuration parameters are set in the parameter file. 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 Database Consistency and integrity. Designers often design too many table associations, 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 Computation and greatly shorten the computation time during data aggregation (isn't it better to perform periodic carry-over ?).

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 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 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 will first remove normal data and query the data of large tables with indexes, the index data may use up all the data block cache space, oracle has to perform disk read and write frequently to obtain data. Therefore, 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.

Physical Structure Optimization

Database Data is ultimately stored on physical disks. Access to data is read and write to these physical disks. Therefore, optimization of these physical storage is an important part of system optimization. The Optimization of the physical storage structure is mainly to rationally allocate the physical storage address of the logical structure. Although this cannot reduce the number of reads and writes to the physical storage, it can make these reads and writes as parallel as possible, reduce disk read/write competition to improve efficiency. You can also perform precise computing on physical storage to reduce unnecessary physical storage structure expansion, thus improving system utilization.

1. parallel disk read/write Optimization

For physical reads and writes to databases, the Oracle system will optimize the database in parallel as much as possible. For example, in the simplest table retrieval operation, if the table structure and the index in the retrieval domain are not in the same physical structure, the retrieval of the index and the retrieval of the table are performed in parallel during the retrieval process.

2. Parallel Operation Optimization

The optimization of parallel operations is based on the statistical results of Operation statements. The first is to calculate the Access frequency of each table and the connection frequency between tables. The following principles are used to allocate table space and physical disks based on the data, reduce the disk I/O competition between system processes and user processes, separate the tables to be connected from the tablespace/physical disk, and separate frequently accessed tables from the tablespace/physical disk; separate the table structures and indexes of tables that often need to be searched in the tablespace/physical disk.

3. reduce storage structure Expansion

If the database of the application system is fragile and constantly growing or shrinking, the efficiency of such a system is reasonable within the non-dynamic change period, but within the dynamic change period, the performance is poor because of the dynamic expansion of oracle. In the process of dynamic expansion, Oracle must expand and allocate new storage space when creating rows and row changes to obtain the default values according to storage requirements, however, table extensions are often not the end of the process, but may also lead to the growth of data files and tablespaces. These extensions will lead to slow online system response. For such a system, the best way is to allocate enough size and a suitable growth margin in advance (unfortunately, these are only after a period of actual operation, in order to obtain a reasonable value from the statistical analysis ). When an object is created, its size should be fully calculated based on the application, and the values of initial, next, and minextents should be defined based on the data, make the database reach a better balance between physical storage and the number of dynamic increases, so that these objects do not increase frequently, but also occupy more databases.

Conclusion

It is of great significance to optimize the Oracle database to improve the availability and efficiency of the computer system, especially in the design and development stage of the Oracle database, to effectively optimize the logical structure and physical structure, creating a database with reasonable planning and layout can minimize the system overhead and greatly improve the overall performance of the application system. This will greatly benefit future database performance adjustments and utilization.