Re: Enforcing plan via SQL plan baseline
- From: Antony Raj <ca_raj@xxxxxxxxx>
- To: "jkstill@xxxxxxxxx" <jkstill@xxxxxxxxx>, Wolfgang Breitling <centrex@xxxxxxxxxxxxx>
- Date: Wed, 15 Feb 2012 10:12:50 -0800 (PST)
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@xxxxxxxxx>
To: Wolfgang Breitling <centrex@xxxxxxxxxxxxx>
Cc: kerry.osborne@xxxxxxxxxxx; Amir.Hameed@xxxxxxxxx; Andy Klock
<andy@xxxxxxxxxxxxxxx>; oracle-l Freelists <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx>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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
