RE: Enforcing plan via SQL plan baseline

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Thu, 2 Feb 2012 20:51:27 -0500
Message-ID: <304F58144267C5439E733532ABC9A3A114039535_at_USA0300MS02.na.xerox.net>



Thanks Kerry.

Below is the original statement:
SELECT PRIMARY_TRANSACTION_QUANTITY, ROWID,   NVL(ORIG_DATE_RECEIVED , DATE_RECEIVED), PLANNING_ORGANIZATION_ID,   PLANNING_TP_TYPE, OWNING_ORGANIZATION_ID, OWNING_TP_TYPE FROM
 MTL_ONHAND_QUANTITIES_DETAIL WHERE INVENTORY_ITEM_ID = :B1 AND   ORGANIZATION_ID = :B2 AND ORGANIZATION_TYPE = 2 AND NVL(:B3,   OWNING_ORGANIZATION_ID) = :B4 AND NVL(:B5,OWNING_TP_TYPE) = :B6 AND
  NVL(:B7,PLANNING_ORGANIZATION_ID)= :B8 AND NVL(:B9,PLANNING_TP_TYPE) = :B10
    AND SUBINVENTORY_CODE = :B11 AND NVL(LOT_NUMBER, '_at_@@') = NVL(:B12,
  '_at_@@') AND LOCATOR_ID IS NULL AND 1 = nvl(:B13,1) AND NVL(REVISION,
  '_at_@@') like NVL(:B14, '@@@') AND NVL(LPN_ID, '-999') = NVL(:B15, '-999')

   AND NVL(CONTAINERIZED_FLAG, 2) = NVL(:B16, 2) AND COST_GROUP_ID =   NVL(:B17, COST_GROUP_ID) ORDER BY DATE_RECEIVED FOR UPDATE OF   PRIMARY_TRANSACTION_QUANTITY
;

Below is what I am trying to do with it via hints: SELECT /*+ FULL(MOQD) PARALLEL(MOQD,4) */ PRIMARY_TRANSACTION_QUANTITY, ROWID,
  NVL(ORIG_DATE_RECEIVED , DATE_RECEIVED), PLANNING_ORGANIZATION_ID,   PLANNING_TP_TYPE, OWNING_ORGANIZATION_ID, OWNING_TP_TYPE FROM
 MTL_ONHAND_QUANTITIES_DETAIL MOQD WHERE INVENTORY_ITEM_ID = :B1 AND   ORGANIZATION_ID = :B2 AND ORGANIZATION_TYPE = 2 AND NVL(:B3,   OWNING_ORGANIZATION_ID) = :B4 AND NVL(:B5,OWNING_TP_TYPE) = :B6 AND
  NVL(:B7,PLANNING_ORGANIZATION_ID)= :B8 AND NVL(:B9,PLANNING_TP_TYPE) = :B10
    AND SUBINVENTORY_CODE = :B11 AND NVL(LOT_NUMBER, '_at_@@') = NVL(:B12,
  '_at_@@') AND LOCATOR_ID IS NULL AND 1 = nvl(:B13,1) AND NVL(REVISION,
  '_at_@@') like NVL(:B14, '@@@') AND NVL(LPN_ID, '-999') = NVL(:B15, '-999')

   AND NVL(CONTAINERIZED_FLAG, 2) = NVL(:B16, 2) AND COST_GROUP_ID =   NVL(:B17, COST_GROUP_ID) ORDER BY DATE_RECEIVED FOR UPDATE OF   PRIMARY_TRANSACTION_QUANTITY
;

Thanks
-----Original Message-----
From: Kerry Osborne [mailto:kerry.osborne_at_enkitec.com] Sent: Thursday, February 02, 2012 8:42 PM To: Hameed, Amir
Cc: Andy Klock; oracle-l
Subject: Re: Enforcing plan via SQL plan baseline

Try generating a 10053 trace (you'll have to flush the statement so that it will get re-parsed). Then check the trace file to see if the statement is recognized as being in SPM (just search for SPM). You should see a section where is says whether the statement is found in SPM or not. If the statement is found the trace will list the hints applied and the plan it came up with - then it will tell you if the plan matched what it was expecting. It does not use the normal plan_hash_value listed in V$SQL to make this determination by the way. If the plan doesn't match (which is possible) then it will ignore the baseline. Hopefully this will give you enough info to figure out what's going on.

Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com

On Feb 2, 2012, at 3:42 PM, Hameed, Amir wrote:

> Yes...in the enabled column in dba_sql_plan_baselines shows a value of
NO.
>
> -----Original Message-----
> From: andyklock_at_gmail.com [mailto:andyklock_at_gmail.com] On Behalf Of
Andy Klock
> Sent: Thursday, February 02, 2012 4:42 PM
> To: Hameed, Amir
> Cc: oracle-l
> Subject: Re: Enforcing plan via SQL plan baseline
>
> That's strange. Can you verify that it was actually disabled?
>
> SELECT sql_handle, plan_name, enabled, accepted
> FROM dba_sql_plan_baselines
> WHERE sql_handle = '&1'
>
> On Thu, Feb 2, 2012 at 4:30 PM, Hameed, Amir <Amir.Hameed_at_xerox.com>
wrote:

>> Thanks...that did not work either.
>> 
>> -----Original Message-----
>> From: andyklock_at_gmail.com [mailto:andyklock_at_gmail.com] On Behalf Of
Andy
>> Klock
>> Sent: Thursday, February 02, 2012 4:28 PM
>> To: Hameed, Amir
>> Subject: Re: Enforcing plan via SQL plan baseline
>> 
>> Disable the plan(s) that you don't want to be used:
>> 
>> DBMS_SPM.alter_sql_plan_baseline
>> attribute_name  => 'enabled',
>> attribute_value => 'NO');
>> 

> --
> http://www.freelists.org/webpage/oracle-l
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 02 2012 - 19:51:27 CST

Original text of this message