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 |
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>