Oracle-L: Re: col_usage$ question

From: Binley Lim <Binley.Lim_at_xtra.co.nz>
Date: Wed, 19 Nov 2003 00:20:00 -0800
Message-ID: <F001.005D716D.20031119002000@fatcity.com>

Bingo!

I did pretty much the same thing for an 8i DW with plenty of ad-hoc queries. I had no way of knowing what those ad-hoc queries might look like, and they will change over time. So I set up a polling process to do a textual parse of v$sql for those SQLs with highest disk_reads and/or buffer_gets, and feed those column names directly to the analyze process to collect histograms.

I started off with a restriction that only skewed columns will get histograms, and thought I had a pretty good method of defining "skew". Turned out that even the columns that do not appear skewed do actually affect CBO decisions. Putting histograms on those apparently "un-skewed" columns produced at least similar, and in some cases, dramatically better results. So, anything in the predicate of a "heavy" SQL, I histogrammed it.

Original Message -----

>. It is currently

> (Oracle 9) used to decide if i might be worth gathering histogram
> information for a column - together with the determination if the data in
> the column is sufficiently skewed. No point in gathering histograms on
> non-skewed data or on data that is never referenced in a predicate.
> I can imagine that Oracle 10 will use that data to recommend indexes - as
> Daniel suggested.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Binley Lim
  INET: Binley.Lim_at_xtra.co.nz

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Wed Nov 19 2003 - 02:20:00 CST