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: [EMAIL PROTECTED] 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: [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).
