| 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
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_at_daas To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
.dla.mil> cc:
Sent by: Subject: RE: Statistical sampling and representative stats
root_at_fatcity.c 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
Avg time 7Min 12Sec 6Min 47Sec 5Min 37Sec 5Min
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
-- 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-LReceived on Tue May 28 2002 - 15:24:30 CDT
(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: Cherie_Machler_at_gelco.com 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).
 |
 |