How Oracle decides to reuse Shared SQL Area
Introduction:
In the previous post, I reviewed an comparison on SHARED SQL AREA vs. PRIVATE SQL AREA. In this topic, I review most internal steps to SQL ready for processing in Oracle Shared SQL Area and explain how Oracle can reuse statistics and informations gathered in the Shared SQL Area.
The process Oracle uses to decide whether to reuse an existing Shared SQL Area is a critical part of its performance tuning mechanism. It's all about avoiding the costly "hard parse" and instead performing a "soft parse."
Here's how Oracle makes that decision:
1. Hashing the SQL Statement
First, Oracle takes the entire SQL statement text and runs it through a hashing algorithm. This algorithm generates a numeric hash value that represents the SQL text.
2. Checking the Library Cache
Next, Oracle uses this hash value to quickly look up an entry in the Library Cache (which is part of the Shared Pool).
• If no match is found for the hash value: The SQL statement is new. Oracle has no choice but to perform a hard parse.
This involves:
1. Syntax Check: Verifying the SQL grammar.
2. Semantic Check: Validating object names (like tables and columns) and user privileges.
3. Optimization: Generating one or more potential execution plans and selecting the most efficient one.
4. Creating the Shared SQL Area: Allocating memory in the Shared Pool to store the parsed statement and the chosen execution plan.
This is a CPU and resource-intensive process that Oracle tries to avoid.
• If a match for the hash value is found: This means one or more SQL statements with the same hash value already exist in the Shared Pool. Now, Oracle must perform additional checks to ensure it's an *exact* match.
3. The String-by-String Comparison
This is the most important step. Even if the hash values match, Oracle performs a character-by-character comparison of the new SQL statement against the one stored in the existing Shared SQL Area.
The SQL statements must be absolutely identical to be considered a match.
This includes:
Whitespace:
SELECT * FROM employees
is different from
SELECT * FROM employees؛
Case (unless configured otherwise):
SELECT * FROM EMPLOYEES
is different from
select * from employees؛
Comments:
SELECT * FROM employees --my query
is different from
SELECT * FROM employees.
Object Names:
SELECT * FROM scott.emp
is different from
SELECT * FROM emp
(even if they resolve to the same table via a synonym).
If this character-by-character comparison fails, Oracle treats it as a new statement and performs a hard parse.
Note: This behavior can mostly related to Cursor Sharing parameter value.(see last section)
4. Environment and Security Check
If the text is identical, Oracle performs one final set of checks on the execution environment. The environment for the new statement must match the environment of the stored statement.
This includes:
Optimizer Settings: The OPTIMIZER_MODE (e.g., `ALL_ROWS`, `FIRST_ROWS`) must be the same.
Schema Objects: The underlying tables, indexes, and statistics must not have changed in a way that would invalidate the existing plan.
For example, if an index used by the plan was dropped, the plan is invalid.
Bind Variable Types: The data types of the bind variables must be compatible.
The Result: Soft vs. Hard Parse
Soft Parse (Match Found): If the SQL text is identical AND the environment checks pass, Oracle reuses the existing Shared SQL Area and its execution plan.
This is a soft parse. It's extremely fast as it skips the complex optimization step.
This is the desired outcome for a high-performance application.
Hard Parse (No Match): If any of the checks fail, Oracle performs a hard parse, creating a new Shared SQL Area.
This is why using bind variables is so critical for application performance.
Statements like:
SELECT * FROM products WHERE product_id = 101;
SELECT * FROM products WHERE product_id = 205;
...are considered two different statements by Oracle, leading to two separate hard parses and two Shared SQL Areas.
However, using a bind variable:
SELECT * FROM products WHERE product_id = :id;
...results in a single statement that can be shared and reused for any `product_id`, leading to one hard parse and many fast soft parses.
How CURSOR_SHARING Affects the SQL Parsing
Process The CURSOR_SHARING parameter does not influence the mandatory validation steps of a parse, but it is the deciding factor in the Soft Parse Decision; the process of reusing an existing cursor.
Hard Parse Prerequisites are Independent: The initial steps of a parse is Syntax Check (verifying grammar) and Semantic Check (validating objects and privileges) are mandatory and must be performed regardless of the CURSOR_SHARING setting.
These steps are not modified by the parameter.
The Soft Parse Decision is Directly Controlled: The core function of CURSOR_SHARING is to define what Oracle considers "identical" for the purpose of cursor reuse in the Shared Pool. This decision is made at the point of the String-by-String Comparison.
1- CURSOR_SHARING = EXACT (Default):
The comparison is performed on the raw SQL text.
Any difference, including case, whitespace, or the literal value in a WHERE clause (e.g., WHERE C=1 vs. WHERE C=2), results in a mismatch, forcing a hard parse.
2- CURSOR_SHARING = FORCE:
Before the comparison, Oracle internally rewrites the SQL statement by replacing all literal values with system-generated bind variables (e.g., :"SYS_B_0").
The comparison is then performed on this rewritten, bind-variable-substituted text.
This allows statements that differ only by their literal values to be considered identical, enabling a soft parse and avoiding the costly Optimization and Shared SQL Area Creation steps.
Impact on Hard Parse Steps: By enabling a soft parse, CURSOR_SHARING allows the database to skip the resource-intensive Optimization (plan generation) and Shared SQL Area Creation steps, which are the most expensive parts of a hard parse.
In summary, CURSOR_SHARING acts as a pre-processor for the soft-parse comparison, effectively expanding the pool of statements that can be considered identical for cursor reuse.
