How to optimize Oracle SQL Performance

This article mainly introduces the summary of Oracle statement optimization rules. What we will talk about today is how to optimize Oracle SQL Performance, this article mainly introduces Oracle SQL Performance Optimization in three major sections. The following describes the specific content of this article.

1. select an appropriate Oracle optimizer for ORACLE SQL Performance optimization adjustment

There are three optimizer types in ORACLE:

A. RULE (based on Rules)

B. COST (COST-based)

C. CHOOSE (optional)

Set the default optimizer. various declarations of the OPTIMIZER_MODE parameter in the ora file, such as RULE, COST, CHOOSE, ALL_ROWS, FIRST_ROWS. of course, you also overwrite SQL statements or sessions.

To use the Cost-Based Optimizer (CBO, Cost-Based Optimizer), you must run the analyze command frequently to increase the accuracy of object statistics in the database.

If the optimizer mode of the database is set to CHOOSE, the actual optimizer mode is related to whether the analyze command has been run. If the table has been analyze, the optimizer mode will automatically become CBO. Otherwise, the database will adopt the RULE form optimizer.

By default, ORACLE uses the CHOOSE optimizer. To avoid unnecessary full table scan, you must avoid using the CHOOSE optimizer, instead, we directly use a rule-based or cost-based optimizer.

2. Access Table in Oracle SQL Performance Optimization and adjustment methods ORACLE adopts two Access Table Record Methods

A. Full table Scan

Full table scan accesses each record in the table sequentially. ORACLE optimizes full table scanning by reading multiple data blocks at a time.

B. Access the table through ROWID

You can use ROWID-based access to improve the efficiency of table access. ROWID contains the physical location information recorded in the table ...... ORACLE uses indexes to connect data with the physical location where data is stored (ROWID. Generally, indexes provide a quick way to access ROWID. Therefore, queries based on index columns can improve the performance.

3. Share SQL statements in Oracle SQL Performance Optimization and adjustment

To avoid repeated parsing of the same SQL statement, ORACLE stores the SQL statement in memory after the first parsing. The memory in the shared buffer pool of the SGA (system global area) can be shared by all database users. Therefore, when you execute an SQL statement (sometimes called a cursor), if it is exactly the same as the previously executed statement, ORACLE can quickly obtain parsed statements and the best execution path. This function greatly improves SQL Execution performance and saves memory usage.

Unfortunately, ORACLE only provides cache buffering for simple tables. This function is not applicable to multi-table join queries. The database administrator must set the appropriate parameters for this region in init. ora. When the memory area is larger, more statements can be reserved. Of course, the possibility of sharing is higher. When you submit an SQL statement to ORACLE, ORACLE will first find the same statement in the memory.

It should be noted that ORACLE adopts a strict match between the two. To achieve sharing, the SQL statements must be identical (including spaces and line breaks ).

The shared statement must meet three conditions:

A. Character-level comparison:

The statements currently executed must be the same as those in the shared pool.

For example:

 
 

  
  
SELECT * FROM EMP;  
 
 

Different from each of the following

 
 

  
  
SELECT * from EMP;   
  
  
Select * From Emp;   
  
  
SELECT * FROM EMP;   
 
 

B. The objects referred to by the two statements must be identical:

For example:

How to access user object names

 
 

  
  
Jack sal_limit private synonym   
  
  
Work_city public synonym   
  
  
Plant_detail public synonym   
  
  
Jill sal_limit private synonym   
  
  
Work_city public synonym   
  
  
Plant_detail table owner 
 
 

Consider whether the following SQL statements can be shared between the two users.

C. bind variables must be used in the two SQL statements)

For example, the two SQL statements in the first group are the same (which can be shared), while the two statements in the second group are different (even when running, different bind variables are assigned the same value)
 

Article by: http://database.csdn.net/page/5b9d1e8e-2573-4b3e-b5d8-ba0a94d6ae49