Oracle Database Optimization Practical Experience Summary _oracle

1. Optimize the application and business logic, this is the most important.

2. Database design phase paradigm and the flexible application of the inverse paradigm. In general, for frequently accessed but infrequently modified data, internal design should be physically irregular; for frequently modified but infrequently accessed data, internal design should be physically standardized.

3. Make full use of memory, optimize SGA, PGA, etc. (11g has achieved SGA+PGA automation, but sometimes still need to manually adjust), the appropriate small table keep to the cache.

4. Optimizing SQL statements
1 reduce the number of queries on the database, that is, to reduce requests for system resources, use of snapshots and maps and other distributed database objects can reduce the number of queries on the database.
2) Try to use the same or very similar SQL statements to query, so that not only the full use of the SQL shared pool of parsed syntax tree, to query the data in the SGA hit the possibility of a significant increase.
3 Limit the use of dynamic SQL, although dynamic SQL is good, but even if there is an exact same query value in the SQL shared pool, dynamic SQL will be parsed again.
4 avoid the execution of SQL statements with no conditions. An SQL statement that has no condition is executed, typically FTS, the database locates a block of data, and then sequentially looks for other data, which is a lengthy process for large tables.
5 If you have constraints on the data in some tables, it is best to use descriptive integrity for the SQL statements that are in the table, rather than using SQL programs to implement them.
6 by canceling the autocommit mode, the SQL statements can be pooled into a set of commit, and the program can also commit and rollback the transaction explicitly with commit and rollbacl.
7) It takes a long time to retrieve a large amount of data setting the row prefetch can improve the performance of the system, set a maximum value, and when the SQL statement returns rows that exceed the value, the value library is temporarily stopped, unless the user sends out a new instruction to start organizing and displaying the data instead of letting the user wait.

5. Optimize IO, put different data files, control files, log files on different disks, tables and indexes in different table space, set the appropriate block size, set asynchronous IO and so on.

6. The proper establishment of a read-only table space to reduce the size of the data and database failure of the update.

7. Table Design Optimization
1) Zoning
2) compression
3) To establish the appropriate index
4 set appropriate pctfree to reduce line and row migration
5) Set the appropriate storage, control the table fragment
6) Other

8. Make full use of the system CPU resources, using the parallel query option (PQO, parallel query selection) way of data query, using the Pqo method not only to allocate SQL statements between multiple CPU request processing, when the queried data in a different disk, A separate process can read data simultaneously.

9. Optimizing Database Connections
1 Use a direct OLE DB database connection method.
ADO allows you to connect to a database in two ways, a traditional ODBC approach and an OLE DB approach. ADO is based on OLE DB technology, in order to support ODBC, you must establish the corresponding OLE DB to ODBC call conversion, while using direct OLE DB method without conversion, thereby increasing processing speed.
2) Using the connection pool mechanism
In database processing, the most expensive resource is to establish a database connection, and the user will have a longer connection wait time. The solution is to reuse the existing connection, that is, to use the connection pool object mechanism.
Connection Pool principle is: The iis+asp system maintains a connection buffer pool, so that the next user access, directly in the connection buffer pool to obtain a database connection, without the need to reconnect the database, so can greatly improve the system response speed.

10. Reduce network traffic by making full use of data background processing schemes
1 reasonable create temporary table or view
To create a temporary table or view, is based on the need to create a new table or view on the basis of the database, for multiple tables to query the information after the new table, for a single table query can create a view, this can make full use of the database capacity, scalability, and so on, all the conditions of judgment, The numerical computation statistics can be appended to the temporary table after the database server is unified processing, and the process of data result can be realized by the process or function of the database.
2 The full use of database packaging technology
Using the database description language to write the process or function of the database, and then the process or function into a package in the background of the database unified operation package can be.
3 data replication, snapshots, views, remote process call technology application
Data replication, the data is replicated to the local, so that future queries will use local data, but only for those data that is not changed. Snapshots can also be used to dynamically replicate data between distributed databases, to define automatic refresh times for snapshots, or to manually refresh them to ensure referential referential integrity of the data. Invoking a remote procedure can also significantly reduce network congestion caused by frequent SQL statement calls.

11. Implementation of the system Resource management distribution plan
Oracle provides database Resource Manager (DRM, Database resource Manager) to control the user's resource allocation, which the DBA can use to assign a percentage of the system resources of the user class and the job class. In a OLDP system, you can assign 75% of the CPU resources to online users and the remaining 25% to the batch user. In addition, the CPU can also be multi-level allocation. In addition to CPU resource allocation, DRM can also perform parallel operations on the resource user group.