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