15 Improving Real-World Performance Through Cursor Sharing

Example 15-9 Replacement of Literals with System Bind Variables

This example sets CURSOR_SHARING to FORCE at the session level, executes three statements containing literals, and displays the plan for each statement:

ALTER SESSION SET CURSOR_SHARING=FORCE;
SET LINESIZE 170
SET PAGESIZE 0
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 101;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 120;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
SELECT SUM(salary) FROM hr.employees WHERE employee_id < 165;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

The following DISPLAY_CURSOR output, edited for readability, shows that 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.

SQL_ID cxx8n1cxr9khn, child number 0
-------------------------------------
SELECT SUM(salary) FROM hr.employees WHERE employee_id < :"SYS_B_0"

Plan hash value: 2410354593

-------------------------------------------------------------------------------------
| 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| EMPLOYEES     |1 | 8 |2 (0)  |00:00:01|
|* 3 |    INDEX RANGE SCAN                  | EMP_EMP_ID_PK |1 |   |1 (0)  |00:00:01|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMPLOYEE_ID"<101)

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.

COL SQL_TEXT FORMAT a36
SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE
FROM   V$SQLAREA
WHERE  eee_TEXT LIKE '%mployee%'
AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                             SQL_ID        VERSION_COUNT HASH_VALUE
------------------------------------ ------------- ------------- ----------
SELECT SUM(salary) FROM hr.employees cxx8n1cxr9khn             1  997509652
 WHERE employee_id < :"SYS_B_0"

Example 15-10 Column with Significant Data Skew

This example assumes that the hr.employees.department_id column has significant data skew. SYSTEM executes the following setup code, which adds 100,000 employees in department 50 to the employees table in the sample schema, for a total of 100,107 rows, and then gathers table statistics:

DELETE FROM hr.employees WHERE employee_id > 999;

ALTER TABLE hr.employees DISABLE NOVALIDATE CONSTRAINT emp_email_uk;

DECLARE
v_counter NUMBER(7) := 1000;
BEGIN
 FOR i IN 1..100000 LOOP    
 INSERT INTO hr.employees 
   VALUES (v_counter,null,'Doe','Doe@example.com',null,'07-JUN-02','AC_ACCOUNT',null,null,null,50);
 v_counter := v_counter + 1;
 END LOOP;
END;
/
COMMIT; 

EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'hr', tabname => 'employees');

ALTER SYSTEM FLUSH SHARED_POOL;

The following query shows a histogram on the employees.department_id column:

COL TABLE_NAME FORMAT a15
COL COLUMN_NAME FORMAT a20
COL HISTOGRAM FORMAT a9

SELECT TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM   DBA_TAB_COLS
WHERE  OWNER = 'HR'
AND    TABLE_NAME = 'EMPLOYEES'
AND    COLUMN_NAME = 'DEPARTMENT_ID';

TABLE_NAME      COLUMN_NAME          HISTOGRAM
--------------- -------------------- ---------
EMPLOYEES       DEPARTMENT_ID        FREQUENCY

Example 15-11 Low-Cardinality Query

This example continues the example in Example 15-10. The following query shows that the value 10 has extremely low cardinality for the column department_id, occupying .00099% of the rows:

VARIABLE dept_id NUMBER
EXEC :dept_id := 10;
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id;

  COUNT(*) MAX(EMPLOYEE_ID)
---------- ----------------
         1              200

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  a9upgaqqj7bn5, child number 0
-------------------------------------
select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id

Plan hash value: 1642965905

-------------------------------------------------------------------------------------
| 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| EMPLOYEES         |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |1 |  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPARTMENT_ID"=:DEPT_ID)

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

COL BIND_AWARE FORMAT a10
COL SQL_TEXT FORMAT a22
COL CHILD# FORMAT 99999
COL EXEC FORMAT 9999
COL BUFF_GETS FORMAT 999999999
COL BIND_SENS FORMAT a9
COL SHAREABLE FORMAT a9

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 SHAREABLE
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 SHAREABLE
---------------------- ------ ----- ---------- --------- ---------- ---------
SELECT COUNT(*), MAX(e      0     1        196         Y          N         Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

The preceding 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 department_id column to compute the selectivity of the predicate WHERE department_id = :dept_id. Because the presence of the histogram indicates that the column is skewed, different values of the bind variable may require different plans.

Example 15-12 High-Cardinality Query

This example continues the example in Example 15-11. The following code re-executes the same query using the value 50, which occupies 99.9% of the rows:

EXEC :dept_id := 50;
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_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 department_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  a9upgaqqj7bn5, child number 0
-------------------------------------
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id

Plan hash value: 1642965905

-------------------------------------------------------------------------------------
| 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| EMPLOYEES         |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |1 |  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPARTMENT_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 SHAREABLE
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 SHAREABLE
---------------------- ------ ----- ---------- --------- ---------- ---------
SELECT COUNT(*), MAX(e      0     2       1329         Y          N         Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

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

Example 15-13 Bind-Aware Cursors

This example continues the example in "Bind-Sensitive Cursors". The following code issues a second query employees with the bind variable set to 50:

EXEC :dept_id := 50;
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_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 50:

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

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a9upgaqqj7bn5, child number 1
-------------------------------------
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id

Plan hash value: 1756381138

-----------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost(%CPU)| Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |254 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |     8 |         |          |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |  100K |  781K |254  (15)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPARTMENT_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 SHAREABLE
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 SHAREABLE
---------------------- ------ ----- ---------- --------- ---------- ---------
SELECT COUNT(*), MAX(e      0     2       1329         Y          N         N
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

SELECT COUNT(*), MAX(e      1     1        800         Y          Y         Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_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.

Example 15-14 Bind-Aware Cursors: Choosing the Optimal Plan

This example continues the example in "Example 15-13". The following code executes the same employees query with the value of 10, which has extremely low cardinality (only one row):

EXEC :dept_id := 10;
SELECT COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id;

  COUNT(*) MAX(EMPLOYEE_ID)
---------- ----------------
         1              200

The following output shows that the 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  a9upgaqqj7bn5, child number 2
-------------------------------------
select COUNT(*), MAX(employee_id) FROM hr.employees WHERE department_id = :dept_id

Plan hash value: 1642965905

-------------------------------------------------------------------------------------
| 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| EMPLOYEES         |1 |8 |2  (0)|00:00:01|
|*3|    INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX | 1|  |1  (0)|00:00:01|
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPARTMENT_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 SHAREABLE
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 SHAREABLE
---------------------- ------ ----- ---------- --------- ---------- ---------
SELECT COUNT(*), MAX(e      0     2       1329         Y          N         N
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

SELECT COUNT(*), MAX(e      1     1        800         Y          Y         Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_id = :dept_id

SELECT COUNT(*), MAX(e      2     1          3         Y          Y         Y
mployee_id) FROM hr.em
ployees WHERE departme
nt_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.