RE: Enforcing plan via SQL plan baseline

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Thu, 2 Feb 2012 16:17:39 -0500
Message-ID: <304F58144267C5439E733532ABC9A3A1140393A8_at_USA0300MS02.na.xerox.net>

Thanks Raj.
I have tried it several times and the optimizer keeps ignoring the manually loaded plan and overwriting it with the one that it is generating, which is an optimal plan. So, the question is, can SQL profiles be used to force a bad plan over a good plan?  

Thanks  

From: Antony Raj [mailto:ca_raj_at_yahoo.com] Sent: Thursday, February 02, 2012 4:14 PM To: Hameed, Amir; oracle-l_at_freelists.org Subject: Re: Enforcing plan via SQL plan baseline  

Hi Amir,  

You need to load the plan manually using DBMS_SPM package.Identify the SQLID of the plan that you need.

Manually loaded plans are automatically marked as accepted, so manual loading forces the evolving process.  

Thanks    

From: "Hameed, Amir" <Amir.Hameed_at_xerox.com> To: oracle-l_at_freelists.org
Sent: Thursday, February 2, 2012 3:56 PM Subject: Enforcing plan via SQL plan baseline

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 Received on Thu Feb 02 2012 - 15:17:39 CST