Oracle-L: RE: Statistical sampling and representative stats collection

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Wed, 29 May 2002 15:13:34 -0800
Message-ID: <F001.0046EF85.20020529151334@fatcity.com>

One problem I have with histograms is having to calculate the proper number of buckets. Thanks to Steve Adams I have a program to do just that. I keep such information in the "dbms_stats_table" so it can be used by dbms_stats when the table is reanalyzed.

The proper number of buckets may change over time, and it's also possible the frequency of distributions of the values for a column may change enough that histograms no longer help. It is however an onerous task to recalculate them. How often are people checking the frequency distribution and the bucket counts. I try to do this every three months.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu

-----Original Message-----
Sent: Wednesday, May 29, 2002 7:39 AM
To: Multiple recipients of list ORACLE-L

> I remember that Steve Orr of this list used Histograms and enjoyed huge
> increase in performance.

Hi John, that over a year ago, your memory is too good. :-)

Haven't worked with histograms lately but here are some before and after stats from that former histogram implementation:

- Rows scanned per second went from 300,000 to <500; 
- consistent reads per second went from 75,000 to <500;
- CPU usage went from 85-100% to 5%; 
- the physical I/O rate dropped significantly.

When histograms help the difference can be dramatic. This was from a single query that was executed via dbms_jobs every minute. After ID'ing the problem

query I went through a tuning exercise at length then found histograms to be

the magic bullet that fixed everything. So, rather than the shotgun approach

of calculating histograms on everything, I think the more surgical technique

of finding the bottlenecks (possibly using the wait interface) still applies.
Of course I'm "preaching to the choir." :-)

Steve Orr
Bozeman, MT

-----Original Message-----
Sent: Tuesday, May 28, 2002 4:54 PM
To: Multiple recipients of list ORACLE-L Importance: High

Cherie,

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

As I observed before, histograms help only when literal predicates are used (until 9i). Steve Adams has this to say about Histograms:

Quote ---- Histograms enable the optimizer to more accurately estimate the selectivity of literal predicates against columns with skewed data distributions. This can help the optimizer to choose a better access path and possibly join order for certain queries than might otherwise be the case. However, redundant histograms on columns with uniformly distributed data, and unduly large histograms on columns with skewed data distributions just increase the CPU cost of parsing and waste space in the shared pool. Therefore histograms should only be created where they are beneficial, and should not be any larger than necessary. Please note that histograms on columns that are not indexed can nevertheless be beneficial because they inform the optimizer of the cardinality of their table for the query and can thus influence the join order.
Unquote ----

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

I remember that Steve Orr of this list used Histograms and enjoyed huge increase in performance. The reason why the Industry hasn't used Histograms as much as it should have been used is due to a combination of lack of knowledge, FUD as well as just plain lethargy. On the other hand, overuse also has its downsides.

Btw, has anyone tracked V$ROWCACHE which provides a fair idea of the row cache (or DD) portion of the Shared pool - the figures against dc_histogram_data and dc_histogram_defs may provide some clues about what's going on within....

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: Orr, Steve INET: sorr_at_rightnow.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: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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 - 18:13:34 CDT