Re: Bind variables and function based index

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Thu, 8 May 2008 15:21:04 -0700
Message-ID: <a2b1e7610805081521u575afe34kfa79fb237c5362a7@mail.gmail.com>

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 Thu May 08 2008 - 17:21:04 CDT