Re: Histogram Helper

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.

>>> sorr_at_arzoo.com 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: Binley.Lim_at_ird.govt.nz

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