Bind variables peeking and Adaptive Cursor Sharing (ACS)

Introduction

Since ages Oracle has always said that a well written application should use bind variables to avoid hard parsing and to limit shared pool usage (this can also be found on multiple blogs dealing with development). One of the first drawbacks of bind variables are the explain plan. Initially Oracle optimlizer did not know what was the value of the bind vairable when first parsing (hard parsing) the query and statistics/histograms were not used correctly and so generating quite often a bad plan.

This has been corrected in Oracle 9i with bind variable peeking. Since this release Oracle optimizer is able to peek the value of the bind variable and generate a plan like if the query where with a fixed value and so using histograms and statistics. issue you could see with this si that it is the first query that is issued that generate an explain plan for all the further query. For example the first query could do a full table scan while 98% of the query would better benefit from an index usage (see example below). This has finally been solved in 11g with Adaptive Cursor Sharing (ACS) that allow Oracle to generate multiple child of the same parent cursor in case multiple value of the bind variable would make the query run faster with a different plan (see example below).

Please note, as stated in official documentation, that ACS is independent of CURSOR_SHARING value.

Testing

To test it we will use below test table:

DROP TABLE test1;
 
CREATE TABLE test1(id NUMBER, descr VARCHAR(50)) TABLESPACE users;
 
DECLARE
i NUMBER;
nbrows NUMBER;
BEGIN
  i:=1;
  nbrows:=50000;
  LOOP
    EXIT WHEN i>nbrows;
    IF (i=1) THEN
      INSERT INTO test1 VALUES(1,RPAD('A',49,'A'));
    ELSE
      INSERT INTO test1 VALUES(nbrows,RPAD('A',49,'A'));
    END IF;
    i:=i+1;
  END LOOP;
  COMMIT;
END;
/
 
CREATE INDEX test1_idx_id ON test1(id) TABLESPACE users;
 
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 2', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE);

Before Oracle 11g

Below example has been done on a 10.2.0.4.0 Oracle release on HPUX Itanium 11.23:

Better issue two times the following statement in a SYSDBA session before starting:

SQL> ALTER SYSTEM flush shared_pool;
 
SYSTEM altered.
 
SQL> ALTER SYSTEM flush shared_pool;
 
SYSTEM altered.
SQL> SET lines 130
SQL> SET autotrace traceonly STATISTICS
SQL> variable id NUMBER;
SQL> EXEC :id:=50000;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT /* Yannick */ * FROM test1 WHERE id=:id;
 
49999 ROWS selected.
 
 
STATISTICS
----------------------------------------------------------
        312  recursive calls
          0  db block gets
       3629  consistent gets
        254  physical reads
          0  redo SIZE
     860598  bytes sent via SQL*Net TO client
      37151  bytes received via SQL*Net FROM client
       3335  SQL*Net roundtrips TO/FROM client
          7  sorts (memory)
          0  sorts (disk)
      49999  ROWS processed
SQL> SELECT sql_id,child_number,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
5vywrtthcud8x            0 SELECT /* Yannick */ * FROM test1 WHERE id=:id

With below explain plan (as expected):

PLAN
-----------------------------------------------------------------------------------------------------------------------------
(1 0)   |  TABLE ACCESS FULL.....................(YJAQUIER.TEST1) COST:95 (1499970 bytes, 49999 ROWS) io COST=94,cpu_cost=13406471

Now let’s issue a query with a bind variable that would benefit from an index access:

SQL> EXEC :id:=1;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT /* Yannick */ * FROM test1 WHERE id=:id;
 
 
STATISTICS
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        252  consistent gets
        248  physical reads
          0  redo SIZE
        622  bytes sent via SQL*Net TO client
        488  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

If we check V$SQL table:

SQL> SELECT sql_id,child_number,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
5vywrtthcud8x            0 SELECT /* Yannick */ * FROM test1 WHERE id=:id

And that’s the main issue of bind peeking, Oracle is sharing the parent cursor for all queries whatever the value of the bind variable and when ID=1 Oracle will still uses a FTS instead of the index range scan. Note that the situation would have better if the first query was ID=1 instead of ID=50000, because whatever the value of ID (except 50000 obviously) the good plan is the one using the index…

It’s anyway expected behavior and it looks quite complex to get rid of it but Oracle made it with 11g and called this feature adaptive cursor sharing (ACS).

Starting with Oracle 11g

Below example has been done on a 11.2.0.1.0 Oracle release on RedHat 5.2:

What’s new in 11g: Oracle added two columns to v$SQL table:

  • IS_BIND_SENSITIVE: Tells you if optimizer peeked a bind variable value and if a different value may change the explain plan.
  • IS_BIND_AWARE: Tells you if a query uses cursor sharing, occurs only after the query has been marked bind sensitive.

And created three new views:

  • V$SQL_CS_HISTOGRAM
  • V$SQL_CS_SELECTIVITY
  • V$SQL_CS_STATISTICS

Flush two times the shared pool in a SYSDBA session and issue the following:

SQL> SET lines 130
SQL> SET autotrace traceonly STATISTICS
SQL> variable id NUMBER;
SQL> EXEC :id:=50000;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT /* Yannick */ * FROM test1 WHERE id=:id;
 
49999 ROWS selected.
 
 
STATISTICS
----------------------------------------------------------
        333  recursive calls
          0  db block gets
       3825  consistent gets
         39  physical reads
          0  redo SIZE
     673889  bytes sent via SQL*Net TO client
      37082  bytes received via SQL*Net FROM client
       3335  SQL*Net roundtrips TO/FROM client
          7  sorts (memory)
          0  sorts (disk)
      49999  ROWS processed

It produces in V$SQL (note the Y value for is_bind_sensitive column of V$SQL) and other new views:

SQL> SELECT sql_id,child_number,is_bind_sensitive,is_bind_aware,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER I I SQL_TEXT
------------- ------------ - - --------------------------------------------------------------------------------
5vywrtthcud8x            0 Y N SELECT /* Yannick */ * FROM test1 WHERE id=:id
 
SQL> SELECT * FROM V$SQL_CS_HISTOGRAM WHERE sql_id='5vywrtthcud8x';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
-------- ---------- ------------- ------------ ---------- ----------
3FB4F2FC 1624061213 5vywrtthcud8x            0          0          0
3FB4F2FC 1624061213 5vywrtthcud8x            0          1          1
3FB4F2FC 1624061213 5vywrtthcud8x            0          2          0
 
SQL> SELECT * FROM V$SQL_CS_SELECTIVITY WHERE sql_id='5vywrtthcud8x';
 
no ROWS selected
 
SQL> SELECT * FROM V$SQL_CS_STATISTICS WHERE sql_id='5vywrtthcud8x';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
-------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
3FB4F2FC 1624061213 5vywrtthcud8x            0          1293584543 Y          1          49999        3825          0

Producing the below explain plan:

PLAN
-----------------------------------------------------------------------------------------------------------------------------
(0 0)   |SELECT STATEMENT .......................COST:136  io COST=,cpu_cost=
(1 0)   |  TABLE ACCESS FULL.....................(YJAQUIER.TEST1) COST:136 (1499970 bytes, 49999 ROWS) io COST=136,cpu_cost=14532214

Now let’s change the value of the variable and execute two times the query (mandatory to make Oracle generating a new plan):

SQL> EXEC :id:=1;
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT /* Yannick */ * FROM test1 WHERE id=:id;
 
 
STATISTICS
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        443  consistent gets
          0  physical reads
          0  redo SIZE
        525  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
 
SQL> SELECT /* Yannick */ * FROM test1 WHERE id=:id;
 
 
STATISTICS
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo SIZE
        529  bytes sent via SQL*Net TO client
        419  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

Let’s check again V$SQL and three new views:

SQL> SELECT sql_id,child_number,is_bind_sensitive,is_bind_aware,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER I I SQL_TEXT
------------- ------------ - - --------------------------------------------------------------------------------
5vywrtthcud8x            0 Y N SELECT /* Yannick */ * FROM test1 WHERE id=:id
5vywrtthcud8x            1 Y Y SELECT /* Yannick */ * FROM test1 WHERE id=:id
 
SQL> SELECT * FROM V$SQL_CS_HISTOGRAM WHERE sql_id='5vywrtthcud8x';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
-------- ---------- ------------- ------------ ---------- ----------
3FB4F2FC 1624061213 5vywrtthcud8x            1          0          1
3FB4F2FC 1624061213 5vywrtthcud8x            1          1          0
3FB4F2FC 1624061213 5vywrtthcud8x            1          2          0
3FB4F2FC 1624061213 5vywrtthcud8x            0          0          1
3FB4F2FC 1624061213 5vywrtthcud8x            0          1          1
3FB4F2FC 1624061213 5vywrtthcud8x            0          2          0
 
6 ROWS selected.
 
SQL> SELECT * FROM V$SQL_CS_SELECTIVITY WHERE sql_id='5vywrtthcud8x';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
3FB4F2FC 1624061213 5vywrtthcud8x            1 =ID                                               0 0.000018   0.000022
 
SQL> SELECT * FROM V$SQL_CS_STATISTICS WHERE sql_id='5vywrtthcud8x';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
-------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
3FB4F2FC 1624061213 5vywrtthcud8x            1          2342552567 Y          1              2           4          0
3FB4F2FC 1624061213 5vywrtthcud8x            0          1293584543 Y          1          49999        3825          0

Producing following explain plan:

PLAN
-----------------------------------------------------------------------------------------------------------------------------
(0 0)   |SELECT STATEMENT .......................COST:2  io COST=,cpu_cost=
(1 0)   |  TABLE ACCESS BY INDEX ROWID...........(YJAQUIER.TEST1) COST:2 (30 bytes, 1 ROWS) io COST=2,cpu_cost=15463
(2 1)   |    INDEX RANGE SCAN....................(YJAQUIER.TEST1_IDX_ID) COST:1 ( bytes, 1 ROWS) io COST=1,cpu_cost=8171

If you execute the query with ID=25000 you will get:

SQL> SELECT sql_id,child_number,is_bind_sensitive,is_bind_aware,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER I I SQL_TEXT
------------- ------------ - - --------------------------------------------------------------------------------
5vywrtthcud8x            0 Y N SELECT /* Yannick */ * FROM test1 WHERE id=:id
5vywrtthcud8x            1 Y Y SELECT /* Yannick */ * FROM test1 WHERE id=:id
5vywrtthcud8x            2 Y Y SELECT /* Yannick */ * FROM test1 WHERE id=:id
 
SQL> SELECT * FROM V$SQL_CS_HISTOGRAM WHERE sql_id='5vywrtthcud8x';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
-------- ---------- ------------- ------------ ---------- ----------
3FB4F2FC 1624061213 5vywrtthcud8x            2          0          1
3FB4F2FC 1624061213 5vywrtthcud8x            2          1          0
3FB4F2FC 1624061213 5vywrtthcud8x            2          2          0
3FB4F2FC 1624061213 5vywrtthcud8x            1          0          1
3FB4F2FC 1624061213 5vywrtthcud8x            1          1          0
3FB4F2FC 1624061213 5vywrtthcud8x            1          2          0
3FB4F2FC 1624061213 5vywrtthcud8x            0          0          1
3FB4F2FC 1624061213 5vywrtthcud8x            0          1          1
3FB4F2FC 1624061213 5vywrtthcud8x            0          2          0
 
9 ROWS selected.
 
SQL> SELECT * FROM V$SQL_CS_SELECTIVITY WHERE sql_id='5vywrtthcud8x';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
3FB4F2FC 1624061213 5vywrtthcud8x            2 =ID                                               0 0.000009   0.000022
3FB4F2FC 1624061213 5vywrtthcud8x            1 =ID                                               0 0.000018   0.000022
 
SQL> SELECT * FROM V$SQL_CS_STATISTICS WHERE sql_id='5vywrtthcud8x';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
-------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
3FB4F2FC 1624061213 5vywrtthcud8x            2          3704044026 Y          1              0           2          0
3FB4F2FC 1624061213 5vywrtthcud8x            1          2342552567 Y          1              2           4          0
3FB4F2FC 1624061213 5vywrtthcud8x            0          1293584543 Y          1          49999        3825          0

The explain plan for child number 2 is the same as the one of child number 0.

Please note that if you issue again the query with ID=50000, the initial query, strangely, you will get:

SQL> SELECT sql_id,child_number,is_bind_sensitive,is_bind_aware,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER I I SQL_TEXT
------------- ------------ - - --------------------------------------------------------------------------------
5vywrtthcud8x            0 Y N SELECT /* Yannick */ * FROM test1 WHERE id=:id
5vywrtthcud8x            1 Y Y SELECT /* Yannick */ * FROM test1 WHERE id=:id
5vywrtthcud8x            2 Y Y SELECT /* Yannick */ * FROM test1 WHERE id=:id
5vywrtthcud8x            3 Y Y SELECT /* Yannick */ * FROM test1 WHERE id=:id
 
SQL> SELECT * FROM V$SQL_CS_HISTOGRAM WHERE sql_id='5vywrtthcud8x';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
-------- ---------- ------------- ------------ ---------- ----------
3FB4F2FC 1624061213 5vywrtthcud8x            3          0          0
3FB4F2FC 1624061213 5vywrtthcud8x            3          1          1
3FB4F2FC 1624061213 5vywrtthcud8x            3          2          0
3FB4F2FC 1624061213 5vywrtthcud8x            2          0          2
3FB4F2FC 1624061213 5vywrtthcud8x            2          1          0
3FB4F2FC 1624061213 5vywrtthcud8x            2          2          0
3FB4F2FC 1624061213 5vywrtthcud8x            1          0          1
3FB4F2FC 1624061213 5vywrtthcud8x            1          1          0
3FB4F2FC 1624061213 5vywrtthcud8x            1          2          0
3FB4F2FC 1624061213 5vywrtthcud8x            0          0          1
3FB4F2FC 1624061213 5vywrtthcud8x            0          1          1
3FB4F2FC 1624061213 5vywrtthcud8x            0          2          0
 
12 ROWS selected.
 
SQL> SELECT * FROM V$SQL_CS_SELECTIVITY WHERE sql_id='5vywrtthcud8x';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER PREDICATE                                  RANGE_ID LOW        HIGH
-------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ----------
3FB4F2FC 1624061213 5vywrtthcud8x            3 =ID                                               0 0.899973   1.099967
3FB4F2FC 1624061213 5vywrtthcud8x            2 =ID                                               0 0.000009   0.000022
3FB4F2FC 1624061213 5vywrtthcud8x            1 =ID                                               0 0.000018   0.000022
 
SQL> SELECT * FROM V$SQL_CS_STATISTICS WHERE sql_id='5vywrtthcud8x';
 
ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
-------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
3FB4F2FC 1624061213 5vywrtthcud8x            3          1293584543 Y          1          49999        3776          0
3FB4F2FC 1624061213 5vywrtthcud8x            2          3704044026 Y          1              0           2          0
3FB4F2FC 1624061213 5vywrtthcud8x            1          2342552567 Y          1              2           4          0
3FB4F2FC 1624061213 5vywrtthcud8x            0          1293584543 Y          1          49999        3825          0

Means that a new child cursor (with the same explain plan as the initial child) is generated and the initial cursor (child equal to 0) will not be used any more and will be one of the first to aged out. By querying is_shareable column of V$SQL table we can see it:

SQL> SELECT sql_id,child_number,is_bind_sensitive,is_bind_aware,is_shareable,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%';
 
SQL_ID        CHILD_NUMBER I I I SQL_TEXT
------------- ------------ - - - --------------------------------------------------------------------------------
5vywrtthcud8x            0 Y N N SELECT /* Yannick */ * FROM test1 WHERE id=:id
5vywrtthcud8x            1 Y Y N SELECT /* Yannick */ * FROM test1 WHERE id=:id
5vywrtthcud8x            2 Y Y Y SELECT /* Yannick */ * FROM test1 WHERE id=:id
5vywrtthcud8x            3 Y Y Y SELECT /* Yannick */ * FROM test1 WHERE id=:id

Remark:
We also see that child number 1 is also not shareable anymore and will so be aged out. When the plan generated for a different value of the bind variable is the same as an existing one then Oracle merge the two cursors (internally Oracle increase the selectivity range of the new cursor to include the selectivity of the new bind) and the first one is marked as not shareable (this is what happened between child 1 and 2, both are doing an index range scan).

References

3 thoughts on “Bind variables peeking and Adaptive Cursor Sharing (ACS)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>