Bind Variable Peeking Not Working Properly

From: Daniel <danielroy10junk_at_hotmail.com>
Date: 7 Dec 2005 07:29:09 -0800
Message-ID: <1133969349.911450.64850@z14g2000cwz.googlegroups.com>

Hi guys, I'm running Oracle 9.2.0.6 on Sun. I have this rather huge (Siebel-generated) query which goes along the line of:

select

    T14.CONFLICT_ID,

      T14.LAST_UPD,
      T14.CREATED,
      T14.LAST_UPD_BY,
      T14.CREATED_BY,
      T14.MODIFICATION_NUM,
      ...
from
     SIEBEL.S_LST_OF_VAL T1,
       SIEBEL.S_PROD_INT T2,
       SIEBEL.S_CONTACT_FNX T3,
       SIEBEL.S_ORG_EXT T4,
       SIEBEL.S_CONTACT_BU T5,
       SIEBEL.S_CONTACT T6,
       SIEBEL.S_ASSET_ATX T7,
       SIEBEL.S_ASSET_BU T8,
       SIEBEL.S_PARTY T9,
       SIEBEL.S_ORG_EXT T10,
       SIEBEL.S_ASSET_CON T11,
       SIEBEL.S_ORG_EXT T12,
       SIEBEL.S_VHCL_FIN_DTL T13,
       SIEBEL.S_ASSET T14
   WHERE
      T14.PR_FIN_DTL_ID = T13.ROW_ID (+) AND
      T14.PREF_SRV_DLR_ID = T4.PAR_ROW_ID (+) AND
      T14.RTNG_DLR_ID = T10.PAR_ROW_ID (+) AND
      T14.ROW_ID = T8.ASSET_ID AND T8.BU_ID = :v1 AND
      T14.PROD_ID = T2.ROW_ID (+) AND
      T14.DLR_ID = T12.PAR_ROW_ID (+) AND
      T14.ROW_ID = T7.PAR_ROW_ID (+) AND
      T14.REF_NUMBER_2 = T1.NAME AND T1.TYPE =
'AUTO_VEHICLE_OPTY_TYPE_CD' AND T1.LANG_ID = :v2 AND
      T14.PR_CON_ID = T11.CONTACT_ID (+) AND T14.ROW_ID = T11.ASSET_ID
(+) AND
      T14.PR_CON_ID = T9.ROW_ID (+) AND
      T14.PR_CON_ID = T6.PAR_ROW_ID (+) AND
      T14.PR_CON_ID = T3.PAR_ROW_ID (+) AND
      T14.PR_CON_ID = T5.ROW_ID (+) AND


      (T8.BU_ID = :v3) AND

      (T14.PR_CON_ID = :v4 AND T1.VAL LIKE :v5)


Bind variable v1: 1-1X-298
Bind variable v2: ENU
Bind variable v3: 1-1X-298

Bind variable v4: 1-11NB-905
Bind variable v5: %Sold

Now if you take a look at bind variable v5 ("%Sold"), Oracle should use bind variable peeking to figure out that an index based on S_LST_OF_VAL.VAL (T1) is a bad idea. If I replace bind variable v5 with its value in the query, it does the proper thing in not using this index. I even made sure that a hard parse ocurred by flushing the shared pool first, without any success. I ran from SQL*Plus:

variable v1 varchar2(15)
exec :v1 := '1-1X-298'
variable v2 varchar2(15)
exec :v2 := 'ENU'
...
Then I ran the query as such (using bind variables) and tkprof showed a hard parse for the query, with the exec plan still using the same harmful index. optimizer_features_enable is 9.2.0. Any idea what's wrong?

Daniel Received on Wed Dec 07 2005 - 09:29:09 CST