Re: RE: query slow in 9i, but not slow in 8i

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 02 Mar 2004 05:43:26 -0700
Message-Id: <6.0.0.22.2.20040302053957.03031c20@pop.centrexcc.com>

Note in-line
At 01:37 AM 3/2/2004, you wrote:

>I have to disagree with part of Wolfgang's comment.

I don't mind being corrected considering that a) it is by Jonathan Lewis
b) the correction actually provides more ammunition for my original point that

"an overdose [of histograms] can kill [performance]."

>To use a histogram, Oracle has to load it into memory,
>then compare predicate values with end-points before
>producing a selectivity value.
>
>If you have histograms on every single column in the
>database, that's a lot of memory to load - and it seems
>to be protected by only one latch. The incremental
>CPU cost of using the histogram for any one optimisation
>call is probably not significant - but the infrastructure
>cost is.
>
>If you have a perfect system, that uses a few distinct
>thousand SQL statements, and optimises them just
>once, then the overhead is irrelevant. If you have a
>typical system, then it's another nail in the coffin.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Mar 02 2004 - 06:37:59 CST