Shared SQL statement for Oracle performance optimization learning notes

Shared SQL statement for Oracle performance optimization learning notes
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 (systemglobal 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 scheme. This function greatly improves SQL Execution performance and saves memory usage.
Unfortunately, ORACLE only provides high-speed buffer (cache buffering) for simple tables. This function is not applicable to multi-table join queries.
The database administrator must set 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 statement must be
Completely identical (including spaces, line breaks, etc ).

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:

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 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;