| 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
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). Received on Tue May 28 2002 - 14:25:22 CDT
 |
 |