RE: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 24 Nov 2009 17:53:59 +0100

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: