Summary of Oracle Database optimization practices

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 statements
1) Reduce the number of queries to the database, that is, reduce the number of queries to the database by using distributed database objects such as snapshots and graphs.
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 an appropriate index
4) set appropriate pctfree to reduce the number of row connections and migration
5) set appropriate storage to control table fragments.
6) Others

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) use the Connection Pool mechanism
In database processing, the biggest resource cost is to establish a database connection, and the user still has 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) create a temporary table or view reasonably
Creating a temporary table or view is to create a new table or view on the basis of the database as needed. A new table can be created for querying information after multi-Table Association. For a single-Table query, a view can be created, in this way, the database capacity and scalability can be fully utilized. All condition judgment and numerical calculation statistics can be processed in the database server background and then appended to the temporary table, the process of forming data results can be implemented by the database process or function.
2) full use of database Packaging Technology
Use the database Description Language to compile the database process or function, and pack the process or function into a uniform running package in the database background.
3) Use of data replication, snapshots, views, and Remote Procedure Call Technology
Data Replication: Copies data to the local database at a time, 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 the system resource management allocation plan
ORACLE provides a Database Resource Manager (DRM) to control user Resource allocation. DBAs can use it to allocate system Resource percentages for 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.