forcing a hinted plan on a non-hinted statement

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

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