Sorry, I did not use anything that sophisticated (nor accurate?):
Uniform distribution = 1/(number of unique values)
Max distribution = (the highest occurrence of a value) / (number of records in
the table)
Tom
-----Original Message-----
Sent: Tuesday, May 28, 2002 4:34 PM
To: Multiple recipients of list ORACLE-L
tom,
This is interesting. How did you determine max
distribution and uniform distribution? Did you use
standard deviation and variance?
regards,
jack silvey
- "Terrian, Tom" <tterrian_at_daas.dla.mil> wrote:
> 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: john.kanagaraj_at_hds.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).
> --
> 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).
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: jack_silvey_at_yahoo.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).
--
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 Wed May 29 2002 - 07:28:20 CDT
