Adaptive cursor sharing was introduced by Oracle in 11g release 1 as a way to generate best plan for a SQL in all situation. Prior to adaptive cursor sharing, optimizer used to generate a single plan for a SQL statement and that plan is used by all cursors of that SQL_ID. So if we have data skewness in a column and that column is being using in “where” clause of the SQL statement, single plan of that SQL will not be optimum for all values of that column, which is passed to the bind variable.
Lets take a simple example. We will create a table and add random data to it.
SQL> create table T1 2 (emp_id number, 3 dept_id number, 4 salary number, 5 gender char(1), 6 dob date, 7 address varchar2(60)); Table created.
Let me add 10K records to this table
SQL> insert into T1
SELECT LEVEL,
(MOD (ROWNUM, 2)+1)*10,
TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2),
DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F'),
TO_DATE ( ROUND (DBMS_RANDOM.VALUE (1, 28))
|| '-'
|| ROUND (DBMS_RANDOM.VALUE (1, 12))
|| '-'
|| ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
'DD-MM-YYYY'
),
DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50))
FROM DUAL
CONNECT BY LEVEL < 10000;
9999 rows created.
We can make one of the column – DEPT_ID as skewed by updating the records as below
SQL> update T1 set dept_id = 10 where rownum <= 9950;
9950 rows updated.
SQL> update T1 set dept_id = 20 where dept_id != 10;
49 rows updated.
SQL> select dept_id, count(1) from T1 group by dept_id;
DEPT_ID COUNT(1)
---------- ----------
20 49
10 9950
So we have skewness for column DEPT_ID now.
Lets gather statistics on this table with cascade option so that it will gather statistics on index as well. Since the data is skew in index, it will also generate histogram.
SQL> exec dbms_stats.gather_table_stats(OWNNAME=>user, TABNAME=>'T1',CASCADE => TRUE , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254'); PL/SQL procedure successfully completed. SQL> select * from dba_histograms where TABLE_NAME = 'T1'; OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU ------------------------------ ------------------------------ ------------------------------ --------------- -------------- -------------------- ADVAITD_DBA T1 DEPT_ID 9950 10 ADVAITD_DBA T1 DEPT_ID 9999 20 SQL> select table_name, column_name, histogram from user_tab_cols where table_name = 'T1' and histogram != 'NONE'; TABLE_NAME COLUMN_NAME HISTOGRAM ------------------------------ ------------------------------ --------------- T1 DEPT_ID FREQUENCY
We can see it has created frequency histogram on the index column – DEPT_ID
Adaptive cursor sharing is affected by multiple factors. Following are some of the important factors which affects adaptive cursor sharing.
- Adaptive cursor sharing is enabled or not. This is the first basic factor which determines if adaptive cursor sharing is enabled or not
- CURSOR_SHARING parameter and what value we are setting for this parameter
- SQL plan baseline. If we have baselines enabled and what all SQL baselines are available for a SQL in ENABLED=YES and ACCEPTED=YES
- OPTIMIZER_INDEX_COST_ADJ – What is the value of this parameter set in database.
You can check if adaptive cursor sharing is enabled by checking the parameter _optimizer_adaptive_cursor_sharing
SQL>@param Enter Parameter name: _adaptive_cursor_sharing Parameter |Description |Session Va|Instance V -------------------------------------|------------------------------------------------------------|----------|---------- _optimizer_adaptive_cursor_sharing |optimizer adaptive cursor sharing |TRUE |TRUE
Now that we have adaptive cursor sharing enabled, lets see how it works and the effect of other factors on adaptive cursor sharing
How adaptive cursor sharing works?
So we have a table T1 with 10K records and one of the column DEPT_ID is having skewed data as seen above. We have 9950 records with DEPT_ID = 10 and 49 records with DEPT_ID = 20.
Lets try to run some queries and check how optimizer behaves
Running SQL for DEPT_NO = 20
SQL>exec :dept_no :=20;
SQL>select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = :dept_no;
COUNT(1)|MAX(DOB)
----------|----------------
49|2002-09-06:00:00
SQL>select sql_id, child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%';
SQL_ID |CHILD_NUMBER|PLAN_HASH_VALUE|EXECUTIONS|BUFFER_GETS|I|I|I
-------------|------------|---------------|----------|-----------|-|-|-
btj8b1hx3cf0g| 0| 3776485839| 1| 58|Y|N|Y
Pay attention to the columns IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE. Column IS_BIND_SENSITIVE tells whether output of the query is sensitive to values of bind variable. Meaning if we change the value of bind variable, will the output change significantly?
In above case, optimizer is showing IS_BIND_SENSITIVE=Y based on the histogram that is present in the column used in “where” clause.
As per Oracle definition, IS_BIND_SENSITIVE VARCHAR2(1) Indicates whether the cursor is bind sensitive (Y) or not (N). A query is considered bind-sensitive if the optimizer peeked at one of its bind variable values when computing predicate selectivities and where a change in a bind variable value may cause the optimizer to generate a different plan.
The plan its using currently is index range scan
select /*+ GATHER_PLAN_STATISTICS */ count(:"SYS_B_0"), max(dob) from
t1 where dept_id = :dept_no
Plan hash value: 3776485839
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 49 | 588 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_T1_DEPT_ID | 49 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPT_ID"=:DEPT_NO)
Lets run the query again but this time we will use the bind value of 10, which has 9950 records
Running SQL for DEPT_NO = 10
SQL>exec :dept_no := 10;
SQL>select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = :dept_no;
COUNT(1)|MAX(DOB)
----------|----------------
9950|2010-12-27:00:00
SQL>select sql_id, child_number, plan_hash_value , executions, buffer_gets, is_bind_sensitive, is_bind_aware,IS_SHAREABLE from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%';
SQL_ID |CHILD_NUMBER|PLAN_HASH_VALUE|EXECUTIONS|BUFFER_GETS|I|I|I
-------------|------------|---------------|----------|-----------|-|-|-
btj8b1hx3cf0g| 0| 3776485839| 2| 169|Y|N|Y
You can see now, we have 2 executions but same cursor is being used. During the second run, we used different bind value and optimizer becomes aware that using different bind value is actually fetching different number of records and plan is expensive.
So even though it didn’t generate any new cursor for this and used same plan as it had earlier, it will generate a new cursor when we run this again for DEPT_NO = 10 and hopefully it will also use a new plan (probably full table scan).
Lets run this query again for same DEPT_NO = 10
Running SQL for DEPT_NO = 10
SQL>exec :dept_no := 10;
SQL>select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = :dept_no;
COUNT(1)|MAX(DOB)
----------|----------------
9950|2010-12-27:00:00
SQL>select sql_id, child_number, plan_hash_value , executions, buffer_gets, is_bind_sensitive, is_bind_aware,IS_SHAREABLE from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%';
SQL_ID |CHILD_NUMBER|PLAN_HASH_VALUE|EXECUTIONS|BUFFER_GETS|I|I|I
-------------|------------|---------------|----------|-----------|-|-|-
btj8b1hx3cf0g| 0| 3776485839| 2| 169|Y|N|N
btj8b1hx3cf0g| 1| 3724264953| 1| 96|Y|Y|Y
Now, you see that it has created a new cursor along with a new plan and also is_bind_aware becomes YES for new cursor. At the same time, cursor # 0 becomes non-shareable (check IS_SHAREABLE column is showing ‘N’). So since optimizer knows that this query is bind sensitive, it will create new cursors which are bind aware and doesn’t use the cursor it created initially, which was not bind aware.
As per Oracle definition, IS_BIND_AWARE VARCHAR2(1) Indicates whether the cursor is bind aware (Y) or not (N). A query is considered bind-aware if it has been marked to use extended cursor sharing. The query would already have been marked as bind-sensitive.
Sometimes optimizer just creates a new cursor with same plan and it might go for index range scan even for selecting majority of rows from the table. This happened with me when I was trying this example. In such cases, cost of plan is determined by optimizer_index_cost_adj parameter. In my case, value of this parameter is set to lowest value of 1 so it will going for index range scan in all cases. Lower value of this variable represents lower cost for index access and it favors use of index. I set the value of this parameter to 50 and after that it was going for full table scan.
So if you check the plan for cursor 1, it will show full table scan as we are selecting 99% of rows from the table (DEPT_NO = 10 will fetch 9950 rows)
SQL>select * from table(dbms_xplan.display_cursor('btj8b1hx3cf0g',1));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID btj8b1hx3cf0g, child number 1
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(:"SYS_B_0"), max(dob) from
t1 where dept_id = :dept_no
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 27 (100)| |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
|* 2 | TABLE ACCESS FULL| T1 | 9950 | 116K| 27 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPT_ID"=:DEPT_NO)
At this point if we go for another execution with DEPT_NO = 20, it will create another cursor, which will be bind aware
Running SQL for DEPT_NO = 20
SQL>exec :dept_no := 20;
SQL>select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = :dept_no;
COUNT(1)|MAX(DOB)
----------|----------------
49|2002-09-06:00:00
SQL>select sql_id, child_number, plan_hash_value , executions, buffer_gets, is_bind_sensitive, is_bind_aware,IS_SHAREABLE from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%';
SQL_ID |CHILD_NUMBER|PLAN_HASH_VALUE|EXECUTIONS|BUFFER_GETS|I|I|I
-------------|------------|---------------|----------|-----------|-|-|-
btj8b1hx3cf0g| 0| 3776485839| 2| 169|Y|N|N
btj8b1hx3cf0g| 1| 3724264953| 1| 96|Y|Y|Y
btj8b1hx3cf0g| 2| 3776485839| 1| 3|Y|Y|Y
As we can see it created cursor 2 which is bind aware. So now cursor 0 is not shareable and will be removed from shared pool in sometime but cursor 1 and 2 are shareable and will be used by further executions.
Effect of bind peeking on adaptive cursor sharing
One of the important thing for using adaptive cursor sharing is that you need to enable bind peeking in database. Without enabling bind peeking, adaptive cursor sharing will not work.
Initially, in my environment, bind peeking was not enabled and because of that optimizer doesn’t have visibility on what values are being passed to the bind variables. So in that case optimizer will not generate new cursors even when adaptive cursor sharing is enabled. Optimizer does NOT even mark the cursor as bind sensitive as it doesn’t have any visibility in bind values
As you can see below, it was using same cursor for multiple executions
SQL>select sql_id, child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like '%test_sql%'; SQL_ID |CHILD_NUMBER|EXECUTIONS|BUFFER_GETS|I|I -------------|------------|----------|-----------|-|- 2bxcmkhms19jn| 0| 12| 220|N|N
If we check the bind peeking, it was turned off
SQL>@param Enter Parameter name: peek Parameter |Description |Session Va|Instance V -------------------------------------|------------------------------------------------------------|----------|---------- _optim_peek_user_binds |enable peeking of user binds |FALSE |FALSE
Effect of CURSOR_SHARING parameter on adaptive cursor sharing
Cursor sharing plays an important role when we are using adaptive cursor sharing. CURSOR_SHARING parameters determines if a cursor can be shared or a new cursor should be created. This was discussed very well on https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force
But will give a brief about how CURSOR_SHARING plays an important role.
Until 11g R2, we had 3 values for CURSOR_SHARING parameter
- EXACT: By setting this value, optimizer will not replace the literals used in the statement with system generated bind variables. So if you are using literals in the statement, each statement is considered as different depending on the literal values you have used.
- SIMILAR: Using this value, optimizer will replace the literals with system generated bind variable only if you don’t have histogram on the column for which you have used literal. If you have histogram, optimizer will NOT replace the literal with bind variable
- FORCE: This value will replace all the literals with system generated bind variables irrespective of whether a histogram is present on the column or not.
Example: we have following statement with literal
select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = 10;
This is the same statement that we used above and optimzer knows that DEPT_ID column is skewed and has histogram defined on it. Becuase it has histogram defined, optimizer knows that data is skewed and replacing the literal value with system generated bind variable can lead to suboptimal plan.
So if we use CURSOR_SHARING = SIMILAR and we have statements like above where we are using literal values on columns with histogram, optimizer will not replace the literal with bind variables.
In such cases adaptive cursor sharing will not kick in as no bind variables present in the query.
For adaptive cursor sharing to work in such situation, we need to drop histograms on the columns where we are using literal values. This has its own problems as data skewness cannot be determined by optimizer.
This is one of the main reason why a value of SIMILAR is deprecated in oracle 12c for CURSOR_SHARING parameter. Oracle encourages to use only FORCE and EXACT going forward.
Effect of baseline on adaptive cursor sharing
This is one of the thing that is covered in good detail in https://blogs.oracle.com/optimizer/entry/how_do_adaptive_cursor_sharing. I am just covering this topic briefly for the sake of completion.
So baseline has a different purpose than adaptive cursor sharing. While adaptive cursor sharing deals with using the right cursor and plan for a SQL statement depending on the bind vales we are passing and skewness of data, purpose of baseline is to provide the stability and avoid using any new unverified plan.
Adaptive cursor sharing kicks in before SQL management baseline. So when the statement is executed, adaptive cursor sharing will peak for bind values and will check the skewness of data. Based on these inputs, it will check if one of the existing cursor and plan can be used or it can derive a new better plan. Adaptive cursor sharing can tells optimizer that a new plan or a cursor needs to be created and after that control moves on to the next part of optimizer. So adaptive cursor sharing only suggests that a new cursor or plan is better and required to be created and provides all the required data.
Now, if we have SQL plan baseline already enabled on this SQL, it will prevent optimizer to use a new plan. However, optimizer can create a new plan and save that in the history of SQL management base. This new plan will be enabled but NOT ACCEPTED. EIther DBA has to manually accept the plan or have to wait for “sql tuning advisor” auto task to kick in and accept the baseline if its verified as good. Only after accepting the new plan, it will be used for the SQL.
In my case, I had a baseline created for the SQL initially.
SQL>exec :dept_no :=20;
SQL>select /*+ GATHER_PLAN_STATISTICS */ count(1), max(dob) from t1 where dept_id = :dept_no;
COUNT(1)|MAX(DOB)
----------|----------------
49|2002-09-06:00:00
If you check the explain plan for this SQL
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID btj8b1hx3cf0g, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(:"SYS_B_0"), max(dob) from
t1 where dept_id = :dept_no
Plan hash value: 3776485839
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 49 | 588 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_T1_DEPT_ID | 49 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPT_ID"=:DEPT_NO)
Note
-----
- SQL plan baseline SQL_PLAN_gwfyvvw5h1w96e68c0740 used for this statement
We can see baseline is getting used in the Note section. We can also see the details of baseline in DBA_SQL_PLAN_BASELINES table
SQL>select sql_handle, plan_name from dba_sql_plan_baselines where signature = (select exact_matching_signature from v$sql where sql_id = 'btj8b1hx3cf0g'); SQL_HANDLE |PLAN_NAME ------------------------------|------------------------------ SQL_fe3bdbdf0b00f126 |SQL_PLAN_gwfyvvw5h1w96e68c0740
Even after running the SQL multiple times for different bind values, we are not getting any new cursor. Also, the cursor is not marked as bind sensitive as shown in below output
SQL>select sql_id, child_number, plan_hash_value , executions, buffer_gets, is_bind_sensitive, is_bind_aware,IS_SHAREABLE from v$sql where sql_text like '%GATHER_PLAN_STATISTICS%'; SQL_ID |CHILD_NUMBER|PLAN_HASH_VALUE|EXECUTIONS|BUFFER_GETS|I|I|I -------------|------------|---------------|----------|-----------|-|-|- btj8b1hx3cf0g| 0| 3776485839| 5| 339|N|N|Y
So if we have SQL plan baselines enabled for a SQL and we have only 1 baseline accepted, we will not see the advantages of adaptive cursor sharing unless we have multiple baselines ACCEPTED so that optimizer can use different plan as suggested by adaptive cursor sharing.
This covers most of the situation for using adaptive cursor sharing.
Hope this helps !!
Reference
https://blogs.oracle.com/optimizer/entry/how_do_adaptive_cursor_sharing
https://blogs.oracle.com/optimizer/entry/update_on_adaptive_cursor_sharing
https://docs.oracle.com/database/121/REFRN/GUID-455358F8-D657-49A2-B32B-13A1DC53E7D2.htm#REFRN10025
Persistent Storage Solutions 
Oh! Wow a detailed write up Advait.
Very well covered article.
Excellent and nice explanation..Advait
Very nicely explained !!