Re: cursor sharing
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 20 Jun 2006 09:42:35 -0500
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@xxxxxxxxxxxxxxxxxxxxx,
06/19/2006 05:18 oracle-l <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
