is this on metalink or in the docs anywhere? I never saw it? You would think that oracle would warn you about over-using histograms. I guess I have to be more careful now. If I use options=>'GATHER AUTO' does oracle make a decision to use histograms? If so, what causes that decision? > > From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx> > Date: 2004/03/02 Tue AM 03:37:12 EST > To: <oracle-l@xxxxxxxxxxxxx> > Subject: Re: RE: query slow in 9i, but not slow in 8i > > > Note in-line > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > March 2004 Hotsos Symposium - The Burden of Proof > Dynamic Sampling - an investigation > March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial > April 2004 Iceland > June 2004 UK - Optimising Oracle Seminar > > > ----- Original Message ----- > From: "Mark Richard" <mrichard@xxxxxxxxxxxxxxxxx> > > > > I am unfortunately speaking from heresay, however I heard once that having > > histograms all over the place can slow down parsing as they have to be > > inspected. I was left with the impression that unless the data is skewed > > enough to warrant a histogram then there actually is a negative cost > > associated with having too many histograms. > > > > I have to disagree with part of Wolfgang's comment. > > 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. > > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------