Re: 10053 curiosity (changes with and without bind)

From: Jared Still <jkstill_at_cybcon.com>
Date: Mon, 24 May 2004 22:28:23 -0700
Message-Id: <1085462903.15775.2045.camel@poirot>

I would suspect that there are histograms on the columns. The bind variable precludes the use histograms by the CBO, whereas the query with the hardcoded value is allowing the CBO to use them when choosing an execution path.

Jared

On Mon, 2004-05-24 at 20:16, 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
> Table: PS_ITM_CAT_TBL Orig CDB: 20304 Cmptd CDN: 10 <--
> Access Path : index (iff)
> INDEX#: 23177 TABLE: PS_ITM_CAT_TBL
> CST: 20 IXSEL: 0.0000e+00 TBSEL: 1.0000e+00
> Access Path : index (index-only)
> INDEX#: 23177 TABLE: PS_ITM_CAT_TBL
> CST: 4.00 IXSEL: 1.0000e-02 TBSEL: 1.0000e-02 <--
>
> WITH EXPLICIT VALUE
> 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
> Table: PS_ITM_CAT_TBL Orig CDB: 20304 Cmptd CDN: 923 <--
> Access Path : index (iff)
> INDEX#: 23177 TABLE: PS_ITM_CAT_TBL
> CST: 20 IXSEL: 0.0000e+00 TBSEL: 1.0000e+00
> Access Path : index (index-only)
> INDEX#: 23177 TABLE: PS_ITM_CAT_TBL
> CST: 206 IXSEL: 1.0000e+00 TBSEL: 1.0000e+00 <--
>
> All of the other table statistics and access path data were identical.
...

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 - 00:17:37 CDT