Saturday, December 16, 2017

Bind-Aware Cursors

Bind-Aware Cursors

A bind-aware cursor is a bind-sensitive cursor that is now eligible to use different plans for different bind values. That means when a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its cardinality estimate.


When a statement with a bind-sensitive cursor executes, the optimizer uses an internal algorithm to determine whether to mark the cursor bind-aware or keep it as bind-sensitive.  This decision depends on whether the cursor produces significantly different data access patterns - plans for different bind values, resulting in a performance cost that differs from expectations.

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 bind value.
•       Marks the original cursor generated for the statement as not sharable (V$SQL.IS_SHAREABLE is N). That means original cursor is no longer usable and is eligible to age out of the library cache.

When the same query repeatedly executes with different bind values, the database examines the bind values and considers whether the current bind value results in a significantly different data volume, or whether an existing plan is sufficient. The database does not create a new plan for each new value.

During the initial hard parses, the optimizer is essentially mapping out the relationship between bind values and the appropriate execution plan.
After this initial period, the database eventually reaches a steady state. Executing with a new bind value results in picking the best child cursor
in the cache, without requiring a hard parse. Thus, the number of parses does not scale with the number of different bind values.
 

This example continues the example in "Bind-Sensitive Cursor". The following code again issues query on emp table with the bind variable set to 60:
   
 


EXEC :dept_id := 60;
SELECT COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept_id = :dept_id;
COUNT(*) MAX(EMPLOYEE_ID)
---------------------------------------------------------
100045           100999


During the first two executions, the database was monitoring the behavior of the queries, and determined that the different bind values caused the queries to differ significantly in cardinality. Based on this difference, the database adapts its behavior so that the same plan is not always shared for this query. Thus, the optimizer generates a new plan based on the current bind value, which is 60 :
      

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a5upref8bn9 , child number 1
------------------------------------
SELECT COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept= :dept_id
Plan hash value: 1756356638
-----------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost(%CPU)| Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |254 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |     8 |         |          |
|*  2 |   TABLE ACCESS FULL| EMP |  100K |  770K |254  (15)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPT_ID"=:DEPT_ID) 


The following query of V$SQL obtains information about the cursor:

   

SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC,
                         BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS,
                         IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT             CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHARABLE
----------------------   ------ ----- ---------- --------- ---------- --------
SELECT COUNT(*),         0     2        1649         Y          N        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id

SELECT COUNT(*),         1     1        700         Y          Y        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id
 

The preceding output shows that the database created an additional child cursor (CHILD# of 1). Cursor 0 is now marked as not shareable. Cursor 1 shows a number of buffers gets lower than cursor 0, and is marked both bind-sensitive and bind-aware. A bind-aware cursor may use different plans for different bind values, depending on the selectivity of the predicates containing the bind variable.

Lets execute below query again with dept_id 10 :-
           

VARIABLE dept_id NUMBER
            EXEC :dept_id := 10;

            SELECT COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept_id = :dept_id;
            COUNT(*) MAX(EMP_ID)
---------- ----------------
             1              100
 

Optimizer picked the best plan, which is an index scan, based on the low cardinality estimate for the current bind value of 10:



SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------

SQL_ID  a5upref8bn9, child number 0
-------------------------------------
SELECT COUNT(*), MAX(emp_id) FROM scott.emp WHERE dept_id = :dept_id;
Plan hash value: 4565465905
-------------------------------------------------------------------------------------
| Id| Operation                           | Name       |Rows|Bytes|Cost (%CPU)|Time |
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                     |                   |  |  |2(100)|        |
| 1|  SORT AGGREGATE                      |                   |1 |8 |      |        |
| 2|   TABLE ACCESS BY INDEX ROWID BATCHED| EMP            |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPT_IX    |1 |  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPT_ID"=:DEPT_ID)
  


The V$SQL output now shows that three child cursors exist:
    


  SELECT SQL_TEXT, CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC,
       BUFFER_GETS AS BUFF_GETS, IS_BIND_SENSITIVE AS BIND_SENS,
       IS_BIND_AWARE AS BIND_AWARE, IS_SHAREABLE AS SHARABLE
FROM   V$SQL
WHERE  SQL_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT             CHILD#  EXEC  BUFF_GETS BIND_SENS BIND_AWARE SHARABLE
----------------------   ------ ----- ---------- --------- ---------- --------
SELECT COUNT(*),         0     2        1649         Y          N        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id

SELECT COUNT(*),         1     1        700         Y          Y        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id

SELECT COUNT(*),         2     1        3         Y          Y        Y
MAX(emp_id) FROM scott.em
p WHERE dept_id = :dept_id


The database discarded the original cursor (CHILD# of 0) when the cursor switched to bind-aware mode. This is a one-time overhead. The database marked cursor 0 as not shareable (SHAREABLE is N), which means that this cursor is unusable and will be among the first to age out of the cursor cache.

Related Links :-



No comments:

Post a Comment