Re: Enforcing plan via SQL plan baseline

From: Saurabh Sood <email2sood_at_gmail.com>
Date: Fri, 3 Feb 2012 14:19:42 +0800
Message-ID: <CALHwA6DZyamSAGUfTdFYbbrANLNLTWzLyWPw788FUj79GLcuyg_at_mail.gmail.com>

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_at_xerox.com> wrote:

> Thanks Mihajlo...I have tried that as well but the outcome was the same.
>
>
> From: Mihajlo Tekic [mailto:mihajlo.tekic_at_gmail.com]
> Sent: Thursday, February 02, 2012 4:22 PM
> To: Hameed, Amir
> Cc: oracle-l_at_freelists.org
> 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_at_xerox.com>
> 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
>
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
-- Saurabh Sood


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 03 2012 - 00:19:42 CST