Re: missing link in my 10053 trace

The confusing thing on that query was: everything WAS right: statistics, histograms, etc, just this to_number cast ...

The Application is 'Peoplesoft' - so not all indices makes sense for every customer. But together with my favorite Engineer (the one who brought the problem to me) we managed to get the statement fixed. We are preparing a 9.2 to 11.2 migration (with HW, Clusterware and OS change also). So a lot of fun. And no single HINT needed so far! At the same time a big cleanup of unnecessary indices is ongoing. Maybe I come up with some other issues soon ;-)

Greg Rahn did a really great explanation what happened to my bad statement here: http://structureddata.org/2011/06/08/implicit-datatype-conversion-histograms-bad-execution-plan/ He did it in such clear words even I understood!

thank you all for your ideas, suggestions and questions,  Martin

Am 09.06.2011 um 15:58 schrieb Jorgensen, Finn:

> 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%.

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