Tuesday, December 12, 2017

Bind-Sensitive Cursors

Bind-Sensitive Cursors

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.
The optimizer uses the below criteria to decide whether a cursor is bind-sensitive:
 • The optimizer has peeked at the bind values to generate cardinality estimates.
 • The bind is used in an equality or a range predicate.

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.


Let's assumes that the scott.emp.dept_id column has significant data skew.

Below code adds 100,000 employees in department 60 to the emp table in the scott schema and then gathers table statistics:


 DELETE FROM scott.emp WHERE emp_id > 999;

 ALTER TABLE scott.emp DISABLE NOVALIDATE CONSTRAINT emp_email_uk;

 DECLARE
 v_counter NUMBER(7) := 1000;
 BEGIN
  FOR i IN 1..100000 LOOP   
  INSERT INTO scott.emp
    VALUES (v_counter,null,'abc','abc@test.com',null,'17-JAN-17','A_ACC',null,null,null,60);
  v_counter := v_counter + 1;
  END LOOP;
 END;
 /
 COMMIT;

 EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'scott', tabname => 'emp');

 ALTER SYSTEM FLUSH SHARED_POOL;


A histogram on the emp.dept_id column:


 SELECT TABLE_NAME, COLUMN_NAME, HISTOGRAM FROM   DBA_TAB_COLS WHERE  OWNER =  'SCOTT' AND    TABLE_NAME = 'EMP' AND    COLUMN_NAME = 'DEPT_ID';

 TABLE_NAME      COLUMN_NAME          HISTOGRAM
 --------------- -------------------- ---------
 EMP       DEPT_ID        FREQUENCY


The following query shows that the value 10 has extremely low cardinality for the column dept_id:


  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


The optimizer chooses an index range scan, as expected for such a low-cardinality query:


  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)


Let's 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 '%emp%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

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


Output shows one child cursor that has been executed once for the low-cardinality query. The cursor has been marked bind-sensitive because the optimizer believes the optimal plan may depend on the value of the bind variable.

When a cursor is marked bind-sensitive, Oracle Database monitors the behavior of the cursor using different bind values, to determine whether a different plan for different bind values is more efficient. The database marked this cursor bind-sensitive because the optimizer used the histogram on the dept_id column to compute the selectivity of the predicate WHERE dept_id = :dept_id.

Presence of the histogram indicates that the column is skewed, different values of the bind variable may require different plans.

The following code re-executes the same query using the value 60, which occupies most of the rows:

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


Even though such an unselective query would be more efficient with a full table scan, the optimizer chooses the same index range scan used for dept_id=10. This reason is that the database assumes that the existing plan in the cursor can be shared.


  
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)


A query of V$SQL shows that the child cursor has now been executed twice:



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


At this stage, the optimizer has not yet marked the cursor as bind-aware.

In next post we'll see when and how optimizer will mark this bind-sensitive cursor to bind-aware.

No comments:

Post a Comment