Re: Enforcing plan via SQL plan baseline
Date: Wed, 15 Feb 2012 10:12:50 -0800 (PST)
Message-ID: <1329329570.33640.YahooMailNeo_at_web36806.mail.mud.yahoo.com>
Hi Jared,
Can you check if the SQL plan baseline is reproducible? You can do this by querying the reproduced in dba_sql_plan_baselines. If it says yes then flush the cursor from the shared pool and try parsing the statement again. If it says no then you have most likely hit upon a bug.
I had similar problem after migrating a stored outline into SQL plan baeline management.In my case,the plan is accepted,enabled and reproducible.But then the optimizer is simply ignoring the SQL plan baseline.
SQL> select sql_handle,plan_name,enabled,accepted,reproduced from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENA ACC REP ------------------------------ ------------------------------ --- --- --- SQL_e539090ea7b58f7d DYNROLE1 YES YES YES
Regard,s
Antony
From: Jared Still <jkstill_at_gmail.com> To: Wolfgang Breitling <centrex_at_centrexcc.com> Cc: kerry.osborne_at_enkitec.com; Amir.Hameed_at_xerox.com; Andy Klock <andy_at_oracledepot.com>; oracle-l Freelists <oracle-l_at_freelists.org> Sent: Tuesday, February 14, 2012 9:00 PM Subject: Re: Enforcing plan via SQL plan baseline
On Mon, Feb 6, 2012 at 11:20 AM, Wolfgang Breitling
<centrex_at_centrexcc.com>wrote:
> And you disabled the original (unhinted) plan? Did you follow the steps in
> Maria's blog ( http://blogs.oracle.com/**optimizer/tags/sql_plan_**
> management <http://blogs.oracle.com/optimizer/tags/sql_plan_management>)? I haven't had a problem substituting a different plan for a sql. My
> problem is that Peoplesoft has too many sql where this technique is not
> applicable ( because they use literals in sql ).
>
Got back to this today.
Still no joy. The only baseline in SPM is the one I want to be used, and Oracle is clearly rejecting it.
10053 says something to the effect "SPM: cannot reproduce" - I forget the exact text at the moment.
The only difference in the SQL used to capture the plan was the addition of an index hint.
Currently reading one of Kerry's blog entries that may shed some light on it.
Jared
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 15 2012 - 12:12:50 CST