Insights into Statistics, Query Optimization and the Oracle Optimizer
-
Cursor Sharing
March 12, 2010
How does Adaptive Cursor sharing behavior differ from Cursor_Sharing = Similar or Force.
| 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 | The 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 | The 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 | The 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) | The 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 first to 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:
Now that we know when literal replacement will take place, and what the query looks like to the optimizer, we can consider adaptive cursor sharing.
If literal replacement takes place, and the query that the optimizer optimizes contains a bind, then adaptive cursor sharing can take place. To adaptive cursor sharing, a bind variable is a bind variable, whether it comes from the user query or is inserted by literal replacement. On the other hand, if the query contains only literals (no binds), adaptive cursor sharing will not take place. In our example above, adaptive cursor sharing can be considered for cases 2 and 3.2. For case 3.2, the optimizer will likely choose the same plan for different values of the literal. In case 2, if there is a histogram, then the optimizer may choose different plans depending on how popular the literal value is.
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. 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.
<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>