RE: missing link in my 10053 trace
Date: Thu, 9 Jun 2011 09:58:47 -0400
Message-ID: <9CE162BC5ED2C643956B526A7EDE46FF02445527DC9F_at_EXM-OMF-04.Ceg.Corp.Net>
I believe since there is a "to_number" applied to the INST_PROD_TYPE column it cannot use the index on that column which leads to a Skip Scan on the PS0RF_INST_PROD index using the SETID column.
In general I have found the version 11.2.0.2 optimizer to be very "skip scan happy". If the stats are not just right and there aren't histograms on just the right columns then you are very likely to end up with an index skip scan. Especially when you are working with packaged application that comes with poorly thought out indexes such as the one you are dealing with here. Why would the leading column of the index that only has a NDV of 2 be in the index at all?
In 11.2.0.2 you have the option of generating extended stats on to_number(INST_PROD_TYPE) which would tell the optimizer the real cardinality which might change the execution plan. The real fix, of course, is to do what you have already done, which is to fix the poorly written code and put quotes around the value as it should be.
Thanks,
Finn
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Greg Rahn
Sent: Wednesday, June 08, 2011 5:18 PM
To: martin.a.berger_at_gmail.com
Cc: Oracle-L Freelists
Subject: Re: missing link in my 10053 trace
Looks the selectivity is being calculated as 0 for OOR predicate
because of the histogram and rounded up to 1 row - so I don't think
it's 1%.
"Using prorated density: 0.000000 of col #3 as selectvity of
out-of-range/non-existent value pred.
(also looks like we have a spelling mistake for "selectvity")
Here is an example:
http://pastebin.com/Esif77MU
On Wed, Jun 8, 2011 at 1:17 PM, Martin Berger <martin.a.berger_at_gmail.com> wrote:
> maybe someone can help me with my interpretation of a 10053 trace file.
> DB: 11.2.0.2.0 - 64bit
> I have a small query with a little error, which causes big troubles.
> The relevant part of the query is
> WHERE ....
> AND inst_prod_type=003
> AND setid='COM01'
>
> but INST_PROD_TYPE is VARCHAR2.
>
> this leads to
> filter[ (TO_NUMBER("INST_PROD_TYPE")=3 AND "SETID"='COM01') ]
>
> based on this TO_NUMBER ( I guess!) the optimiser takes a fix
> selectivity of 1%.
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 09 2011 - 08:58:47 CDT
>>> This e-mail and any attachments are confidential, may contain legal, professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP1
-- http://www.freelists.org/webpage/oracle-l