Re: missing link in my 10053 trace
From: Greg Rahn <greg_at_structureddata.org>
Date: Wed, 8 Jun 2011 14:17:55 -0700
Message-ID: <BANLkTim0zfEeu-+Nkgdn9RuvuWzt3WpZ5g_at_mail.gmail.com>
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")
Date: Wed, 8 Jun 2011 14:17:55 -0700
Message-ID: <BANLkTim0zfEeu-+Nkgdn9RuvuWzt3WpZ5g_at_mail.gmail.com>
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 Wed Jun 08 2011 - 16:17:55 CDT