Re: Enforcing plan via SQL plan baseline
- From: Saurabh Sood <email2sood@xxxxxxxxx>
- To: Amir.Hameed@xxxxxxxxx
- Date: Fri, 3 Feb 2012 14:19:42 +0800
Hi Amir,
In this case, if you want to use FTS plan, then you have to manually load
the plan from the cursor cache and while using
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE set the attribute "FIXED" to yes. It
will fix the plan for that particular sqlid used.
The database does not verify manually loaded plans for performance, but
adds them as accepted plans. So you should see accepted=YES in
DBA_SQL_PLAN_BASELINES view after adding the plan.
Check the fixed column value under DBA_SQL_PLAN_BASELINES, it should be
yes. Now flush the query from shared pool to check the results.
- Saurabh Sood
www.askdba.org
On Fri, Feb 3, 2012 at 5:26 AM, Hameed, Amir <Amir.Hameed@xxxxxxxxx> wrote:
> Thanks Mihajlo...I have tried that as well but the outcome was the same.
>
>
> From: Mihajlo Tekic [mailto:mihajlo.tekic@xxxxxxxxx]
> Sent: Thursday, February 02, 2012 4:22 PM
> To: Hameed, Amir
> Cc: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Enforcing plan via SQL plan baseline
>
>
>
>
>
> Have you tried to fix the plan that you want to be used. (Fixed Plan
> Baseline)
>
>
>
> http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#BA
> BICJBG
>
>
>
> ~Mihajlo
>
> On Thu, Feb 2, 2012 at 2:56 PM, Hameed, Amir <Amir.Hameed@xxxxxxxxx>
> wrote:
>
> Folks,
> When using SQL plan baseline, is it possible to instruct the optimizer
> to use a baseline that has sub-optimal plan than the one it is currently
> using. For example, a SQL statement, which cannot be altered because it
> is coming out of a COTS application, is currently using an index scan
> and I would like to force it to use FTS. I have tried forcing the
> optimizer to use the SQL plan baseline but it keeps using the index path
> and hence ignoring the plan baseline. Any feedback will be appreciated.
>
>
>
> Thanks
>
> Amir
>
>
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
-- Saurabh Sood
--
//www.freelists.org/webpage/oracle-l
Other related posts: