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: Ghassan Salem <salem.ghassan_at_gmail.com>
Date: Mon, 20 Nov 2006 18:12:17 +0100
Message-ID: <411d50f60611200912r5d3b03abs27e05705a5d22545@mail.gmail.com>


As for space consumption of histograms, it's usually low, until you have lots of partitions/subpartitions, and a good number of columns on which you compute histograms. At a client site, someone used to compute histograms on a table that had around 70 columns, with several thousands of partitions. The system tablespace was around 10G (with the histogram table taking much of it).

rgds

On 11/20/06, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
>
> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Nov 20 2006 - 11:12:17 CST

Original text of this message

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