Re: 10053 curiosity (changes with and without bind)

From: Henry Poras <hporas_at_comcast.net>
Date: Tue, 25 May 2004 08:15:52 -0400
Message-ID: <000d01c44252$063b7d50$1102a8c0@GROUCHO>

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_at_centrexcc.com> To: <oracle-l_at_freelists.org> 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_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
> -----------------------------------------------------------------

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:13:15 CDT