Re: 10053 curiosity (changes with and without bind)
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 25 May 2004 06:48:31 -0600
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@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
-----------------------------------------------------------------
Other related posts:
