Inside Oracle 11g Adaptive Cursor Sharing Feature!!

 Author: Lilian

Page: 1 2 3 4

Oracle adaptive cursor sharing feature is introduced with 11g. Oracle 11g Adaptive Cursor Sharing feature improve the plans that are selected for queries containing bind variables. Bind peeking has been around for quite awhile and works well when all the values used for the binds in your statement have similar selectivity. However this approach has a problem when the column used in the WHERE clause with the bind contains data skew. The plan is optimized for the peeked value of the bind variable, but not for all possible values. Adaptive Cursor Sharing feature allows cursors using bind variables the possibility of getting new plans when the value of the bind changes.

Oracle 11g has enhanced optimizer to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value.

Consider a table emp with 100,000 rows and one index called emp_i1 on deptno column.

SQL> desc emp

        Name          Null?                 Type
        ----        -------         -------------
        ENAME                                 VARCHAR2(20)
        EMPNO                                 NUMBER
        PHONE                                 VARCHAR2(20)
        DEPTNO                               NUMBER

Histogram:

A histogram is created automatically on the deptno table when we gather statistics on the emp table. This is due to data skew in the deptno column.

SQL> select table_name, column_name, histogram from user_tab_cols;

        TABLE_NAME         COLUMN_NAME         HISTOGRAM
        -----------       ------------     ---------------------
                EMP                         DEPTNO                HEIGHT BALANCED
                EMP                         EMPNO                   NONE
                EMP                         ENAME                   NONE
                EMP                         PHONE                   NONE

Bind Variable:

Now execute a simple select on emp table which has a single WHERE
clause predicate on the deptno column. The predicate contains a bind variable. We will begin by using the value 9 for this bind variable. The value 9 occurs 10 times in the table, i.e. in 0.0001% of the rows.

SQL> exec :deptno := 9

SQL> select /*ACS_1*/ count(*), max(empno)
from emp
where deptno = :deptno;

              COUNT(*)               MAX(EMPNO)
              ----------          ----------
                  10                             99

Execution Plan:

The execution plan will give us the index range scan.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 272gr4hapc9w1, child number 0
------------------------------------------------------------------------

select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno

Plan hash value: 3184478295
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT    | | | | 2 (100)|
| 1 | SORT AGGREGATE       | | 1| 16 | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|
| 3 | INDEX RANGE SCAN    | EMP_I1| 1| | 1 (0)|
------------------------------------------------------------------------ Continued...

Page: 1 2 3 4

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Tuning Guide for Concurrent Disk I/O
   A Guide to Oracle Bitmap Index Techniques!!
   Bad DBA - Bad Practices
   Inside Oracle DETERMINISTIC Clause!!