oracle spm 사용한 sql plan 이관

1. RUN QUERY ON THE SOURCE INSTANCE

select distinct plan_hash_value from v$sql where sql_id='6wvw48wpnsmp1';

 

 

2. LOAD THE PLAN TO SPM

set serveroutput on

declare
ret binary_integer;
 l_sql_id varchar2(13);

l_plan_hash_value number;
l_fixed varchar2(3);
l_enabled varchar2(3);
Begin
l_sql_id := '&&sql_id';
l_plan_hash_value := to_number('&&plan_hash_value');
 l_fixed := 'Yes';
 l_enabled := 'Yes';
 ret := dbms_spm.load_plans_from_cursor_cache(
     sql_id=>l_sql_id,
     plan_hash_value=>l_plan_hash_value,
     fixed=>l_fixed,
     enabled=>l_enabled);
 end;
/

 

select count(*) from dba_sql_plan_baselines ;

 

 

3. CREATE A STAGING TABLE ON THE SOURCE INSTANCE

 

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
  table_name      => 'SPM_STAGETAB',
  table_owner     => 'TEST',
  tablespace_name => 'SYSAUX');
END;
/

 

4. PACK THE BASELINE

 

DECLARE
   my_plans number;
   BEGIN
     my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
     table_name => 'SPM_STAGETAB',
     enabled => 'yes',


     table_owner => 'TEST',
     plan_name => 'SQL_PLAN_6wvw48wpnsmp1',
   sql_handle => 'SQL_6wvw48wpnsmp1');
END;
/

 

5. TRANSFER THE STAGING TABLE FROM THE SOURCE TO THE TARGET INSTANCE

 

exp file=SPM_STAGETAB.dmp tables=TEST.SPM_STAGETAB log=SPM_STAGETAB.log compress=n
imp system file=SPM_STAGETAB.dmp log=imp_SPM_STAGETAB.log fromuser=test touser=test

 

 

6. UNPACK THE BASELINE

 

select count(*) from dba_sql_plan_baselines;

SET SERVEROUTPUT ON
DECLARE
    l_plans_unpacked  PLS_INTEGER;
       BEGIN
       l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
             table_name      => 'SPM_STAGETAB',
             table_owner     => 'TEST');

          DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
    END;
/

 

7. VERIFY THE BASELINE

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin FROM dba_sql_plan_baselines;

DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_6wvw48wpnsmp1',
    PLAN_NAME       => 'SQL_PLAN_6wvw48wpnsmp1',
    ATTRIBUTE_NAME  => 'fixed',
    attribute_value => 'YES');

   DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/


select SQL_PLAN_BASELINE from v$sql where sql_id='6wvw48wpnsmp1';