forcing a hinted plan on a non-hinted statement

From: Job Miller <>
Date: Mon, 15 Mar 2010 07:57:09 -0700 (PDT)
Message-ID: <>

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;

   SQL_HANDLE=> :sql_handle,
   ATTRIBUTE_VALUE=>'NO'); 6. Modify SQL Statement using hints and execute it.

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..       

Received on Mon Mar 15 2010 - 09:57:09 CDT

Original text of this message