This discussion is archived
13 Replies Latest reply: Aug 25, 2013 7:52 PM by GTS (DBA) RSS

cursor_sharing=similar

GTS (DBA) Journeyer
Currently Being Moderated

Hello Experts ;

 

I don't understand what i marked as bold following term.

SIMILAR causes statements that may differ in some literals, but are otherwise identical, to share a cursor,

unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

 

Can i get any simple example for  ( degree to which the plan is optimized )  ?


Thanks !

  • 1. Re: cursor_sharing=similar
    SomeoneElse Guru
    Currently Being Moderated
  • 2. Re: cursor_sharing=similar
    GTS (DBA) Journeyer
    Currently Being Moderated

    @someoneElse

     

    Thanks for given link.

    i am referring foloowing link , it's just similar as 'ask tom'  but  tom explained many things!

    THE RAJESH BLOG: CURSOR_SHARING = SIMILAR / FORCE / EXACT

    Thanks !  I am going to read , i'll come back  tomorrow.

  • 3. Re: cursor_sharing=similar
    SomeoneElse Guru
    Currently Being Moderated

    > i am referring foloowing link , it's just similar as 'ask tom'  but  tom explained many things!


    "Similar" to Tom's article?


    It looks like Rajesh copy/pasted Tom's article word-for-word!  And didn't give Tom credit!

     

    Tom's article is from 2006, Rajesh is from 2010.  Man oh man, utterly shameless.

  • 4. Re: cursor_sharing=similar
    GTS (DBA) Journeyer
    Currently Being Moderated

    Hello SomeoneElse

     

    well , i am learning ..  but i need to clear some confusions,

    Sachin Arora's Blog: CURSOR_SHARING - Do we use it?

     

    From this link , i have a doubt , please have a look  on terminal 1  and terminal 2

    when queries  were similar , but  both terminal output  acts different .

    i don't  understand  the  logic. 


    3. CURSOR_SHARING = SIMILAR (Introduced in 9i)


    >> Terminal 1 >>


    SQL> alter session set cursor_sharing=similar;

    Session altered.

     

    SQL> select * from test1 where t1=2;

    1 row selected.

     

    SQL> select * from test1 where t1=1;

    99 rows selected.

     

    SQL> select sql_text  from v$sql  where sql_text like 'select * from test1%' order by sql_text;

    SQL_TEXT

    select * from test1 where t1=:"SYS_B_0"

    select * from test1 where t1=:"SYS_B_0"


    >> Terminal 2 >>


    SQL> alter system flush shared_pool;

    System altered.


    SQL> select * from test1 where t1=2 and t1=22;

    no rows selected

     

    SQL> select * from test1 where t1=2 and t1=23;

    no rows selected

     

    SQL> select sql_text  from v$sql  where sql_text like 'select * from test1%' order by sql_text;

    SQL_TEXT

    select * from test1 where t1=:"SYS_B_0" and t1=:"SYS_B_1"


    Thanks in advance ..

  • 5. Re: cursor_sharing=similar
    SomeoneElse Guru
    Currently Being Moderated

    It helps when you ask specific questions.  I'm not sure what you want.

  • 6. Re: cursor_sharing=similar
    GTS (DBA) Journeyer
    Currently Being Moderated

    @ SomeoneElse


    I am asking  doubt what  author is saying here ..  Sachin Arora's Blog: CURSOR_SHARING - Do we use it?

    No need to get confuse. please have a look on  red marked and  blue  marked.

    Questions are mentioned  bottom of this reply.


    >> terminal 1 >>

     

    SQL> create table test1 (t1 number,t2 number);

    Table created.


    SQL>
    1 begin
    2 for i in 1 .. 100 loop
    3 insert into test1 values(1,i);
    4 end loop;
    5 commit;
    6 update test1 set t1=2 where rownum <> 2

       /

    PL/SQL procedure successfully completed.

     

    SQL> create index tt_indx on test1(t1);

    Index created.

     


    SQL> alter session set cursor_sharing=similar;

    Session altered.

     

    SQL> select * from test1 where t1=2;

    1 row selected.

     

    SQL> select * from test1 where t1=1;

    99 rows selected.

     

    SQL> select sql_text   from v$sql    where sql_text like 'select * from test1%'  order by sql_text;

    SQL_TEXT
    select * from test1 where t1=:"SYS_B_0"
    select * from test1 where t1=:"SYS_B_0

    "

     

    This tells us that even though the 2 statements were similar, Oracle opted for a different plan.

     

    But this doesn’t mean that SIMILAR and EXACT are same.   See this:

     

    >> terminal 2 >>


    SQL> alter system flush shared_pool;

    System altered.

     

    SQL> select * from test1 where t1=2 and t1=22;

    no rows selected

     

    SQL> select * from test1 where t1=2 and t1=23;

    no rows selected

     

    SQL> select sql_text  from v$sql   where sql_text like 'select * from test1%'   order by sql_text;

    SQL_TEXT

    select * from test1 where t1=:"SYS_B_0" and t1=:"SYS_B_1"

     

    My questions are :

     

    both  terminal  sql statements having  different inputs ( please see where  condition).

    on terminal  1 , oracle has created 2  plans to execute the query ?  -  I can understand.

    on terminal  2 , oracle  having only one plan  to execute the query - why so ??  i cannot understand  - 

     

    Thanks in advance ..

  • 7. Re: cursor_sharing=similar
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    I briefly glanced at the blog post you're referencing, and it doesn't appear to me to be particulary clear. So I wouldn't fixate on it, just move on to another article on the subject (e.g. by Tom Kyte Ask Tom &amp;quot;CURSOR_SHARING=FORCE&amp;quot;), or study documentation thoroughly.

     

    In a nutshell, cursor_sharing = similar is a compromise between the two extremes: cursor_sharing = exact (which doesn't change anything) and cursor_sharing = force (which forces replacement of literals with system-generated bind variables wherever possible). So with this setting, the optimizer is supposed only to replace a literal with a bind when it's safe to do so from the performance point of view.

     

    Imagine, for example, that you have a table whose contents describe the population of our planet. Two similar queries are very different from the optimization point of view:

     

    SELECT * FROM ALL_PEOPLE WHERE COUNTRY_OF_ORIGIN = 'China';

    SELECT * FROM ALL_PEOPLE WHERE COUNTRY_OF_ORIGIN = 'Vatican';

     

    The first one returns 1.3 billion entries out of 7 billion, and it would be a disaster to read it via an index. In the second case, the query only returns 800 rows out of 7 billion, and an index on COUNTRY_OF_ORIGIN would be extremely efficient here.

     

    Getting back to cursor_sharing -- ideally, Oracle should be enough to see that with this setting, it shouldn't touch predicates like COUNTRY_OF_ORIGIN = CONST with system-generated binds, because depending on the value of CONST, different plans are required for a suitable performance. In reality, however, all Oracle can do here is check whether there exists a histogram on a column or not and other simple things like this. So there is a good chance that the optimizer would make a mistake and not use system-generated binds where appropriate, or the other way round, use them where not appropriate.

     

    Also, there are numerous bugs associated with CURSOR_SIMILAR = SIMILAR (just search the MOS bug database using these keywords and see for yourself). In addition to that, in 11g this setting has become deprecated.

     

    So, what should one do? First of all, one should realize that using CURSOR_SIMILAR at all is sort of like using clutches (to use Tom Kyte's terminology) -- it should never be developer's first choice. The ideal solution is to use normal (not system-generated) binds in the code where appropriate, and literals elsewhere. It's the developer who should take the responsibility and decide which query is to benefit from bind variables and which not, not some primitive software algorithm built into Oracle optimizer.

     

     

    Hope this helps.

     

    Best regards,

    Nikolay

  • 8. Re: cursor_sharing=similar
    GTS (DBA) Journeyer
    Currently Being Moderated


    Are  you  saying  CURSOR_SHARING=EXACT

    Is  this  sufficient  ?   i mean  due to many bugs ( can we ignore  similar and force) ?

  • 9. Re: cursor_sharing=similar
    Justin Cave Oracle ACE
    Currently Being Moderated

    Setting cursor_sharing to EXACT is sufficient if the application is written correctly.

     

    If the application is written correctly, it will use bind variables wherever bind variables are appropriate and literals wherever literals are appropriate.  The conscientious developer will ensure that their statements are written correctly.

     

    cursor_sharing of FORCE or SIMILAR is something that a DBA can occasionally use to work around poorly written applications.  Ideally, either should be a temporary measure until the developers fix the problem code.  And either setting is likely to introduce some problems-- the hope is that they solve more problems than they introduce.

     

    Justin

  • 10. Re: cursor_sharing=similar
    Lalit Kumar B Explorer
    Currently Being Moderated

    Ideally CURSOR_SHARING as default to EXACT works the way it is intended to.The cursor sharing parameter is for auto binding of the literals, and it does not optimize/deoptimize the plan in anyway. You shouldn't be altering the parameter unless you have an issue with the Shared pool being flooded up with too many different execution plans for different literals. And per your subject of the post, CURSOR_SHARING=SIMILAR will help only when the distinct literals are less. If your application  takes a lot of unique values as inputs which is passed as literals to the predicate, then due to the skewness of the data you would again have different plans allover. So, it ONLY depends on the values you take as input from the frotnend application.

  • 11. Re: cursor_sharing=similar
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

     


    Are  you  saying  CURSOR_SHARING=EXACT

    Is  this  sufficient  ?   i mean  due to many bugs ( can we ignore  similar and force) ?

     

    yes, if the application was properly written (i.e. using bind variables when appropriate).

     

    Best regards,

    Nikolay

  • 12. Re: cursor_sharing=similar
    Lalit Kumar B Explorer
    Currently Being Moderated

    NikolaySavvinov wrote:

     

    Hi,

     

    8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

     


    Are  you  saying  CURSOR_SHARING=EXACT

    Is  this  sufficient  ?   i mean  due to many bugs ( can we ignore  similar and force) ?

     

    yes, if the application was properly written (i.e. using bind variables when appropriate).

     

    Best regards,

    Nikolay

     

    +1 (completely agree with Nikolay)

  • 13. Re: cursor_sharing=similar
    GTS (DBA) Journeyer
    Currently Being Moderated

    @ SomeoneElse

     

    Completely i understood. Ask  tom link  is very clear.

     

    Thanks  someoneElse !  and Thanks all !


    "  Along with the PLAN,  Oracle optimizer stored the literal value also."

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points