Adaptive Cursor Sharing

This feature was introduced in 11g release 1 to cater a performance issue caused by bind variable peeking. Adaptive cursor sharing means that if an SQL is submitted for execution that has bind variables, and it already has an execution plan in shared pool, still optimizer can generate a new execution plan based on the values passed in the bind variable this time. This means that on every call of an SQL, optimizer may use already available plans in the shared pool, or it may generate a new one that is more efficient based on the bind variable values.

If we have a table with a highly skewed column, for example, a column “GENDER” which has “F” (female) for 95% of the rows, and rest of 5% rows have “M”. With adaptive cursor sharing, if first time query executes with the bind variable (supposed  v_gender) value set to “M”, optimizer will probably generate an execution plan with INDEX RANGE SCAN. Later, if same query comes again having value of bind variable set to “F”, optimizer can check at run time whether current execution plan is best for the current bind variable values of the SQL or not. If it senses that current plan is not good for these values, it will generate a new optimized execution plan.

I will try to explain this with an example. In this example, I am creating a table with a highly skewed column object_id and then we will see how optimizer selects different plans for same SQL based on the values use din bind variable.

SQL> create table test as select * from dba_objects; Table created. --Execute Following SQL 17 times. In the end you will have 65536 rows for object_id=100. SQL> insert into test select * from test where object_id=100; 65536 rows created. SQL> commit; Commit complete. SQL> select count(*) from test;   COUNT(*) ----------     222501 --Create index on object_id SQL> create index test_idx on test (object_id); Index created. --Gather statistics on the table and index SQL> exec dbms_stats.gather_table_stats('c##salman','test',cascade=>true) PL/SQL procedure successfully completed.

Now I will execute a SELECT statement using bind variable to select object name with object_id=102. Optimizer would select an execution plan with an INDEX SCAN because we are fetching only 1 row from our total 222501 rows and an index scan is the best way to fetch 1 row.

SQL> variable v_objid number; SQL> exec  :v_objid:=102; PL/SQL procedure successfully completed. SQL> select count(object_name) from test where object_id=:v_objid;   COUNT(*) ----------          1 --Check SQL ID and then current plan from the cursor cache. SQL> select sql_id from v$sql where sql_text like 'select count(object_name) from test where object_id=:v_objid'; SQL_ID ------------- bdgffww5rp2bn select * from table(dbms_xplan.display_cursor('bdgffww5rp2bn',0)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  bdgffww5rp2bn, child number 0 ------------------------------------- select count(object_name) from test where object_id=:v_objid Plan hash value: 939327728 ------------------------------------------------------------------------------------------------- | Id  | Operation                            | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                     |          |       |       |     2 (100)|          | |   1 |  SORT AGGREGATE                      |          |     1 |    18 |            |          | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TEST     |     1 |    18 |     2   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN                  | TEST_IDX |     1 |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("OBJECT_ID"=:V_OBJID)

We see that optimizer chose a plan with index scan.

Now we open another session and try to execute same SQL by passing object_id=100. Ideally there should be a FULL TABLE SCAN plan if object_id=100 because with this value, SQL would be fetching most of the rows form the table for which index scan is an expensive operation.

--Open a new Session ================== SQL> exec  :v_objid:=100 PL/SQL procedure successfully completed. SQL> select count(object_name) from test where object_id=:v_objid; COUNT(OBJECT_NAME) ------------------             131072 --Value of second parameter here is 1 this time as we are checking the child cursor of same SQL executed from a second session. SQL>  select * from table(dbms_xplan.display_cursor('bdgffww5rp2bn',1)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID  bdgffww5rp2bn, child number 1 ------------------------------------- select count(object_name) from test where object_id=:v_objid Plan hash value: 1950795681 --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |       |       |   932 (100)|          | |   1 |  SORT AGGREGATE    |      |     1 |    18 |            |          | PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |*  2 |   TABLE ACCESS FULL| TEST |   134K|  2368K|   932   (1)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter("OBJECT_ID"=:V_OBJID) 19 rows selected. --We can crosscheck about which session executed which child cursor for this SQL SQL> select sql_id,plan_hash_value,child_number from v$sql where sql_id='bdgffww5rp2bn'; SQL_ID        PLAN_HASH_VALUE        CHILD_NUMBER ------------- --------------- ---------- ------------------------------------------ bdgffww5rp2bn       939327728           0 bdgffww5rp2bn      1950795681          1 SQL> select sid,serial#,sql_id,SQL_CHILD_NUMBER from v$session where username='C##SALMAN';        SID    SERIAL#    SQL_ID             SQL_CHILD_NUMBER ---------- ---------- ------------- ---------------------------------         15      36193     bdgffww5rp2bn               0         21       6745      bdgffww5rp2bn               1

As we see above, 2 SQLs with different values passed in bind variables, optimizer selected a different plan for each execution. This is what adaptive cursor sharing is all about.