RE: missing link in my 10053 trace

  • From: "Jorgensen, Finn" <Finn.Jorgensen@xxxxxxxxxxxxxxxxx>
  • To: "'greg@xxxxxxxxxxxxxxxxxx'" <greg@xxxxxxxxxxxxxxxxxx>, "'martin.a.berger@xxxxxxxxx'" <martin.a.berger@xxxxxxxxx>
  • Date: Thu, 9 Jun 2011 09:58:47 -0400

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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Greg Rahn
Sent: Wednesday, June 08, 2011 5:18 PM
To: martin.a.berger@xxxxxxxxx
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 <martin.a.berger@xxxxxxxxx> 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


Other related posts: