Re: Different Plans for Literal Vs Bind Variables

The definition of IDX$_7D3D0001 is

SQL> select table_name, column_name, column_position from dba_ind_columns
  2  where index_owner = 'SYSADM' and index_name = 'IDX$$_7D3D0001';

TABLE_NAME                     COLUMN_NAME
COLUMN_POSITION
------------------------------ ------------------------------
---------------
PS_DEPT_TBL                    DEPTID
1
PS_DEPT_TBL                    SETID
2
PS_DEPT_TBL                    EFFDT
3




The PS_DEPT_SCRTY_VW object  IS A DELIVERED VIEW ...

SQL> select DBMS_METADATA.GET_DDL('VIEW','PS_DEPT_SCRTY_VW', 'SYSADM')
  2  from dual
  3  /

DBMS_METADATA.GET_DDL('VIEW','PS_DEPT_SCRTY_VW','SYSADM')
----------------------------------------------------------------------------
----

  CREATE OR REPLACE FORCE VIEW "SYSADM"."PS_DEPT_SCRTY_VW" ("SETID",
"EFFDT", "D
EPTID", "DESCR", "TREE_NODE_NUM", "TREE_NODE_NUM_END") AS
  SELECT B.SETID , A.EFFDT ,B.DEPTID ,B.DESCR ,A.TREE_NODE_NUM
,A.TREE_NODE_NUM_
END FROM PSTREENODE A ,PS_DEPT_TBL B WHERE A.TREE_NAME = 'DEPT_SECURITY' AND
A.S
ETID = B.SETID AND A.TREE_NODE = B.DEPTID AND B.EFFDT = ( SELECT MAX(EFFDT)
FROM
 PS_DEPT_TBL C WHERE C.SETID = A.SETID AND C.DEPTID = B.DEPTID AND C.EFFDT
<= A.
EFFDT)
--------------------------------------------------------------------------

OPTIMIZER_MAX_PERMUTATIONS is obsolete in 10G.  As OPTIMIZER_FEATURES_ENABLE
is 10.2.0.3 the value is 2000.

I ran the 10053 traces.

Here is the peeked bind variable information.

Peeked Binds
============
  Bind variable information
    position=1
    datatype(code)=1
    datatype(string)=VARCHAR2(32)
    char set id=46
    char format=1
    max length=32
    value=SLAC
  Bind variable information
    position=2
    datatype(code)=1
    datatype(string)=VARCHAR2(32)
    char set id=46
    char format=1
    max length=32
    value=2008-01-08
--------------------------------------

The costs differ.  Here is the information  from the  query using the
literals.


Index: IDX$$_7D3D0001  Col#: 2 1 3
    LVLS: 1  #LB: 4  #DK: 894  LB/K: 1.00  DB/K: 1.00  CLUF: 737.00


Access Path: index (index (FFS))
    Index: IDX$$_7D3D0001
    resc_io: 3.00  resc_cpu: 225140
    ix_sel: 0.0000e+00  ix_sel_with_filters: 1
  Access Path: index (FFS)
    Cost:  3.03  Resp: 3.03  Degree: 1
      Cost_io: 3.00  Cost_cpu: 225140
      Resp_io: 3.00  Resp_cpu: 225140


And the query using the bind variables


Index: IDX$$_7D3D0001  Col#: 2 1 3
    LVLS: 1  #LB: 4  #DK: 894  LB/K: 1.00  DB/K: 1.00  CLUF: 737.00

Access Path: index (index (FFS))
    Index: IDX$$_7D3D0001
    resc_io: 3.00  resc_cpu: 585001
    ix_sel: 0.0000e+00  ix_sel_with_filters: 1
  Access Path: index (FFS)
    Cost:  3.07  Resp: 3.07  Degree: 1
      Cost_io: 3.00  Cost_cpu: 585001
      Resp_io: 3.00  Resp_cpu: 585001

-----------------------------------------------------------

I think this shows that the access paths were given different costs.

Ian



--
http://www.freelists.org/webpage/oracle-l

Other related posts: