RE: Enforcing plan via SQL plan baseline
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 OfAndy
>> 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-lReceived on Thu Feb 02 2012 - 19:51:27 CST