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