Re: 10053 curiosity (changes with and without bind)
- From: "Henry Poras" <hporas@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 May 2004 08:15:52 -0400
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
??)
Henry
----- Original Message -----
From: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, May 24, 2004 11:38 PM
Subject: Re: 10053 curiosity (changes with and without bind)
> Your columns have histograms on them. The query with the literal values
> uses the information in the histogram, the one with bind variables does
not
> ( in 8i, in 9 the optimizer will use the bind value during the first parse
> of the sql).
>
> At 09:16 PM 5/24/2004, Henry Poras wrote:
> >I'm going slightly batty(er) trying to understand why Oracle (8.1.7.4) is
> >using two different execution plans for two nearly identical queries. The
> >only difference between the two is that one uses a bind variable where
the
> >other uses a hard coded value. The field in question, however, is NOT
> >NULL, with only one distinct value (# of distinct values in
dba_tab_column
> >shows 1). Trying to find the source of this difference, I looked at a
> >10053 trace and found the following:
> >WITH BIND
> >SINGLE TABLE ACCESS PATH
> >Column: SETID Col#: 1
> > NDV: 1 NULLS: 0 DENS:
> > 6.6667e-03
> >Column: EFFDT Col#: 5
> > NDV: 22 NULLS: 0 DENS: 2.4626e-05
>
> 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
> -----------------------------------------------------------------
----------------------------------------------------------------
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:
