query slow in 9i, but not slow in 8i 2004-03-02 - By Wolfgang Breitling
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
