RE: Histogram Helper

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Fri, 06 Apr 2001 03:45:58 -0700
Message-ID: <F001.002E3B83.20010406033027@fatcity.com>

Hi Steve,

Good idea, but unfortunately the table statistics and the basic column statistics (called single-bucket histograms) that are available if you only analyze the table are not sufficient to determine whether the distribution of values for any column is uniform or skewed. That means that we cannot do this with a data dictionary query; we will have to scan the table.

Based on your suggestion I have written "consider_histogram.sql" at http://www.ixora.com.au/scripts/query_opt.htm#consider_histogram that will scan a table and do the analysis for a single column only. The fact that it has to do a full scan each time you run it will probably make you use it selectively on columns that are subject to literal predicates in expensive queries. In my opinion, that's probably a good thing. Enjoy!

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
Sent: Friday, 6 April 2001 9:17
To: LazyDBA mailing list

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

Think you know someone who can answer the above question? Forward it to them! to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

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 Fri Apr 06 2001 - 05:45:58 CDT