Implement 10g SQL PROFILE in upgraded 11g DB

Process to implement 10g SQL PROFILE in upgraded 11g DB

Log on to 11g DB and do the following steps

Sqlplus “/as sysdba”
alter session set optimizer_features_enable='10.2.0.4';

NOTE: - Before running the under mentioned Step, make sure you have the SQL_ID


declare
v_sql varchar2(8000); c NUMBER;
begin
select sql_text into v_sql from DBA_HIST_SQLTEXT where sql_id='6w5b08q6kytqk';
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, to_char(exact_matching_signature,999999999999999999999999), sysdate from v$sqlarea where sql_id='6w5b08q6kytqk';

SQL_ID        LAST_LOAD_TIME      PLAN_HASH_VALUE TO_CHAR(EXACT_MATCHING_SI SYSDATE
------------- ------------------- --------------- ------------------------- -------------------
6w5b08q6kytqk 2013-07-11-10.28.39       769474218       8398069496001790632 2013-07-11-11.45.38



Run the under mentioned PROC with the values you got from above.

declare
 n number;
 begin n:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
 sql_id => '6w5b08q6kytqk',
 plan_hash_value => '769474218',
 FIXED  => 'YES',
 ENABLED => 'YES');
 dbms_output.put_line(n);
 end;
 /

==================

Verify the above step to make sure you had put the things in place and its results

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 = 8398069496001790632;

Before you fired the sql query

SQL_HANDLE                PLAN_NAME                      ORIGIN         VERSION    CREATED                        LAST_EXECUTED       ENA ACC
------------------------- ------------------------------ -------------- ---------- ------------------------------ ------------------- --- ---
SQL_748befb6343b02a8      SQL_PLAN_792zgqsu3q0p8ae304cc8 MANUAL-LOAD    11.2.0.3.0 2013-07-11-13.08.07                                YES YES

After I fired the sql Query
SQL_HANDLE                PLAN_NAME                      ORIGIN         VERSION    CREATED                        LAST_EXECUTED       ENA ACC
------------------------- ------------------------------ -------------- ---------- ------------------------------ ------------------- --- ---
SQL_748befb6343b02a8      SQL_PLAN_792zgqsu3q0p8ae304cc8 MANUAL-LOAD    11.2.0.3.0 2013-07-11-13.08.07            2013-07-11-13.14.02 YES YES



Thanks for Looking