| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Statistical sampling and representative stats collection
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
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
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: ListGuru_at_fatcity.com (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: tterrian_at_daas.dla.mil 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: ListGuru_at_fatcity.com (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).Received on Tue May 28 2002 - 14:55:21 CDT
 |
 |