RE: missing link in my 10053 trace

From: Jorgensen, Finn <>
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 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 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.


-----Original Message-----
From: [] On Behalf Of Greg Rahn Sent: Wednesday, June 08, 2011 5:18 PM
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:

On Wed, Jun 8, 2011 at 1:17 PM, Martin Berger <> wrote:
> maybe someone can help me with my interpretation of a 10053 trace file.
> DB: - 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'
> 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%.

Greg Rahn

>>> 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
Received on Thu Jun 09 2011 - 08:58:47 CDT

Original text of this message