Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Neil Kodner <nkodner@xxxxxxxxx>
  • To: info@xxxxxxxxxxxxxxxxxxxxx
  • Date: Tue, 24 Nov 2009 12:03:16 -0500

Here is the histogram on letter_bin.prty_id

http://spreadsheets.google.com/ccc?key=0ArUTHdqN0J--dDVXZ1ZPTGlROUxfU0FXV3RNdFVINlE&hl=en

I've never manually crafted a histogram before.

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: