Re: Different Plans for Literal Vs Bind Variables
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_NAMECOLUMN_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-lReceived on Sun Jan 11 2009 - 00:34:28 CST