Re: Bind variables and function based index

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Mon, 12 May 2008 11:46:51 -0700
Message-ID: <a2b1e7610805121146h255d3368icdf4fffc9b225b52@mail.gmail.com>


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_at_gmail.com> 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_at_gmail.com> 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_at_gmx.net> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org]
> > On Behalf Of Á¶µ¿¿í
> > > Sent: Thursday, May 08, 2008 7:31 AM
> > > To: ksmadduri_at_gmail.com
> > > Cc: oracle-l_at_freelists.org
> > > 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_at_gmail.com>:
> > > >
> > > > 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_at_gmail.com> 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_at_gmail.com
> >
> > 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
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon May 12 2008 - 13:46:51 CDT

Original text of this message