Re: 10053 curiosity (changes with and without bind)

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 25 May 2004 06:48:31 -0600
Message-Id: <6.1.0.6.2.20040525063825.02b17ec8@pop.centrexcc.com>

That is correct, the query with the literal uses the histogram, recognizing that there is only 1 setid. The query with the bind variable uses the density value of the statistics and because you collected a histogram on it, density is no longer 1/NDV, and therefore correct, as it would be without the histogram.

Loose the "for all indexed columns" in your analyze. Do histogram ONLY for columns where you have identified that it is necessary/beneficial and ONLY for those.

I coined the phrase "Histograms are like drugs - an overdose can kill [performance]"

You just proved me right

At 06:15 AM 5/25/2004, you wrote:
>The setid field does have a histogram. (it probably shouldn't, but it comes
>from an 'all indexed columns' analyze). My confusion is that the setid field
>has only one distinct value and no nulls (~20,000 records in the table and
>~20,000 records with setid=DFCII). My thought was that since NDV=1 (with or
>without the histogram, with or without bind variables), the statistical
>information used would be pretty similar (I forgot that the optimizer
>doesn't look at the bind variable until 9)
>
>I also think (I'll check my notes and post in a bit) that removing the
>histogram improves the bind variable query performance. (change in DENSITY
>??)

regards

Wolfgang Breitling
Centrex Consulting Corporation
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 May 25 2004 - 07:45:59 CDT