The documentation has under-sold this feature. Even for columns that do not look very
skewed at all, histograms helps to turn ugly explain-plans to very fast ones.
Somewhere inside the optimizer, histograms help it decide on sensible join-orders
amongst other things. You didn't say, but I suspect your dramatic improvement is in
the different join order.
If you can, compute for table for ALL columns for all indexes (3 alls and leave out
the indexed keyword). This will do all columns, but may not be feasible because it
could blow out sys.histgrm$ depending on how many tables/columns/partitions you have.
Do not specify the buckets -- let the analyze choose. It will vary the number
depending on what it finds.
If you cannot do all columns, then do indexed plus where-clause columns. For the where
clause columns, look at the SQL in the v$sqlarea + v$sqltext with high disk_reads and
buffer_gets. For those ones, parse the SQL from v$sqltext after the from-clause to get
tokens (words), and lookup dba_tab_columns for valid column_names. From this list and
dba_ind_columns, you can construct the columns-list to feed the analyze statement. For
a DW, this is essential to cut down QFHs. I have a procedure to do this if you are
interested. This may still blow out your sys.histgrm$ - so you will have to apply a
little local knowledge to cut down on the list.
At the very least, I would do indexed columns for SQL with high
disk_reads/buffer_gets/executions. This is minimal cost for potentially very high
gains as you found out.
Have fun.
>>> [EMAIL PROTECTED] 04/06/01 12:20PM >>>
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--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Binley Lim
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).
