Hi Milen, Thank you for the suggestions. The run time plan and theoritical plan both were using the 'bad plan'. But from sqlplus if I supply the bind variables then it was using the right indexes. On further investigation, I found that this line is causing the 'bad' explain plan -RH.AUTHORIZATION_STATUS=DECODE(:B3, ,'1',RH.AUTHORIZATION_STATUS,:B3). If I replace that with RH.AUTHORIZATION_STATUS=DECODE('1' ,'1',RH.AUTHORIZATION_STATUS,'1' ) the the explain plan shifts to use the 'good' plan. SELECT /*+ INDEX(PO_REQUISITION_LINES_ALL XXDL_PO_REQUISITION_LINES_F2) */ RH.SEGMENT1 REQUISITION_NUMBER, XXDL_REQ_WEB_INQUIRY.DECODE_PERSON_NAME(RH.PREPARER_ID) ORIGINATOR_NAME, RL.SUGGESTED_VENDOR_NAME, RH.CREATION_DATE, RH.AUTHORIZATION_STATUS, DECODE(NVL(PH.PO_HEADER_ID,'1'),'1',' ',NVL(PH.AUTHORIZATION_STATUS, 'INCOMPLETE')) PO_STATUS FROM PO.PO_REQUISITION_LINES_ALL RL, PO.PO_REQUISITION_HEADERS_ALL RH, PO.PO_REQ_DISTRIBUTIONS_ALL RD, PO.PO_DISTRIBUTIONS_ALL PD, PO.PO_HEADERS_ALL PH, MTL_CATEGORIES MC WHERE 1=1 AND UPPER(RL.SUGGESTED_VENDOR_NAME) LIKE UPPER(:B7 || '%') AND RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID+0 AND RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID AND RD.DISTRIBUTION_ID = PD.REQ_DISTRIBUTION_ID(+) AND RD.EXPENDITURE_TYPE = NVL(:B6 , RD.EXPENDITURE_TYPE) AND PD.PO_HEADER_ID = PH.PO_HEADER_ID(+) AND RH.CREATION_DATE BETWEEN :B5 AND :B4 AND RH.AUTHORIZATION_STATUS=DECODE('1' ,'1',RH.AUTHORIZATION_STATUS,'1' ) AND RL.CATEGORY_ID = MC.CATEGORY_ID AND MC.SEGMENT1||'' = NVL(:B2 ,MC.SEGMENT1) AND MC.SEGMENT2||'' = NVL(:B1 , MC.SEGMENT2) AND ((MC.ATTRIBUTE9 = 'Y') OR (RH.PREPARER_ID = FND_GLOBAL.EMPLOYEE_ID) OR (XXDL_REQ_WEB_INQUIRY.ISAPPROVER(RH.REQUISITION_HEADER_ID, FND_GLOBAL.EMPLOYEE_ID) = 'Y')) ORDER BY RH.CREATION_DATE DESC On 5/8/08, Milen Kulev <makulev@xxxxxxx> wrote: > > Hello Kumar, > could you send us please the real execution plan(s) , extracted from the > shared pool: > select * from > table(dbms_xplan.display_cursor(<sql_id>,<child#>,'+PEEKED_BINDS')); > > > Perhaps you have s bind peeking issue... > > Best Regards. Milen > ** > > -----Original Message----- > *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Á¶µ¿¿í > *Sent:* Thursday, May 08, 2008 7:31 AM > *To:* ksmadduri@xxxxxxxxx > *Cc:* oracle-l@xxxxxxxxxxxxx > *Subject:* Re: Bind variables and function based index > > Query is query. The important thing is cost and cardinality. > > Post your explain plan for both cases, using "select * from > table(dbms_xplan.display(null,null,'all'))"; (if you're on 10g) or > "select * from table(dbms_xplan.display)" (on 9i) > > Dion Cho > > > 2008/5/8, Kumar Madduri <ksmadduri@xxxxxxxxx>: >> >> Hi List, >> In continuation with my earlier post >> This is the query. Couple of things I noticed about this is that this >> query does not use the Function based index created on suggested_vendor_name >> and also the explain plan repeats itslef (it does the same steps twice). >> Attached is the explain plan so that you see what I mean. >> >> On the other hand if I replace these bind variables with actual values, it >> does use the index and no repeatition (in explain plan) >> >> SELECT >> RH.SEGMENT1 REQUISITION_NUMBER, >> XXDL_REQ_WEB_INQUIRY.DECODE_PERSON_NAME(RH.PREPARER_ID) ORIGINATOR_NAME, >> >> >> RL.SUGGESTED_VENDOR_NAME, RH.CREATION_DATE, RH.AUTHORIZATION_STATUS, >> DECODE(NVL(PH.PO_HEADER_ID,'1'),'1',' ',NVL(PH.AUTHORIZATION_STATUS, >> 'INCOMPLETE')) PO_STATUS >> FROM >> >> PO.PO_REQUISITION_LINES_ALL RL, PO.PO_REQUISITION_HEADERS_ALL RH, >> >> >> PO.PO_REQ_DISTRIBUTIONS_ALL RD, PO.PO_DISTRIBUTIONS_ALL PD, >> PO.PO_HEADERS_ALL PH, MTL_CATEGORIES MC WHERE 1=1 AND >> UPPER(RL.SUGGESTED_VENDOR_NAME) LIKE UPPER(:B7 || '%') AND >> >> RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID+0 AND >> >> >> RL.REQUISITION_LINE_ID = RD.REQUISITION_LINE_ID AND RD.DISTRIBUTION_ID = >> PD.REQ_DISTRIBUTION_ID(+) AND RD.EXPENDITURE_TYPE = NVL(:B6 , >> RD.EXPENDITURE_TYPE) AND PD.PO_HEADER_ID = PH.PO_HEADER_ID(+) AND >> >> RH.CREATION_DATE BETWEEN :B5 AND :B4 AND RH.AUTHORIZATION_STATUS=DECODE(:B3 >> >> >> ,'1',RH.AUTHORIZATION_STATUS,:B3 ) AND RL.CATEGORY_ID = MC.CATEGORY_ID AND >> MC.SEGMENT1||'' = NVL(:B2 ,MC.SEGMENT1) AND MC.SEGMENT2||'' = NVL(:B1 , >> >> MC.SEGMENT2) AND ((MC.ATTRIBUTE9 = 'Y') OR (RH.PREPARER_ID = >> >> >> FND_GLOBAL.EMPLOYEE_ID) OR >> (XXDL_REQ_WEB_INQUIRY.ISAPPROVER(RH.REQUISITION_HEADER_ID, >> FND_GLOBAL.EMPLOYEE_ID) = 'Y')) ORDER BY RH.CREATION_DATE DESC >> >> >> >> On Tue, May 6, 2008 at 5:56 PM, Mark Brinsmead < >> pythianbrinsmead@xxxxxxxxx> wrote: >> >> >>> Using bind variables rather than literals can alter the cardinality >>> estimates used by the CBO, and that can alter the execution plan. >>> >>> It might be helpful to provide the actual query, or at least the >>> execution plans. >>> >>> >>> >>> On Tue, May 6, 2008 at 5:54 PM, Kumar Madduri <ksmadduri@xxxxxxxxx> >>> wrote: >>> >>>> Hi >>>> Is there any restriction with bind variables and function based indexes. >>>> I noticed that if I have literals fbi is being used but if I replace that >>>> with bind variables then fbi is not used. DB version is 10.2.0.1. You >>>> cant seem to force it to use FBI through hints also. As I am investigating >>>> this, I thought of posting here to see if there are any known issues or >>>> restrictions that prevent function based indexes usage with bind variables. >>>> QUERY_REWRITE_INTEGRITY is set to trusted and query_rewrite_enabled is >>>> true. >>>> >>>> thank you >>>> - kumar >>>> >>> >>> >>> >>> >>> -- >>> Cheers, >>> -- Mark Brinsmead >>> Senior DBA, >>> The Pythian Group >>> http://www.pythian.com/blogs >> >> >> >> >> >> > >