Summary of Practical Experience of Oracle Database Optimization
- 2021-09-05 01:13:38
- OfStack
1. Optimize the application and business logic, which is the most important.
2. Flexible application of paradigm and anti-paradigm in database design stage. 1 In general, for frequently accessed but infrequently modified data, the internal design should be physically non-standardized; For data that is frequently modified but not frequently accessed, the internal design should be physically normalized.
3. Make full use of memory, optimize sga, pga, etc. (11g has realized sga+pga automation, but sometimes it still needs to be adjusted manually), and properly put small table keep into cache.
4. Optimize the sql statement
1) Reduce the number of database queries, that is, reduce the request for system resources. Using distributed database objects such as snapshots and visualized graphs can reduce the number of database queries.
2) Try to query using the same or very similar SQL statements, which not only makes full use of the parsed syntax tree in the SQL shared pool, but also greatly increases the possibility of the data to be queried hitting in SGA.
3) Limit the use of dynamic SQL. Although dynamic SQL works well, dynamic SQL is reparsed even if there is an identical query value in the SQL shared pool.
4) Avoid the execution of SQL statement without any conditions. When SQL statement without any conditions is executed, FTS is usually carried out. The database first locates a data block, and then searches for other data in sequence, which will be a long process for large tables.
5) If there are constraints on the data in some tables, it is better to implement the SQL statement in the table with descriptive integrity rather than in the SQL program.
6) The SQL statements can be grouped into one group and committed centrally by canceling the automatic commit mode, and the program can explicitly commit and roll back the transaction with COMMIT and ROLLBACL.
7) It takes a long time to retrieve a large amount of data. Setting the row prefetch number can improve the performance of the system. Set a maximum value. When the row returned by SQL statement exceeds this value, the value library temporarily stops executing unless the user issues a new instruction to start organizing and displaying data, instead of letting the user continue to wait.
5. Optimize io, put different data files, control files and log files on different disks, put tables and indexes on different tablespaces, set appropriate block size, set asynchronous io, etc.
6. Establish a read-only table space properly to reduce the update of data block header and the occurrence of database failure.
7. Table design optimization
1) Partition
2) Compression
3) Establish an appropriate index
4) Set the appropriate pctfree to reduce row connections and row migration
5) Set the appropriate storage to control table fragmentation
6) Others, etc
8. Make full use of system cpu resources, use Parallel Query Option (PQO, parallel query selection) to query data. Using PQO can not only allocate SQL statement request processing among multiple CPU, but also read data simultaneously by independent processes when the queried data is on different disks.
9. Optimize database connections
1) Use a direct OLE DB database connection.
There are two ways to connect to the database through ADO, one is the traditional ODBC mode, and the other is OLE DB mode. ADO is based on OLE DB technology. In order to support ODBC, the corresponding call conversion from OLE DB to ODBC must be established, while the direct OLE DB mode does not need conversion, thus improving the processing speed.
2) Using the Connection Pool mechanism
In database processing, the most expensive resource is to establish database connection, and users will have a long connection waiting time. The solution is to reuse the existing Connection, that is, to use the Connection Pool object mechanism.
The principle of Connection Pool is that one connection buffer pool is maintained in IIS+ASP system, so that when the next user visits, one database connection is directly obtained from the connection buffer pool without reconnecting the database, so the response speed of the system can be greatly improved.
10. Make full use of the background processing scheme of data to reduce network traffic
1) Reasonable creation of temporary tables or views
So-called creating temporary tables or views, Is to create new tables or views on top of the database as needed, For a new table that queries information after multiple tables are associated, For single table query, the view can be created, which can make full use of the characteristics of large capacity and strong expandability of database. All the condition judgments and numerical calculation statistics can be added to temporary tables after being processed by the background system 1 of database server, and the process of forming data results can be realized by the process or function of database.
2) Make full use of database packaging technology
Use the database description language to write the database process or function, and then the process or function into a package in the database background system 1 run package can be.
3) Application of data replication, snapshot, view and remote procedure call technology
Data replication, that is, the data is copied to the local once, so that the local data will be used for future queries, but only for those data that have not changed much. Snapshots can also be used to dynamically replicate data between distributed databases, and define automatic refresh time or manual refresh of snapshots to ensure referential integrity of data. Calling remote procedures also significantly reduces network congestion caused by frequent SQL statement calls.
11. Implementing the System Resource Management Allocation Plan
ORACLE provides Database Resource Manager (DRM, Database Resource Manager) to control resource allocation for users, and DBA can use it to allocate the percentage of system resources for user classes and job classes. In an OLDP system, 75% of CPU resources can be allocated to online users, leaving the remaining 25% for batch users. In addition, multi-stage allocation of CPU can be performed. In addition to performing ES 116EN resource allocation, ES 117EN can also perform concurrent operation restrictions on resource user groups.
2. Flexible application of paradigm and anti-paradigm in database design stage. 1 In general, for frequently accessed but infrequently modified data, the internal design should be physically non-standardized; For data that is frequently modified but not frequently accessed, the internal design should be physically normalized.
3. Make full use of memory, optimize sga, pga, etc. (11g has realized sga+pga automation, but sometimes it still needs to be adjusted manually), and properly put small table keep into cache.
4. Optimize the sql statement
1) Reduce the number of database queries, that is, reduce the request for system resources. Using distributed database objects such as snapshots and visualized graphs can reduce the number of database queries.
2) Try to query using the same or very similar SQL statements, which not only makes full use of the parsed syntax tree in the SQL shared pool, but also greatly increases the possibility of the data to be queried hitting in SGA.
3) Limit the use of dynamic SQL. Although dynamic SQL works well, dynamic SQL is reparsed even if there is an identical query value in the SQL shared pool.
4) Avoid the execution of SQL statement without any conditions. When SQL statement without any conditions is executed, FTS is usually carried out. The database first locates a data block, and then searches for other data in sequence, which will be a long process for large tables.
5) If there are constraints on the data in some tables, it is better to implement the SQL statement in the table with descriptive integrity rather than in the SQL program.
6) The SQL statements can be grouped into one group and committed centrally by canceling the automatic commit mode, and the program can explicitly commit and roll back the transaction with COMMIT and ROLLBACL.
7) It takes a long time to retrieve a large amount of data. Setting the row prefetch number can improve the performance of the system. Set a maximum value. When the row returned by SQL statement exceeds this value, the value library temporarily stops executing unless the user issues a new instruction to start organizing and displaying data, instead of letting the user continue to wait.
5. Optimize io, put different data files, control files and log files on different disks, put tables and indexes on different tablespaces, set appropriate block size, set asynchronous io, etc.
6. Establish a read-only table space properly to reduce the update of data block header and the occurrence of database failure.
7. Table design optimization
1) Partition
2) Compression
3) Establish an appropriate index
4) Set the appropriate pctfree to reduce row connections and row migration
5) Set the appropriate storage to control table fragmentation
6) Others, etc
8. Make full use of system cpu resources, use Parallel Query Option (PQO, parallel query selection) to query data. Using PQO can not only allocate SQL statement request processing among multiple CPU, but also read data simultaneously by independent processes when the queried data is on different disks.
9. Optimize database connections
1) Use a direct OLE DB database connection.
There are two ways to connect to the database through ADO, one is the traditional ODBC mode, and the other is OLE DB mode. ADO is based on OLE DB technology. In order to support ODBC, the corresponding call conversion from OLE DB to ODBC must be established, while the direct OLE DB mode does not need conversion, thus improving the processing speed.
2) Using the Connection Pool mechanism
In database processing, the most expensive resource is to establish database connection, and users will have a long connection waiting time. The solution is to reuse the existing Connection, that is, to use the Connection Pool object mechanism.
The principle of Connection Pool is that one connection buffer pool is maintained in IIS+ASP system, so that when the next user visits, one database connection is directly obtained from the connection buffer pool without reconnecting the database, so the response speed of the system can be greatly improved.
10. Make full use of the background processing scheme of data to reduce network traffic
1) Reasonable creation of temporary tables or views
So-called creating temporary tables or views, Is to create new tables or views on top of the database as needed, For a new table that queries information after multiple tables are associated, For single table query, the view can be created, which can make full use of the characteristics of large capacity and strong expandability of database. All the condition judgments and numerical calculation statistics can be added to temporary tables after being processed by the background system 1 of database server, and the process of forming data results can be realized by the process or function of database.
2) Make full use of database packaging technology
Use the database description language to write the database process or function, and then the process or function into a package in the database background system 1 run package can be.
3) Application of data replication, snapshot, view and remote procedure call technology
Data replication, that is, the data is copied to the local once, so that the local data will be used for future queries, but only for those data that have not changed much. Snapshots can also be used to dynamically replicate data between distributed databases, and define automatic refresh time or manual refresh of snapshots to ensure referential integrity of data. Calling remote procedures also significantly reduces network congestion caused by frequent SQL statement calls.
11. Implementing the System Resource Management Allocation Plan
ORACLE provides Database Resource Manager (DRM, Database Resource Manager) to control resource allocation for users, and DBA can use it to allocate the percentage of system resources for user classes and job classes. In an OLDP system, 75% of CPU resources can be allocated to online users, leaving the remaining 25% for batch users. In addition, multi-stage allocation of CPU can be performed. In addition to performing ES 116EN resource allocation, ES 117EN can also perform concurrent operation restrictions on resource user groups.