Re: Different Plans for Literal Vs Bind Variables

From: Ian MacGregor <ian_at_slac.stanford.edu>
Date: Sat, 10 Jan 2009 22:34:28 -0800
Message-ID: <C58ED2F4.162C8%ian_at_slac.stanford.edu>



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
Received on Sun Jan 11 2009 - 00:34:28 CST

Original text of this message