Oracle 数据库 SQL 语句共享与解析策略-CSDN博客

https://docs.oracle.com/database/121/TGDBA/tune_shared_pool.htm#TGDBA565

SQL Sharing Criteria

Oracle Database automatically determines whether a SQL statement or PL/SQL block being issued is identical to another statement currently in the shared pool.

To compare the text of the SQL statement to the existing SQL statements in the shared pool, Oracle Database performs the following steps:

The text of the SQL statement is hashed.

If there is no matching hash value, then the SQL statement does not currently exist in the shared pool, and a hard parse is performed.
If there is a matching hash value for an existing SQL statement in the shared pool, then the text of the matched statement is compared to the text of the hashed statement to verify if they are identical.

The text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces, case, and comments. For example, the following statements cannot use the same shared SQL area:
```
SELECT * FROM employees;
SELECT * FROM Employees;
SELECT *  FROM employees;
```
Also, SQL statements that differ only in literals cannot use the same shared SQL area. For example, the following statements do not resolve to the same SQL area:
```
SELECT count(1) FROM employees WHERE manager_id = 121;
SELECT count(1) FROM employees WHERE manager_id = 247;
```
The only exception to this rule is when the CURSOR_SHARING parameter is set to FORCE, in which case similar statements can share SQL areas. For information about the costs and benefits involved in cursor sharing, see "Sharing Cursors".
The objects referenced in the issued statement are compared to the referenced objects of all existing statements in the shared pool to ensure that they are identical.

References to schema objects in the SQL statements or PL/SQL blocks must resolve to the same object in the same schema. For example, if two users each issue the following SQL statement but they each have their own employees table, then this statement is not considered identical, because the statement references different tables for each user:
```
SELECT * FROM employees;
```
Bind variables in the SQL statements must match in name, data type, and length.

For example, the following statements cannot use the same shared SQL area, because the bind variable names are different:
```
SELECT * FROM employees WHERE department_id = :department_id;
SELECT * FROM employees WHERE department_id = :dept_id;
```
Many Oracle products, such as Oracle Forms and the precompilers, convert the SQL before passing statements to the database. Characters are uniformly changed to uppercase, white space is compressed, and bind variables are renamed so that a consistent set of SQL statements is produced.
The session's environment must be identical.

For example, SQL statements must be optimized using the same optimization goal.