I just experienced an incredibly DRAMATIC performance boost with a
well-placed histogram! Now I'm hungry for "Histogram Helper." I'd like to
analyze the database and identify other possible candidates for histograms.
(Then maybe review the code after that. I'm using CHOOSE optimization with
automatic statistics gathering on all tables and indexes.)
Here's a quote from the Oracle Tuning Guide:
"In general, you should create histograms on columns that are frequently
used in WHERE clauses of queries and have a highly skewed data distribution.
For many applications, it is appropriate to create histograms for all
indexed columns because indexed columns typically are the columns most often
used in WHERE clauses."
Has anyone ever created histograms for all indexed columns as suggested
above? You could query DBA_INDEXES (or DBA_IND_COLUMNS) and create dynamic
SQL as follows: analyze table <TBL_NAME> compute statistics for all indexed
columns size 10;
Better yet, you could set the "method_opt" parameter of the
DBMS_STATS.GATHER_SCHEMA_STATS procedure to "all indexed columns size 100."
But isn't that brute force? I'm thinking our histogram helper should be a
little more sophisticated by factoring in an analysis of how skewed the data
is using the num_distinct, density, num_buckets, and last_analyzed columns
from dba_tab_columns. Finally, histogram helper should be automated to
ensure our statistics are maintained.
Any ideas or personal experiences with histograms? Any white papers on
implementing and managing histograms?
TIA!
Steve Orr
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Orr
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).
