Sorry, I did not use anything that sophisticated (nor accurate?): Uniform distribution = 1/(number of unique values) Max distribution = (the highest occurrence of a value) / (number of records in the table)
Tom -----Original Message----- Sent: Tuesday, May 28, 2002 4:34 PM To: Multiple recipients of list ORACLE-L tom, This is interesting. How did you determine max distribution and uniform distribution? Did you use standard deviation and variance? regards, jack silvey --- "Terrian, Tom" <[EMAIL PROTECTED]> wrote: > John, > > I know in a previous job, we determined that > histograms where not worth it. The > following is from a test that we performed: > > *********************************************************************** > > Table- F_tab Uniform Distribution Max Distribution > Field- P_code 0.65% 18% > > Therefore, from the above numbers, the field should > be a good candidate for > histograms so I did the following tests. Based on > the following combinations of > statistics and histograms, I timed how fast a sample > query ran: > > w/o stats w/ stats w/stats w/stats > P_Code no histograms 100 buckets 50 buckets > ---------- ---------- ---------- ---------- > ---------- > 0101 342 secs. 428 385 500 > 0101 406 416 326 340 > 0101 391 390 327 359 > 6501 458 490 337 342 > 6501 475 380 358 490 > 6501 518 395 326 354 > ---------- --------- ---------- ---------- > ---------- > Total Secs. 1730 1629 1348 2085 > (w/o high > and low > values) > Avg time 7Min 12Sec 6Min 47Sec 5Min 37Sec 5Min > 51Sec > per run > > However, to create the histogram it takes 1hr42min. > Too long for the > benefit that we gain. > > *********************************************************************** > > Tom > > -----Original Message----- > Sent: Tuesday, May 28, 2002 3:25 PM > To: Multiple recipients of list ORACLE-L > > Ian, > > > John are you saying to create histograms on all > indexed > > columns, or just the ones with distributions which > are skewed > > and also for ones which although symmetric in > distribution > > have some values much more prevalent than others? > > > To keep this simplistic, I wouldn't use Histograms > (or let it default to 2) > *unless* hardcoded values are known to be used, at > least in 8i. The > situation becomes different in 9i as the CBO is able > to peek into these > values even when bind variables are used. (I think > there is a script out > there on Steve Adam's site called 'Histogram Helper' > which can suggest this > for you). > > However, as Larry mentioned in a previous email, the > CBO is influenced by > distributions in non-indexed colummns. The issue > here is that the number of > buckets really matter, and the default of 2 can > influence incorrect > decisions (haven't we all seen 'em? :) So what I am > essentially saying is > this: Use COMPUTE and Histograms when you have to, > but don't sweat over it > unless it pinches ya. > > And how do we determine it is pinching? V$SYSSTAT is > a pretty good > indicator: (At the risk of being called a part of > the 'ratios' group) Is the > ratio of 'table scan blocks gotten' to 'table scan > rows gotten' acceptable? > Is the number of table scans acceptable? Is the > number of 'db block gets' > too much - as compared to 'physical reads'? > > I am in the process of determining the overheads of > having 'too many' > histograms - I am observing some 'row cache lock' > latch waits and think that > this could have been the result of too many > histograms. Hope to post some > info back to the list soon. > > John Kanagaraj > Oracle Applications DBA > DBSoft Inc > (W): 408-970-7002 > > The manuals for Oracle are here: > http://tahiti.oracle.com > The manual for Life is here: > http://www.gospelcom.net > > ** The opinions and statements above are entirely my > own and not those of my > employer or clients ** > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: John Kanagaraj > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Terrian, Tom > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Terrian, Tom INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
