Re: forcing a hinted plan on a non-hinted statement
- From: John Kanagaraj <john.kanagaraj@xxxxxxxxx>
- To: jobmiller@xxxxxxxxx
- Date: Mon, 15 Mar 2010 17:18:20 -0700
Thanks Job for documenting this. We use a similar procedure for
Outlines and the code below helps me (and us all!) when moving to 11g
SPM.
John
On Mon, Mar 15, 2010 at 7:57 AM, Job Miller <jobmiller@xxxxxxxxx> wrote:
> I saw this and thought it might be helpful to someone.
>
> If you have a statement that you can hint to a correct plan, and you want
> that hinted plan to be the correct plan all the time for the non-hinted
> statement, use SPM in 11g to associate a good plan (from the hinted
> execution) with a particular non-hinted SQL statement.
>
> 1. Run the non-hinted SQL producing the bad plan
> 2. Find the SQL_ID
> 3. Create a SQL Plan Baseline for the statment
>
> variable cnt number;
> execute :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'...');
>
> 4. Disabling that plan.. requires sql_hanlde and plan_name:
> select sql_handle, sql_text, plan_name, enabled from dba_sql_plan_baselines;
>
> 5. Alter SQL Plan Base to disabled:
>
> variable cnt number;
> exec :cnt:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
> SQL_HANDLE=> :sql_handle,
> PLAN_NAME=>:plan_name
> ATTRIBUTE_NAME=>'enabled'
> ATTRIBUTE_VALUE=>'NO');
>
> 6. Modify SQL Statement using hints and execute it.
>
> 7. Fin new SQL_ID and PLAN_HASH_VALUE
> select SQL_ID, SQL_FULLTEXT from v$sql where SQL_TEXT like
> '%YOUR_HINTED_STATEMENT%'
>
> 8. create new accepted plan for original SQL stmt by associating the
> modified plan to the original statement's SQL Handle
>
> dbms_spm.load_plans_from_cursor_cache(sql_id=>:sql_id,plan_hash_value=>:plan_hash,sql_handle=>
> :sql_handle);
>
> 9. verify hinted plan is in baseline and enabled
>
> select sql_handle, sql_text, plan_name, enabled from dba_sql_plan_baselines;
>
>
> Good luck..
>
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
John Kanagaraj <><
http://www.linkedin.com/in/johnkanagaraj
http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
--
//www.freelists.org/webpage/oracle-l
Other related posts:
