Hi Finn, Yes it is an Oracle Financials environment. Hi List, I am still investigating why the decode statement is the root cause of hte issue (in the where clause) and concatanating it with'' solves the issue. Like I mentioned there is no index on authorization_status column and so I am not suppresssing any index usage also by doing this. But this makes all the difference. Any ideas or pointers? On 5/9/08, Finn Jorgensen <finn.oracledba@xxxxxxxxx> wrote: > These table/view names look all too familiar. Oracle Financials? > > I've been tuning queries for that app that ended up being 40-way joins > by the time all views had been extrapolated. :) > > Finn > > On 5/8/08, Kumar Madduri <ksmadduri@xxxxxxxxx> wrote: > > 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >