Bind Variable Peeking
Introduced in 9i,in bind variable peeking (also known as bind peeking), the optimizer looks at the value in a bind variable when the database performs a hard parse of a statement. The optimizer does not look at the bind variable values before every parse. Rather, the optimizer peeks only when the optimizer is first invoked, which is during the hard parse.
When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. By peeking at bind values during the initial hard parse, the plan may not be optimal for all possible bind values.
--Assume that you execute the following statements, which execute three different statements using different literals (101, 120, and 165), and then display the execution plans for each.
SQL> SET AUTOTRACE ON EXPLAIN
--The database hard parsed all three statements, which were not identical. The DISPLAY_CURSOR output, which has been edited for clarity, shows that the optimizer chose the same index range scan plan for the first two statements, but a full table scan plan for the statement using literal 165.
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < 101;
SUM(SALARY)
-----------
24000
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<101)
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < 120;
SUM(SALARY)
-----------
163308
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<120)
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < 165;
SUM(SALARY)
-----------
445608
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| * 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<165)
--The preceding output shows that the optimizer considers a full table scan more efficient than an index scan for the query that returns more rows.
--This example rewrites the query to use bind variables instead of literals. You bind the same values (101, 120, and 165) to the bind variable :emp_id, and then display the execution plans for each.The DISPLAY_CURSOR output shows that the optimizer chose exactly the same plan for all three statements.
SQL> VAR emp_id NUMBER;
SQL> EXEC :emp_id := 101;
PL/SQL procedure successfully completed.
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < :emp_id;
SUM(SALARY)
-----------
24000
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<TO_NUMBER(:EMP_ID))
SQL> EXEC :emp_id := 120;
PL/SQL procedure successfully completed.
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < :emp_id;
SUM(SALARY)
-----------
163308
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<TO_NUMBER(:EMP_ID))
SQL> EXEC :emp_id := 165;
PL/SQL procedure successfully completed.
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < :emp_id;
SUM(SALARY)
-----------
445608
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<TO_NUMBER(:EMP_ID))
--In contrast, when the preceding statements were executed with literals, the optimizer chose a lower-cost full table scan when the employee ID value was 165. This is the problem solved by adaptive cursor sharing.CURSOR_SHARING Parameter
In Oracle, the CURSOR_SHARING initialization parameter determines how the database handles SQL statements that are similar but not identical (e.g., they differ only in literal values). Its primary purpose is to improve performance by allowing multiple sessions to share the same execution plan, thereby reducing “hard parses” that can cause high CPU usage and latch contention.
EXACT :-
This is the default value. The database enables only textually identical statements to share a cursor. The database does not attempt to replace literal values with system-generated bind variables. In this case, the optimizer generates a plan for each statement based on the literal value.
SIMILAR :-
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
FORCE :-
The database replaces all literals with system-generated bind variables. For statements that are identical after the bind variables replace the literals, the optimizer uses the same plan.
Parameter type String
Syntax CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
Default value EXACT
Modifiable ALTER SESSION/ALTER SYSTEM
Basic No You can set CURSOR_SHARING at the system or session level, or use the CURSOR_SHARING_EXACT hint at the statement level.
SQL> SHOW parameter CURSOR_SHARING
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < 101;
SUM(SALARY)
-----------
24000
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<101)
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < 120;
SUM(SALARY)
-----------
163308
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<120)
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < 165;
SUM(SALARY)
-----------
445608
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<165)
SQL> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,HASH_VALUE
2 FROM V$SQLAREA
3 WHERE SQL_TEXT LIKE '%employees%'
4 AND SQL_TEXT NOT LIKE '%SQL_TEXT%';
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE
---------------------------------------------------------- -------------- ------------ -----------
SELECT SUM(salary) FROM employees WHERE employee_id < 165 12rgh61pdj8hp 1 1792582165
SELECT SUM(salary) FROM employees WHERE employee_id < 120 7xstcck4uaspb 1 2309317291
SELECT SUM(salary) FROM employees WHERE employee_id < 101 490j6jm5x334q 1 3419507862
SQL> ALTER SESSION SET CURSOR_SHARING=FORCE;
Session altered.
SQL> SHOW parameter CURSOR_SHARING
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string FORCE
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < 101;
SUM(SALARY)
-----------
24000
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<101)
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < 120;
SUM(SALARY)
-----------
163308
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<120)
SQL> SELECT SUM(salary) FROM employees WHERE employee_id < 165;
SUM(SALARY)
-----------
445608
Execution Plan
----------------------------------------------------------
Plan hash value: 2410354593
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 26 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"<165)
--All three statements used the same plan.The optimizer chose the plan, an index range scan, because it peeked at the first value (101) bound to the system bind variable, and picked this plan as the best for all values. In fact, this plan is not the best plan for all values. When the value is 165, a full table scan is more efficient.
--A query of V$SQLAREA confirms that Oracle Database replaced with the literal with system bind variable : "SYS_B_0", and created one parent and one child cursor (VERSION_COUNT=1) for all three statements, which means that all executions shared the same plan.
SQL> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT,HASH_VALUE
2 FROM V$SQLAREA
3 WHERE SQL_TEXT LIKE '%employees%'
4 AND SQL_TEXT NOT LIKE '%SQL_TEXT%';
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE
----------------------------------------------------------------- ----------------------------- ----
SELECT SUM(salary) FROM employees WHERE employee_id < :"SYS_B_0" cbh4a95wf6r79 1 2028166377
