RE: Histogram Helper

Hi All,
 I had fair amount of success using Histograms for all indexed columns. I have been using it since 7.3.4.x version. However, histograms do not offer any help if the SQL uses bind variables. Most of our application code does use bind variables and hence does not benefit from this really neat feature. May be that is true (use of bind varables) with others as well.

Regards,

• Kirti Deshpande Verizon Information Services http://www.superpages.com

> -----Original Message-----
> From: Cherie_Machler_at_gelco.com [SMTP:Cherie_Machler_at_gelco.com]
> Sent: Friday, April 06, 2001 7:46 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Histogram Helper
>
>
> Steve,
>
> I too experienced dramatic improvement when using
> a couple of histograms on very heavily skewed columns.
>
> The main thing with histograms is that you need to keep
> them updated. They do more harm than good if they
> get stale.
>
> I experimented with creating histograms on all indexed
> columns. Didn't hurt anything but didn't help anything unless
> the data was skewed. I read somewhere (can't remember
> where) that if you can afford the processing time and the
> space to maintain the histograms, theoretically it shouldn't
> hurt to create them where they are not needed. I guess that's
> if you can't afford the time to figure out where the data is
> skewed or if you don't know how.
>
> However, that seemed like a waste to me so we basically
> just use them sparingly where they can really help.
>
> I've posted several times in the past about histograms
> and recieved very few replies. That leads me to believe
> that not many people are using or are interested in histograms.
> I don't see them heavily emphasized at conferences or
> in books.
>
> Cherie Machler
> Gelco Information Network
>
>
>
>
> "Steve Orr"
>
> <sorr_at_arzoo.c To: Multiple recipients of
> list ORACLE-L <ORACLE-L_at_fatcity.com>
> om> cc:
>
> Sent by: Subject: Histogram Helper
>
> root_at_fatcity.
>
> com
>
>
>
>
>
> 04/05/01
>
> 07:20 PM
>
> Please
>
> respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
> 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: sorr_at_arzoo.com
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.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).