13 Replies Latest reply: Aug 25, 2013 9:52 PM by GTS (DBA) RSS

    cursor_sharing=similar

    GTS (DBA)

      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 !

        • 2. Re: cursor_sharing=similar
          GTS (DBA)

          @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

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

              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

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

                • 6. Re: cursor_sharing=similar
                  GTS (DBA)

                  @ 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

                    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)


                      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
                        JustinCave

                        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

                          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

                            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

                              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)

                                @ 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."