Case Study: How to Return a Good SQL Execution Plan from 10g Days After an 11g Migration

Confirm that the 10G execution plan is/was better

alter session set optimizer_features_enable='10.2.0.4';

How to get 11G to use the 10G execution plan

alter session set optimizer_features_enable='10.2.0.4';
 declare
 v_sql varchar2(8000);
  c NUMBER;
 begin
 select sql_text into v_sql from DBA_HIST_SQLTEXT where sql_id='djkbyr8vkc64h';
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, v_sql, dbms_sql.NATIVE);
  dbms_sql.close_cursor(c);
 end;

select sql_id,LAST_LOAD_TIME,PLAN_HASH_VALUE, exact_matching_signature, sysdate from v$sqlarea where sql_id='djkbyr8vkc64h';
 
 SQL_ID LAST_LOAD_TIME PLAN_HASH_VALUE EXACT_MATCHING_SIGNATURE SYSDATE
 ------------- ------------------- --------------- ------------------------ -------------------
 djkbyr8vkc64h 2012.11.12 01:25:51 810205201 14465951278806438046 2012.11.12 01:26:04

declare
 n number;
 begin
 n:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
 sql_id => 'djkbyr8vkc64h',
 plan_hash_value => '810205201'
 FIXED => 'YES',
 ENABLED => 'YES');
 dbms_output.put_line(n);
 end;
 /
 1
 
 PL/SQL procedure successfully completed.

col version for a10
 col SQL_HANDLE for a25
 col CREATED for a30
 col sb.last_executed for a30
 SELECT
 sb.sql_handle,
 sb.plan_name,
 sb.origin,
 sb.version,
 cast(sb.created as date) created,
 cast(sb.last_executed as date) last_executed,
 sb.enabled,
 sb.accepted
 FROM
 dba_sql_plan_baselines sb
 WHERE
 sb.signature = 14465951278806438046;
 
 SQL_HANDLE PLAN_NAME ORIGIN VERSION CREATED LAST_EXECUTED ENA ACC
 ------------------------- ------------------------------ -------------- ---------- ------------------------------ ------------------- --- ---
 SQL_c8c1620b4f5d909e SQL_PLAN_cjhb21d7pv44y27600b06 MANUAL-LOAD 11.2.0.3.0 2012.11.12 01:31:58 YES YES

set output on
 DECLARE
  i NATURAL;
 BEGIN
  i := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
 sql_handle=>'SQL_c8c1620b4f5d909e',
 PLAN_NAME=> 'SQL_PLAN_cjhb21d7pv44y27600b06',
 attribute_name=>'enabled',attribute_value=>'no');
  dbms_output.put_line(i);
 END;
 /

Credits

• Gavin Soorma whose presentation "Upgrade with confidence – 11g Optimizer Plan Stability" hinted to try adjusting optimizer_features_enable back to 10g value on a session level. Thank you my friend Gavin. :)
• Maxym Kharchenko whose blog post "How to find SPM baseline by sql_id" pointed me to the relationship between sql_id, v$sqlarea.exact_matching_signature, and dba_sql_plan_baselines.signature. Thank you my friend Max. :)