Re: Better cardinality estimate when dialing optimizer_features_enable back
- From: Neil Kodner <nkodner@xxxxxxxxx>
- To: info@xxxxxxxxxxxxxxxxxxxxx
- Date: Tue, 24 Nov 2009 13:21:36 -0500
Another data point is if I specify a cardinality hint, I get a desired
execution plan (Hash Join instead of NL), and a great response time.
On Tue, Nov 24, 2009 at 12:26 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
> Here are some queries in question, along with their explain plans using
> ALLSTATS LAST
>
> http://www.neilkodner.com/allstats_last.txt
>
> and an accompanying 10046 trace
>
> http://www.neilkodner.com/allstats_last_10046.txt
>
>
>
>
> On Tue, Nov 24, 2009 at 11:53 AM, Randolf Geist <
> info@xxxxxxxxxxxxxxxxxxxxx> wrote:
>
>> Neil,
>>
>> If it's a single table access predicate issue - reproducible via a simple
>> query against LETTER_BIN - e.g. SELECT COUNT(*) FROM LETTER_BIN WHERE
>> PARTY_ID = <some_literal_value>, then you should be able to easily pinpoint
>> the issue. Since you say that the values are not evenly distributed, I
>> assume that a histogram exists on that column.
>>
>> So if you have many more than 254 distinct values the resulting
>> height-balanced histogram might not represent the data in a suitable way for
>> your needs - you might need to manually craft one that better suits your
>> needs - in particular which values are treated as popular or non-popular in
>> the histogram.
>>
>> 10.2.0.4 (and 11g) introduces a different handling of the density used for
>> estimating the cardinality of non-popular and non-existing values in case of
>> height-balanced histograms, see Alberto Dell'Era's excellent post series:
>>
>>
>> http://www.adellera.it/blog/2009/10/16/cbo-newdensity-replaces-density-in-11g-10204-densities-part-iii/
>>
>> So by going to a optimizer setting prior to 10.2.0.4 the old density
>> calculation is going to be used which might result in a higher estimate than
>> with the new density calculation.
>>
>> So for histograms - check the generated histogram
>> (DBA/ALL/USER_TAB_HISTOGRAMS for non-partitioned tables). In general check
>> the recorded low/high column values, since they will influence the
>> cardinality estimate if you're outside the recorded low/high column values.
>>
>> By the way - this is not directly related to the cardinality issue that
>> you've raised - your WORKLOAD System Statistics are not going to be used by
>> the optimizer, since your SREADTIM (4ms) is greater than your MREADTIM
>> (1.5ms) which makes them invalid:
>>
>> PNAME PVAL1
>> ------------------------------ ----------
>> CPUSPEEDNW 774.281
>> IOSEEKTIM 8.049
>> IOTFRSPEED 15136.032
>> SREADTIM 4.004
>> MREADTIM 1.547
>> CPUSPEED 777
>> MBRC 5
>> MAXTHR 5261312
>>
>> This means that the CBO will fall back to a mixture of NOWORKLOAD and
>> WORKLOAD System Statistics - the SREADTIM and MREADTIM will be synthesized
>> from IOSEEKTIM and IOTFRSPEED, but the MBRC from the WORKLOAD System
>> Statistics will be used to determine the MREADTIM - assuming a default
>> blocksize of 8KB:
>>
>> SREADTIM = IOSEEKTIM + 8192 / IOTFRSPEED = 8.049 + 8192 / 15136.032 =
>> 8.59ms
>>
>> MREADTIM = IOSEEKTIM + 8192 * MBRC / IOTFRSPEED = 8.049 + 8192 * 5 /
>> 15136.032 = 10.75ms
>>
>> which is quite different from default NOWORKLOAD System Statistics
>> (SREADTIM = 12ms, MREADTIM 26ms with 8KB default blocksize). Your settings
>> tend to favor full table scans (FTS based on MREADTIM will be cheap in
>> comparison to single block accesses based on SREADTIM) albeit the invalid
>> WORKLOAD System Statistics (which would obviously favor FTS even more).
>>
>> Regards,
>> Randolf
>>
>> Oracle related stuff blog:
>> http://oracle-randolf.blogspot.com/
>>
>> Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
>> http://www.apress.com/book/view/1430226684
>>
>> http://www.amazon.de/Expert-Oracle-Practices-Database-Administration/dp/1430226684
>> ___________________________________________________________
>> Preisknaller: WEB.DE DSL Flatrate für nur 16,99 Euro/mtl.!
>> http://produkte.web.de/go/02/
>>
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
Other related posts:
