This chapter contains the following topics:
A bind variable is a placeholder in a SQL statement that must be replaced with a valid value or value address for the statement to execute successfully. By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time. The following query uses v_empid as a bind variable:
SELECT * FROM employees WHERE employee_id = :v_empid;
This section contains the following topics:
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.
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, the optimizer can determine the selectivity of a WHERE clause condition as if literals had been used, thereby improving the plan.
The following 100,000 row emp table exists in the database. The table has the following definition:
SQL> DESCRIBE emp Name Null? Type ---------------------- -------- ---------------------------------- ENAME VARCHAR2(20) EMPNO NUMBER PHONE VARCHAR2(20) DEPTNO NUMBER
The data is significantly skewed in the deptno column. The value 10 is found in 99.9% of the rows. Each of the other deptno values (0 through 9) is found in 1% of the rows. You have gathered statistics for the table, resulting in a histogram on the deptno column. You define a bind variable and query emp using the bind value 9 as follows:
VARIABLE deptno NUMBER EXEC :deptno := 9 SELECT /*ACS_1*/ COUNT(*), MAX(empno) FROM emp WHERE deptno = :deptno;
The query returns 10 rows:
COUNT(*) MAX(EMPNO)
---------- ----------
10 99
To generate the execution plan for the query, the database peeked at the value 9 during the hard parse. The optimizer generated selectivity estimates as if the user had executed the following query:
SELECT /*ACS_1*/ COUNT(*), MAX(empno) FROM emp WHERE deptno = 9;
When choosing a plan, the optimizer only peeks at the bind value during the hard parse. This plan may not be optimal for all possible values.
Oracle Database automatically determines whether the SQL statement or PL/SQL block being issued is identical to another statement currently in the shared pool.
Oracle Database performs the following steps to compare the text of the SQL statement to existing SQL statements in the shared pool:
The text of the statement is hashed.
If no matching hash value exists, then the SQL statement does not currently exist in the shared pool, so the database performs a hard parse.
The database looks for a matching hash value for an existing SQL statement in the shared pool. The following options are possible:
No matching hash value exists.
In this case, the SQL statement does not currently exist in the shared pool, so the database performs a hard parse. This ends the shared pool check.
A matching has value exists.
In this case, the database compares the text of the matched statement to the text of the hashed statement to see 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;
Usually, 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 parameter CURSOR_SHARING has been set to FORCE, in which case similar statements can share SQL areas. The costs and benefits involved in using CURSOR_SHARING are explained in "When to Set CURSOR_SHARING to FORCE".
See Also:
Oracle Database Reference for more information about theCURSOR_SHARING initialization parameterThe database compares objects referenced in the issued statement to the referenced objects of all existing statements in the 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 issue the following SQL statement, and if each user has its own employees table, then the following statement is not identical because the statement references different employees tables for each user:
SELECT * FROM employees;
The database determines whether bind variables in the SQL statements match in name, data type, and length.
For example, the following statements cannot use the same shared SQL area because the bind variable names differ:
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. The conversion uniformly changes characters to uppercase, compresses white space, and renames bind variables so that a consistent set of SQL statements is produced.
The database determines whether the session environment is identical.
For example, SQL statements must be optimized using the same optimizer goal (see "Choosing an Optimizer Goal").
The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.
For appropriate queries, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. For example, the optimizer might choose one plan for bind value 9 and a different plan for bind value 10. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the same plan is not always used for each execution or bind variable value.
Adaptive cursor sharing is enabled for the database by default and cannot be disabled. Adaptive cursor sharing does not apply to SQL statements containing more than 14 bind variables.
Note:
Adaptive cursor sharing is independent of theCURSOR_SHARING initialization parameter (see "Sharing Cursors for Existing Applications"). Adaptive cursor sharing is equally applicable to statements that contain user-defined and system-generated bind variables.A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.
The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:
The optimizer has peeked at the bind values to generate selectivity estimates.
A histogram exists on the column containing the bind value (see Chapter 11, "Histograms").
The bind is used in a range predicate.
Example 15-2 Bind-Sensitive Cursors
Example 15-1 queried the emp table using the bind value 9 for deptno. In this example, you run the DBMS_XPLAN.DISPLAY_CURSOR function to show the execution plan:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
The output is as follows:
---------------------------------------------------------------------------------- | Id Operation | Name | Rows | Bytes |Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 16 | | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 16 | 2 (0)| 00:00:01| |* 3 | INDEX RANGE SCAN | EMP_I1 | 1 | | 1 (0)| 00:00:01| ----------------------------------------------------------------------------------
The plan indicates that the optimizer chose an index range scan, which is expected because of the low cardinality of the value 9. Query V$SQL to view statistics about the cursor:
COL BIND_SENSI FORMAT a10
COL BIND_AWARE FORMAT a10
COL BIND_SHARE FORMAT a10
SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",
IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
FROM V$SQL
WHERE SQL_TEXT LIKE 'select /*ACS_1%';
As shown in the following output, one child cursor exists for this statement and has been executed once. A small number of buffer gets are associated with the child cursor. Because the deptno data is skewed, the database created a histogram. This histogram led the database to mark the cursor as bind-sensitive (IS_BIND_SENSITIVE is Y).
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 1 56 Y N Y
For each execution of the query with a new bind value, the database records the execution statistics for the new value and compares them to the execution statistics for the previous value. If execution statistics vary greatly, then the database marks the cursor bind-aware.
A bind-aware cursor is a bind-sensitive cursor that is eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.
When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values. If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:
Generates a new plan based on the new bind value.
Marks the original cursor generated for the statement as not sharable (V$SQL.IS_SHAREABLE is N). This cursor is no longer usable. The database marks the cursor as able to age out of the shared SQL area quickly.
Example 15-3 Bind-Aware Cursors
In Example 15-1 you queried emp using the bind value 9. Now you query emp using the bind value 10. The query returns 99,900 rows that contain the value 10:
COUNT(*) MAX(EMPNO) ---------- ---------- 99900 100000
Because the cursor for this statement is bind-sensitive, the optimizer assumes that the cursor can be shared. Consequently, the optimizer uses the same index range scan for the value 10 as for the value 9.
The V$SQL output shows that the same bind-sensitive cursor was executed a second time (the query using 10) and required many more buffer gets than the first execution:
SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",
IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
FROM V$SQL
WHERE SQL_TEXT LIKE 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 2 1010 Y N Y
Now you execute the query using the value 10 a second time. The database compares statistics for previous executions and marks the cursor as bind-aware. In this case, the optimizer decides that a new plan is warranted, so it performs a hard parse of the statement and generates a new plan. The new plan uses a full table scan instead of an index range scan:
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 208 (100)| | | 1 | SORT AGGREGATE | | 1 | 16 | | | |* 2 | TABLE ACCESS FULL| EMP | 95000 | 1484K| 208 (1)| 00:00:03 | ---------------------------------------------------------------------------
A query of V$SQL shows that the database created an additional child cursor (child number 1) that represents the plan containing the full table scan. This new cursor shows a lower number of buffer gets and is marked bind-aware:
SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",
IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
FROM V$SQL
WHERE SQL_TEXT LIKE 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 2 1010 Y N Y
1 2 1522 Y Y Y
After you execute the query twice with value 10, you execute it again using the more selective value 9. Because of adaptive cursor sharing, the optimizer "adapts" the cursor and chooses an index range scan rather than a full table scan for this value (see "Introduction to Access Paths").
A query of V$SQL indicates that the database created a new child cursor (child number 2) for the execution of the query:
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
0 2 1010 Y N N
1 1 1522 Y Y Y
2 1 7 Y Y Y
Because the database is now using adaptive cursor sharing, the database no longer uses the original cursor (child 0), which is not bind-aware. The shared SQL area can now age out the defunct cursor.
If the optimizer creates a plan for a bind-aware cursor, and if this plan is the same as an existing cursor, then the optimizer can perform cursor merging. In this case, the database merges cursors to save space in the shared SQL area. The database increases the selectivity range for the cursor to include the selectivity of the new bind.
Suppose you execute a query with a bind value that does not fall within the selectivity ranges of the existing cursors. The database performs a hard parse and generates a new plan and new cursor. If this new plan is the same plan used by an existing cursor, then the database merges these two cursors and deletes one of the old cursors.
You can use the V$ views for adaptive cursor sharing to see selectivity ranges, cursor information (such as whether a cursor is bind-aware or bind-sensitive), and execution statistics:
V$SQL shows whether a cursor is bind-sensitive or bind-aware
V$SQL_CS_HISTOGRAM shows the distribution of the execution count across a three-bucket execution history histogram
V$SQL_CS_SELECTIVITY shows the selectivity ranges stored for every predicate containing a bind variable if the selectivity was used to check cursor sharing
V$SQL_CS_STATISTICS summarizes the information that the optimizer uses to determine whether to mark a cursor bind-aware.
Reuse of shared SQL for multiple users running the same application, avoids hard parsing. Soft parses provide a significant reduction in the use of resources such as the shared pool and library cache latches.
To share cursors:
Use bind variables rather than literals in SQL statements whenever possible.
For example, the following statements cannot use the same shared area because they do not match character for character:
SELECT employee_id FROM employees WHERE department_id = 10; SELECT employee_id FROM employees WHERE department_id = 20;
By replacing the literals with a bind variable, only one SQL statement would result, which could be executed twice:
SELECT employee_id FROM employees WHERE department_id = :dept_id;
Note:
For existing applications where rewriting the code to use bind variables is impractical, you can use theCURSOR_SHARING initialization parameter to avoid some hard parse overhead. See "Sharing Cursors for Existing Applications".Avoid application designs that result in large numbers of users issuing dynamic, unshared SQL statements.
Typically, the majority of data required by most users can be satisfied using preset queries. Use dynamic SQL where such functionality is required.
Ensure that users of the application do not change the optimization approach and goal for their individual sessions.
Establish the following policies for application developers:
Standardize naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.
Consider using stored procedures whenever possible.
Multiple users issuing the same stored procedure use the same shared PL/SQL area automatically. Because stored procedures are stored in a parsed form, their use reduces run-time parsing.
For SQL statements that are identical but are not being shared, query V$SQL_SHARED_CURSOR to determine why the cursors are not shared. This would include optimizer settings and bind variable mismatches.
In SQL parsing, an identical statement is a statement whose text is identical to another, character for character, including spaces, case, and comments. A similar statement is identical except for the values of some literals.
The parse phase compares the statement text with statements in the shared pool to determine whether the statement can be shared. If the initialization parameter CURSOR_SHARING=EXACT (default), and if a statement in the pool is not identical, then the database does not share the SQL area. Each statement has its own parent cursor and its own execution plan based on the literal in the statement.
When SQL statements use literals rather than bind variables, a nondefault setting for the CURSOR_SHARING initialization parameter enables the database to replace literals with system-generated bind variables. Using this technique, the database can sometimes reduce the number of parent cursors in the shared SQL area.
When CURSOR_SHARING is set to a nondefault value, the database performs the following steps during the parse:
Searches for an identical statement in the shared pool
If an identical statement is found, then the database skips to Step 3. Otherwise, the database proceeds to the next step.
Searches for a similar statement in the shared pool
If a similar statement is not found, then the database performs a hard parse. If a similar statement is found, then the database proceeds to the next step.
Proceeds through the remaining steps of the parse phase to ensure that the execution plan of the existing statement is applicable to the new statement
If the plan is not applicable, then the database performs a hard parse. If the plan is applicable, then the database proceeds to the next step.
Shares the SQL area of the statement
Note:
The database does not perform literal replacement on theORDER BY clause because it is not semantically correct to consider the constant column number as a literal. The column number in the ORDER BY clause affects the query plan and execution, so the database cannot share two cursors having different column numbers.See Also:
"SQL Sharing Criteria" for more details on the various checks performed
Oracle Database Reference to learn about the CURSOR_SHARING initialization parameter
The best practice is to write sharable SQL and use the default of EXACT for CURSOR_SHARING. However, for applications with many similar statements, setting CURSOR_SHARING to FORCE can significantly improve cursor sharing, resulting in reduced memory usage, faster parses, and reduced latch contention. Consider this approach when statements in the shared pool differ only in the values of literals, and when response time is poor because of a very high number of library cache misses.
Note:
Staring in Oracle Database 12c, theSIMILAR value for CURSOR_SHARING is deprecated. Use FORCE instead.When CURSOR_SHARING is set to FORCE, the database uses one parent cursor and one child cursor for each distinct SQL statement. The database uses the same plan for each execution of the same statement. For example, consider the following statement:
SELECT * FROM hr.employees WHERE employee_id = 101
If you use FORCE, then the database optimizes this statement as if it contained a bind variable and uses bind peeking to estimate cardinality. Statements that differ only in the bind variable share the same execution plan.
Setting CURSOR_SHARING to FORCE has the following drawbacks:
The database must perform extra work during the soft parse to find a similar statement in the shared pool.
There is an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals in a SELECT statement. However, the actual length of the data returned does not change.
Star transformation is not supported.
See Also:
Oracle Database Reference to learn about the CURSOR_SHARING initialization parameter