Inside Oracle 11g Adaptive Cursor Sharing Feature!!

 Author: Lilian

Page: 1 2 3 4

Execution Statistics:

SQL> select child_number, executions, buffer_gets,
is_bind_sensitive, is_bind_aware
from v$sql
where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------  -------------------------
      0          2          1007             Y              N
      1          1           821             Y              Y

By looking at the execution statistics we see that there is a new cursor, which represents the plan which uses a table scan. But if we execute the query again with a more selective bind value, we should use the index plan:

SQL> exec :deptno := 9

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

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

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

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

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)|
------------------------------------------------------------------------

If we look at the execution statistics again, there are three cursors now.

SQL> select child_number, executions, buffer_gets,
is_bind_sensitive, is_bind_aware, is_shareable
from v$sql
where sql_text like 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE      IS_SHAR
------------ ---------- ----------- ----------------  --------------     ---------
      0          2          957             Y              N               N
      1          1          765             Y              Y               Y
      2          2            6             Y              Y               Y

The original cursor was discarded when the cursor switched to bind aware mode. This is a one-time overhead. The cursor is marked as not shareable which means that this cursor will be among the first to be aged out of the cursor cache, and that it will no longer be used. In other words, it is just waiting to be garbage collected.

Additional Cursors in Oracle 11g:

Oracle 11g uses additional cursors for such queries because when a new bind value is used, the optimizer tries to find a cursor that it thinks will be a good fit, based on similarity in the bind value's selectivity. If it cannot find such a cursor, it will create a new one. If the plan for the new cursor is the same as one of the existing cursors, the two cursors will be merged, to save space in the cursor cache. This will result in one being left behind that is in a not shareable state. This cursor will be aged out first if there is crowding in the cursor cache, and will not be used for future executions. Read Again!!

Page: 1 2 3 4

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   SQL Injection Attacks - Are you safe?
   Secrets of Resources with AWR Reports, Great!!
   Oracle Tips for Monitoring Oracle Import Speed!!
   Performance Optimization by avoiding Table Locks!!