Adaptive Cursors in Oracle 11g


Up to Oracle 1og we have an algorythm, called Bind Peeking.

Briefly, this means the following:

  • In SQL statement we use a bind variable with not null value.
  • First time, when SQL statement was executed, the Oracle will apply Hard Parsing to establish the best execusion plan for this statement, using (“peeking”) the value of the bind variable.
  • Next time,when we use the same SQL, but with differernt bind value, the same execution plan will be used(called Soft Parsing)
  • This is very good for OLTP systems, where the data is dispersed approximately evenly.

In OLAP environment the Bind Peeking typically is not optimal, because there is no guarantee, that the data-according to the bind variable-is spreaded approximately evenly.

In Oracle 11g we can ask the server to see the subsequent values of the bind variable to determine whether the previous plan was optimal or Oracle has to create a new, better execution plan using relevant statistics (typically histograms).

Let’s see the following case study.

  • First, we create a simple table with an index for the record_type column.
  • Second, we populate it with the natural numbers from 1 to 10000.
  • Third, we modifiy the even numbers for the number 2. So, the half part of the table contains the same values and the other half part of the table contains different (odd) values.

CREATE TABLE ac_test
(id NUMBER, record_type NUMBER);
CREATE INDEX ac_test_ix ON ac_test(record_type);

DECLARE
TYPE test_tab_type IS TABLE OF ac_test%ROWTYPE INDEX BY PLS_INTEGER;
test_tab test_tab_type;
BEGIN
FOR i IN 1 .. 100000 LOOP
test_tab(I).id := i;
test_tab(I).record_type := i;
END LOOP;
FORALL i IN 1 .. test_tab.COUNT
INSERT INTO ac_test VALUES test_tab(i);
UPDATE ac_test SET record_type =2 WHERE MOD(record_type ,2)=0;
COMMIT;
END;
/

  • We clear the Shared Pool and make this feature enabled.
  • Using the DBMS_STATS package we create statistics (including histograms)

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION set “_optimizer_adaptive_cursor_sharing”= TRUE;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER, ‘ac_test’,
method_opt=>’for all indexed columns size skewonly’, cascade=>TRUE);
END;
/

  • Now we create and populate a bind variable with 1 and execute an SELECT statement
  • Examine the execution plan
  • Observe that that cursor is Bind Sensitive, but not Bind Aware


VARIABLE v_rec_type NUMBER;
EXEC :v_rec_type := 1
SELECT MAX(id) FROM ac_test WHERE record_type = :v_rec_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
COL IS_BIND_SENSITIVE FORMAT A20
COL IS_BIND_AWARE FORMAT A20
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM v$sql WHERE sql_text =
‘SELECT MAX(id) FROM ac_test WHERE record_type = :v_rec_type’;

The result is similar to the following:

-------------------------------------------------------------------------
| Id | Operation                        | Name       |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| 1 | SORT AGGREGATE
| 2 | TABLE ACCESS BY INDEX ROWID       | AC_TEST    |
|* 3 | INDEX RANGE SCAN                 | AC_TEST_IX |
-------------------------------------------------------------------------


SQL_ID           CHILD_NUMBER    IS_BIND_SENSITIVE    IS_BIND_AWARE
43ydhqbsh1t4x    0                               Y                N
  • Now we can repeat the previous process  with the value 2 of the bind variable

  • We can observe  that the execution plan is the same.

  • Repeat the process again with the value 2 of the bind variable!

  • We can observe  that the execution plan is different!  

     

    ----------------------------------------------
    | Id  | Operation                  | Name    |
    ----------------------------------------------
    |   0 | SELECT STATEMENT           |         | 
    |   1 |  SORT AGGREGATE            |         | 
    |*  2 |   TABLE ACCESS FULL        | AC_TEST |

     

    
    SQL_ID        CHILD_NUMBER       IS_BIND_SENSITIVE    IS_BIND_AWARE
    ------------- ------------        ----------------    --------------
    43ydhqbsh1t4x            0                         Y               N
    43ydhqbsh1t4x            1                         Y               Y
    

     

     

     

     

     

     

     

     

     

Advertisements

One Response to “Adaptive Cursors in Oracle 11g”

  1. Donnie Pawlowicz Says:

    Excellent blog post, I will be sure to bookmark this post in my Diigo account. Have a great day.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: