Analysis of the same Oracle SQL statement

In actual applications, to resolve the same Oracle SQL statement without repeating it, Oracle SQL statements are generally stored in the relevant 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 the execution performance of Oracle SQL 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 Oracle 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 two Oracle SQL statements)

For example, the two Oracle SQL statements in the first group are the same (which can be shared), while the two statements in the second group are different (even if different bind variables have the same value at runtime)

A.

 
 

  
  
select pin ， name from people where pin = ：blk1.pin;   
  
  
select pin ， name from people where pin = ：blk1.pin;  
 
 

B.

 
 

  
  
select pin ， name from people where pin = ：blk1.ot_ind;   
  
  
select pin ， name from people where pin = ：blk1.ov_ind;  
 
 

The above content is an introduction to the sharing of Oracle SQL statements. I hope you will gain some benefits.

Article by: http://www.programbbs.com/doc/class10-2.htm