Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** histograms

Re: ** histograms

From: Binh Pham <binhpham15_at_hotmail.com>
Date: Mon, 20 Nov 2006 18:34:42 +0000
Message-ID: <BAY103-F35274C9BEA2862CCAA946DD2ED0@phx.gbl>

Wolfgang,

What is the example of histogram without index effects performance?

Thanks.


From:  Wolfgang Breitling <breitliw@centrexcc.com>
Reply-To:  breitliw@centrexcc.com
To:  ajoshi977@yahoo.com
CC:  oracle-l@freelists.org
Subject:  Re: ** histograms
Date:  Mon, 20 Nov 2006 07:51:22 -0700
>At 10:49 AM 11/19/2006, A Joshi wrote:
>>Hi,
>>   About use of histograms : I think histograms are useful for
>>indexes on columns that are not very selective.
>
>Histograms have nothing to do with indexes. A histogram can be
>useful to the CBO (the RBO does not use histogram information) if
>(a) the column is used in a predicate
>(b) the distribution of column values differs significantly from
>uniform which the CBO assumes in the absence of a histogram
>
>to "prove" the point that histograms have nothing to do with
>indexing I have an example where a histogram on a non-indexed column
>reduces cpu time and elapsed time of a query by by 50% and 40%
>respectively, i.e. the sql runs almost twice as fast with the
>histogram and consumes half as much cpu compared to without the
>histogram.
>
>>However I came across note 1031826.6 on metalink. About maintenance
>>and space cost. I think space cost is negligible and can be
>>ignored. About maintenance : does it mean statistics need to be
>>gather often? Or does it mean some other cost.
>>
>>Question : Is there any other overhead or any other negative impact
>>of using histograms?
>
>Yes
>
>>
>>Is it advisable to use histograms just for some tables and some
>>specific columns or is it OK to just set database wide?
>
>No.
>
>At least in my opinion. I have seen an unnecessary histogram - on a
>column with perfectly uniform data distribution - destroy ( and I
>mean destroy, by a factor of ~ 9000 ) the performance of a sql
>statement. See also the recent post by Fuad Arshad and I quote: "it
>is generating histograms which doesnt work well with the
>application".
>
>
>Regards
>
>Wolfgang Breitling
>Centrex Consulting Corporation
>www.centrexcc.com
>
>
>
>______________________________________________________________________
>This email has been scanned by the MessageLabs Email Security
>System.
>For more information please visit http://www.messagelabs.com/email
>______________________________________________________________________
>--
>http://www.freelists.org/webpage/oracle-l
>
>


Get the latest Windows Live Messenger 8.1 Beta version. Join now. -- http://www.freelists.org/webpage/oracle-l Received on Mon Nov 20 2006 - 12:34:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US