Table of contents
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
- Adaptive Cursor Sharing in 11G [ID 836256.1]
- Bind Variable Peeking – Drives Me Nuts!
- Bind variable peeking in 11G
- Why are there more cursors in 11g for my query containing bind variables?
- Explain adaptive cursor sharing behavior with cursor_sharing = similar and force
One thought on “Bind variables peeking and Adaptive Cursor Sharing (ACS)”