Oracle Database optimization Summary

Summary of Oracle Database optimization 1. Optimizing applications and business logic is the most important thing. 2. flexible application of the database design phase paradigm and the anti-paradigm. Generally, the internal design should be physical and non-standard for frequently accessed but infrequently modified data. For data that is frequently modified but not frequently accessed, the internal design should be physically normalized. 3. Make full use of the memory to optimize sga and pga (11G has already achieved sga + pga automation, but sometimes it still needs to be adjusted manually) and properly store small tables in the cache. 4. Optimize SQL statement 1) Reduce the number of queries to the database, that is, reduce the number of requests to system resources. Using distributed database objects such as snapshots and graphs can reduce the number of queries to the database. 2) try to use the same or very similar SQL statements for queries. This not only makes full use of the analyzed syntax tree in the SQL sharing pool, the possibility of hitting the data to be queried in SGA is also greatly increased. 3) restrict the use of dynamic SQL statements. Although dynamic SQL statements are useful, dynamic SQL performs syntax analysis again even if there is a completely identical query value in the SQL sharing pool. 4) Avoid executing SQL statements without any conditions. When an SQL statement without any conditions is executed, it is usually required to perform the FTS. The database first locates a data block and then searches for other data in sequence, this is a long process for large tables. 5) If you have constraints on the data in some tables, it is best to use the description integrity of the SQL statements in the table to be created, rather than in the SQL program. 6) The automatic COMMIT mode can be canceled to merge SQL statements into a group for execution and then submit them in a centralized manner. The program can also use COMMIT and ROLLBACL to submit and roll back the transaction explicitly. 7) It takes a long time to retrieve a large amount of data. setting the number of row prefetch can improve the system performance and set a maximum value. When the SQL statement returns a row that exceeds this value, the numeric database temporarily stops running, unless a user sends a new instruction, the user starts to organize and display data, rather than waiting for the user to continue. 5. Optimized I/O, put different data files, control files, and log files on different disks, tables and indexes in different tablespaces, set proper block sizes, and set asynchronous I/O. 6. Create a read-only tablespace to reduce data block header updates and database faults. 7. table Design Optimization 1) Partition 2) compression 3) Create appropriate indexes 4) set appropriate pctfree to reduce row connections and row migration 5) set proper storage and control table fragmentation 6) other 8. make full use of the system cpu resources and use the Parallel Query Option (PQO, Parallel Query selection) Method for Data Query. Using the PQO method, you can not only allocate SQL statement request processing among multiple CPUs, when the queried data is on different disks, independent processes can read the data at the same time. 9. Optimize database connection 1) use the direct ole db database connection method. You can use ADO to connect to a database in two ways. One is the traditional ODBC method and the other is the ole db method. ADO is based on the ole db technology. To support ODBC, you must establish the call conversion from the corresponding ole db to ODBC. However, you do not need to convert the data using the direct ole db method, this increases the processing speed. 2) using the Connection Pool mechanism in database processing, the biggest resource expense is to establish a database Connection, and the user will have a long Connection wait time. The solution is to reuse the existing Connection, that is, use the Connection Pool object mechanism. The Connection Pool principle is: a Connection buffer Pool is maintained in the IIS + ASP system, so that when the next user accesses, a database Connection is directly obtained in the Connection buffer Pool, instead of reconnecting to the database, the system response speed can be greatly improved. 10. make full use of the data background processing solution to reduce network traffic 1) reasonably create a temporary table or view to create a temporary table or view, that is, create a new table or view based on the database as needed, A new table can be created for querying information after multi-Table Association, and a view can be created for querying a single table. This makes full use of the features of large database capacity and high scalability, the judgment and numerical calculation statistics of all conditions can be uniformly processed on the database server background and then appended to the temporary table. The process of forming data results can be achieved through the database process or function. 2) The database packaging technology makes full use of the database Description Language to compile the database process or function, and then pack the process or function into a uniform running package in the database background. 3) data replication, snapshots, views, and Remote Process calling techniques use data replication to replicate data once to the local device, so that local data will be used for future queries, but it is only suitable for those data with little changes. Snapshots can also be used to dynamically copy data between distributed databases, define the automatic snapshot refresh time or manual refresh, to ensure the integrity of reference data. The Remote Call process also greatly reduces network congestion caused by frequent SQL statement calls. 11. implement system Resource management allocation plan ORACLE provides Database Resource Manager (DRM) to control user Resource allocation, DBA can use it to allocate the percentage of system resources of user and job classes. In an OLDP system, 75% of CPU resources can be allocated to online users, and the remaining 25% is reserved for batch users. In addition, you can perform multi-level CPU allocation. In addition to CPU resource allocation, DRM can also limit the parallel operations on resource user groups.