Re: cursor sharing

Nigel,

Thanks for your response. In my situation the SQLs are using the bind variables so I think that your scenario does not apply. In my tests I have removed the date values from the SQL and replaced them with &1 and &2. When I enter
the june dates as input parameters, the query runs for over an hour, when I enter may dates it completes in 4-5
seconds. Moreover when I use the CURSOR_SHARING_EXACT for June dates, the query finishes in seconds
(just like May).

thank you

Gene Gurevich
Oracle Engineering
224-405-4079

                                                                           
             Nigel Thomas                                                  
             <nigel_cl_thomas@                                             
             yahoo.com>                                                 To 
                                       genegurevich_at_discoverfinancial.com, 
             06/19/2006 05:18          oracle-l <oracle-l_at_freelists.org>   
             PM                                                         cc 
                                                                           
                                                                   Subject 
             Please respond to         Re: cursor sharing                  
               Nigel Thomas                                                
             <nigel_cl_thomas@                                             
                yahoo.com>                                                 
                                                                           
                                                                           
                                                                           

>>Can someone recommend a good article on the cursor sharing in Oracle 9. I
>>just found a query that runs in 5 seconds
>>with CURSOR_SHARING_EXACT and in more than 1 hour without it
>>(cursor_sharing is set to force on the DB level)
>>I'm trying to find out why it happens.

Gene

One possibility is that you have multiple variants of the 'same' query, using different literals, eg:

select x from y where z = 'A';
select x from y where z='B';

With cursor_sharing=exact the queries are separately parsed - so can have different execution plans. With cursor_sharing=force, Oracle recognises the similarity and may uses a single execution plan (based on the first example to be parsed - although be aware that other factors like bind variable peeking may also come into play).

Depending on the skew of the data, what's good for the first query isn't for the second. Suppose that 50% of the rows have z='B', but only 1% have z='A'; an index on y.z will benefit the first query, but not the second (for which a full table scan would likely be faster). The more complex the query, the more potential ways there are for the optimizer to miscalculate the cost (and the more the effect can be magnified) - especially if stats are inaccurate or not collected.

Regards Nigel
www.preferisco.com

--
http://www.freelists.org/webpage/oracle-l