Oracle-L: Re: HISTOGRAMS , CBO and OLTP

From: Binley Lim <Binley.Lim_at_ird.govt.nz>
Date: Sun, 09 Feb 2003 15:53:36 -0800
Message-ID: <F001.005480D2.20030209155336@fatcity.com>

For DSS systems (especially where there a lots of ad-hoc queries), be liberal with histograms. They can have a significant positive impact on the join-order chosen by the CBO even when the columns don't look skewed. There is additional cost in CPU, cache and system tablespace(especially if you do all columns !!!), but pales into insignificance when you have the potential for turning the run-time of queries that takes hours into minutes. It's simple enough to poll v$sql for column names after the where-clause and only do histograms for those columns.

For OLTP, you can test to see if there's any difference to the explain-plans with histograms, and if there are none, take them off.

>>> mjgnmenon_at_yahoo.com 02/08/03 05:08p.m. >>>

So far only had to use RBO, now we are moving towards using CBO. However none of the documents really talk in detail about Histograms, the goods, bads where and where not to use.

I have read that HISTOGRAMS are good for columns that are non unique and that have many duplicate values. While DSS systems have more occurances of these kind of columns, OLTP databases could also have certain columns like status codes or state codes that have duplicate values.

Is it OK to have histograms generated for all columns irrespective of the type of system the database supports? and pros and cons?

Could some one point me to a good white paper or material that would discuss this?

TIA Menon

Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Binley Lim
  INET: Binley.Lim_at_ird.govt.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 Sun Feb 09 2003 - 17:53:36 CST