Insights into Statistics, Query Optimization and the Oracle Optimizer
-
Cursor Sharing
March 12, 2010
Explain adaptive cursor sharing behavior with cursor_sharing = similar and force.
Here's another question that was submitted during the OpenWorld Optimizer Roundtable. It's a common question that we've discussed a little bit in a couple other posts, but we wanted to summarize everything in one place.
First, let's quickly review what the different values for the cursor_sharing parameter mean. We discussed this behavior in some detail in an earlier post about cursor_sharing. Below is a summary of the behavior of the different values in different cases (copied from the earlier post):
Adaptive cursor sharing (ACS) is another feature we've blogged about before, which allows the optimizer to generate a set of plans that are optimal for different sets of bind values. A common question is how the two interact, and whether users should consider changing the value of cursor_sharing when upgrading to 11g to take advantage of ACS. The simplest way to think about the interaction between the two features for a given query is to first consider whether literal replacement will take place for a query. Consider a query containing a literal:
select * from employees where job = 'Clerk'
As we see from the table above, the treatment of this query by literal replacement will depend on the value of the cursor_sharing parameter and whether there is a histogram on the job column. Here are the interesting cases:
This example shows that if you use histograms, and want the optimizer to choose an optimal plan for different literal values using ACS, then you should set cursor_sharing to force. If it is set to similar, then literal replacement will not take place, and a child cursor will be created for each value of the literal. Setting cursor_sharing to similar effectively disables ACS for these kinds of queries. By setting cursor_sharing to force and letting adaptive cursor sharing kick in, the optimizer can choose optimal plans for different values, but if the same plan is appropriate for several values, they will share a single child cursor. Historically, cursor_sharing=similar has been recommended as a middle ground between no literal replacement (which causes a lot of cursors to be generated) and forced literal replacement (which causes a potentially sub-optimal plan to be shared for all literals). We now recommend using adaptive cursor sharing along with cursor_sharing=force instead.
So far we have only discussed cursor sharing in the presence of histograms. There are other cases where the optimizer's choice of plan can depend on the specific literal that appears in the query, for instance when binds appear in range predicates or when a bind value falls outside of a column's range (according to the optimizer statistics). Binds appearing in these kinds of predicates are also considered by adaptive cursor sharing, whereas they are not considered by cursor_sharing=similar.
First, let's quickly review what the different values for the cursor_sharing parameter mean. We discussed this behavior in some detail in an earlier post about cursor_sharing. Below is a summary of the behavior of the different values in different cases (copied from the earlier post):
| CURSOR_SHARING VALUE | SPACE USED IN SHARED POOL | QUERY PERFORMANCE |
|---|---|---|
| EXACT (No literal replacement) | Worst possible case - each stmt issued has its own parent cursor | Best possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt |
| FORCE | Best possible case as only one parent and child cursor for each distinct stmt | Potentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan |
| SIMILAR without histogram present | Best possible case as only one parent and child cursor for each distinct stmt | Potentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan |
| SIMILAR with histogram present | Not quite as much space used as with EXACT but close. Instead of each stmt having its own parent cursor they will have their own child cursor (which uses less space) | Best possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt |
Adaptive cursor sharing (ACS) is another feature we've blogged about before, which allows the optimizer to generate a set of plans that are optimal for different sets of bind values. A common question is how the two interact, and whether users should consider changing the value of cursor_sharing when upgrading to 11g to take advantage of ACS. The simplest way to think about the interaction between the two features for a given query is to first consider whether literal replacement will take place for a query. Consider a query containing a literal:
select * from employees where job = 'Clerk'
As we see from the table above, the treatment of this query by literal replacement will depend on the value of the cursor_sharing parameter and whether there is a histogram on the job column. Here are the interesting cases:
- Cursor_sharing = exact. No literal replacement will take place, and the optimizer will see the query as is.
- Cursor_sharing = force. Whether there is a histogram or not, literal replacement will take place, and the optimizer will optimize the query as if it were:
select * from employees where job = :b
Bind peeking will take place, so that the value "Clerk" is used to generate cardinality estimates for the query. Subsequent executions of this query differing only in the literal value will share the same plan. - Cursor_sharing = similar. There are two different cases for this:
- There is a histogram on the job column. In this case, literal replacement will not take place. The presence of a histogram indicates that the column is skewed, and the optimal plan may depend on the literal value. Hence, the optimizer sees the query as:
select * from employees where job = 'Clerk'
and subsequent executions with a different literal will not necessarily use the same plan. - There is no histogram on the job column. This indicates that the column is not skewed, and the optimizer is likely to choose the same plan no matter the literal, so literal replacement takes place.
- There is a histogram on the job column. In this case, literal replacement will not take place. The presence of a histogram indicates that the column is skewed, and the optimal plan may depend on the literal value. Hence, the optimizer sees the query as:
This example shows that if you use histograms, and want the optimizer to choose an optimal plan for different literal values using ACS, then you should set cursor_sharing to force. If it is set to similar, then literal replacement will not take place, and a child cursor will be created for each value of the literal. Setting cursor_sharing to similar effectively disables ACS for these kinds of queries. By setting cursor_sharing to force and letting adaptive cursor sharing kick in, the optimizer can choose optimal plans for different values, but if the same plan is appropriate for several values, they will share a single child cursor. Historically, cursor_sharing=similar has been recommended as a middle ground between no literal replacement (which causes a lot of cursors to be generated) and forced literal replacement (which causes a potentially sub-optimal plan to be shared for all literals). We now recommend using adaptive cursor sharing along with cursor_sharing=force instead.
So far we have only discussed cursor sharing in the presence of histograms. There are other cases where the optimizer's choice of plan can depend on the specific literal that appears in the query, for instance when binds appear in range predicates or when a bind value falls outside of a column's range (according to the optimizer statistics). Binds appearing in these kinds of predicates are also considered by adaptive cursor sharing, whereas they are not considered by cursor_sharing=similar.
Join the discussion
Comments ( 2 )
<quote>
In case 2, if there is a histogram, then the optimizer may choose different plans depending on how popular the literal value is.
<quote>
I don't see different plans with cursor_sharing=Force as you said in the above article. Below is the test script i used for this demo.
alter system flush shared_pool;
drop table t purge;
create table t
nologging as
select a.*,
decode(rownum,1,1,99) id
from all_objects a;
create index t_ind on t(id);
begin
dbms_stats.gather_table_stats(ownname=>user,
tabname=>'T',
estimate_percent=>100,
cascade=>true,
method_opt=>'for all indexed columns size 254');
end;
/
alter session set cursor_sharing=force;
select max(object_id) from t look_for_me where id = 1;
select max(object_id) from t look_for_me where id = 99;
rajesh@ORA11G> l
1 select sql_id,sql_text
2 from v$sql
3* where lower(sql_text) like 'select max(object_id) from t look_for_me%'
rajesh@ORA11G> /
SQL_ID SQL_TEXT
------------- ------------------------------
4f57cfg7vm386 select max(object_id) from t l
ook_for_me where id = :"SYS_B_
0"
1 row selected.
rajesh@ORA11G>