Re: ** histograms
- From: "Ghassan Salem" <salem.ghassan@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Mon, 20 Nov 2006 18:12:17 +0100
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@xxxxxxxxxxxxx> 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
______________________________________________________________________
--
//www.freelists.org/webpage/oracle-l
Other related posts:
