How to improve the efficiency of Oracle databases

Oracle Database efficiency is a matter of great concern. The following describes several methods to improve the efficiency of Oracle databases for your reference. If you are interested in Oracle Database efficiency, take a look.

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.

3. Efficient SQL statement Design

Generally, the following methods can be used to optimize the performance of SQL statements on data operations:

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.

 

Oracle index Optimization Design

Implementation of parallel query of one column in oracle

Use of oracle rownum statements

Oracle paging query statements

Oracle index type