Re: Enforcing plan via SQL plan baseline

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Thu, 2 Feb 2012 19:41:44 -0600
Message-Id: <32279751-85FD-4376-9491-59ECF02D8D6D_at_enkitec.com>



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:41:44 CST

Original text of this message