The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is “adaptive” because the cursor adapts its behaviour so that the database does not always use the same plan for each execution or bind variable value.

With bind peeking, the optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. The optimizer determines the cardinality of any WHERE clause condition as if literals had been used instead of bind variables. If a column in a WHERE clause has skewed data, however, then a histogram may exist on this column. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, this plan may not be good for all values.

For example, the optimizer might choose one plan for bind value 10 and a different plan for bind value 50. Cursor sharing is “adaptive” because the cursor adapts its behavior so that the optimizer does not always choose the same plan for each execution or bind variable value. Thus, the optimizer automatically detects when different execution of a statement would benefit from different execution plans

create index acs_deptid_idx on acs(dept_id);

With No Histogram

set lines 150

col TABLE_NAME for a10

col column_name for a10

col histogram for a20

select table_name, column_name, histogram from user_tab_cols where table_name = ‘ACS’ ;

Bind peeking

When there is no histogram on skew column optimizer has no way to know that data is skew. Bind peeking behavior changes with cursor_sharing value and literal / bind variable

  • cursor_sharing = EXACT and Literal  [No bind peeking – because a new cursor is opened every time]
  • cursor_sharing = EXACT and bind variables are used [Bind peeking]
  • cursor_sharing = FORCE (Literal and bind variables both will show bind peeking because literal automatically converted to bind variables internally ) [Bind peeking]

cursor_sharing = EXACT and Literal  [No bind peeking]

We will run these statements –

select count(1),max(salary) from ACS where dept_id = 2;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

select count(1),max(salary) from ACS where dept_id = 5;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

select count(1),max(salary) from ACS where dept_id = 7;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

select count(1),max(salary) from ACS where dept_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

set line 150

COL SQL_TEXT FORMAT a60

SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE

FROM   V$SQLAREA

WHERE  SQL_TEXT LIKE ‘%ACS where dept_id%’

AND    SQL_TEXT NOT LIKE ‘%SQL_TEXT%’ and SQL_TEXT NOT LIKE ‘%EMPTY%’

order by plan_hash_value;

set lines 150

COL SQL_TEXT FORMAT a60

COL CHILD# FORMAT 99999

COL EXEC FORMAT 9999

COL SCHEMA FORMAT a10

SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT,

CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL

WHERE SQL_TEXT LIKE ‘%ACS where dept_id%’ AND SQL_TEXT NOT LIKE ‘%SQL_TEXT%’ and SQL_TEXT NOT LIKE ‘%EMPTY%’ ORDER BY SQL_ID;

cursor_sharing = EXACT and Bind variable [Bind peeking]

we will run these statements – As seen above dept_id=10 should have a FULL TABLE SCAN but here all plans are same. Because first value passed in bind was 2 and it used index range scan. This will be used for bind peeking and optimizer will assume that all other values will also have same plan. IF THAT FIRST VALUE would have been 10 then all plans will have full table scan.

var dept number

exec :dept :=2;

select count(1),max(salary) from ACS where dept_id = :dept;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

exec :dept :=5;

select count(1),max(salary) from ACS where dept_id = :dept;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

exec :dept :=7;

select count(1),max(salary) from ACS where dept_id = :dept;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

exec :dept :=10;

select count(1),max(salary) from ACS where dept_id = :dept;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

Now let’s see IF THAT FIRST VALUE would have been 10 then all plans will do full table scan.

Cursor_sharing = FORCE and literal

FORCE mode converts all literals internally to bind variable hence even after literal value changes plan does not changes. Only one parent cursor and child cursor is used.

Values passed – Dept_id =2,5,7,10 (all have different count)

Solution to bind peeking = Adaptive cursor sharing (ACS)

Here we are taking example of cursor_sharing=EXACT and bind variable combination. Results will be same in cursor_sharing=FORCE

Right now there is now histogram on the table column dept_id which have skew data.

set lines 150

col TABLE_NAME for a10

col column_name for a10

col histogram for a20

select table_name, column_name, histogram from user_tab_cols where table_name = ‘ACS’ ;

If you run the query it will show that it is not bind sensitive

select sql_id, child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_text like ‘%ACS where dept_id%’ and sql_text not like ‘%is_bind_aware%’;

Now flush the shared pool and run the default stats. Frequency histogram will be created on dept_id column.

Now run the query again it should be bind sensitive but not bind aware. Make a note of buffer_gets

If you run the query with dept_id=10 it will use the same plan as of dept_id=2 But notice the difference in buffer gets

If you run query one more time with dept_id=10. It will adapt to a new plan

Notice that the old child cursor entry for sql_id= 76jdt7zvnmndc has been marked as non shareable. New entry is now bind sensitive, bind aware and shareable. Old child cursor is now is_shareable =N

If I run with dept_id=2 again it will pick the right plan and will create a new child cursor. Now both child 1 and 2 have is_shareable =Y

set line 150

COL SQL_TEXT FORMAT a60

SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE

FROM   V$SQLAREA

WHERE  SQL_TEXT LIKE ‘%ACS where dept_id%’

AND    SQL_TEXT NOT LIKE ‘%SQL_TEXT%’ and SQL_TEXT NOT LIKE ‘%EMPTY%’ and SQL_TEXT NOT LIKE ‘%select sql_id%’

order by plan_hash_value;

Adaptive cursor sharing in action when cursor_sharing=FORCE

If you want to see why different child cursor are created

select child_number, bind_mismatch, optimizer_mode_mismatch, literal_mismatch, auth_check_mismatch,TRANSLATION_MISMATCH,BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id = ‘grzc6s1tmkjyb’;