RE: Statistical sampling and representative stats collection

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Tue, 28 May 2002 14:53:31 -0800
Message-ID: <F001.0046D6F9.20020528145331@fatcity.com>

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: 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). Received on Tue May 28 2002 - 17:53:31 CDT