I have had some really good experiences with using histograms.
They didn't always produce the improvements that I expected
but in many cases, I saw 10 times, 100 times, or even 1,000 faster
execution times after adding histograms.
I don't have the specifics, but these were cases where the data
was very heavily skewed and the column that the histogram
was created on was included in the WHERE clause of the SELECT
statement and set to some specified value with an equal sign
(bind variables were not used).
In my experience, histograms seem a bit hit or miss but in the
cases where they've worked, the performance improvement
has been good or even fantastic. In the cases where they haven't
helped, I've simply removed them.
Based on the scarcity of previous responses to emails on this list,
it seems that histograms are not that widely used throughout the
industry. I'm not sure why.
Cherie Machler
Oracle DBA
Gelco Information Network
"Terrian, Tom"
<tterrian@daas To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
.dla.mil> cc:
Sent by: Subject: RE: Statistical sampling and
representative stats
[EMAIL PROTECTED] collection
om
05/28/02 02:55
PM
Please respond
to ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
