SQL Plan Transfer
A small example to demonstrate this
plan_hash_value= 4160939255
sqlid = 0zwsk3sanq3mp
SQL> set serveroutput on
SQL> 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 ;
select SQL_HANDLE, PLAN_NAME from dba_sql_plan_baselines;
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'SPM_STAGETAB',
table_owner => 'MONITOR',
tablespace_name => 'TOOLS');
END;
DECLARE
my_plans number;
BEGIN
my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => 'SPM_STAGETAB',
enabled => 'yes',
table_owner => 'MONITOR',
plan_name => 'SQL_PLAN_ghhs1n37rh9qa49455cd7',
sql_handle => 'SQL_f84301a0cf7826ca');
END;
/
expdp monitor/M0n!t0r!ng456 directory=EXP_DIR file=SPM_STAGETAB.dmp tables=MONITOR.SPM_STAGETAB log=SPM_STAGETAB.log compress=n
GRANT READ, WRITE ON DIRECTORY EXP_DIR TO Monitor;
GRANT READ, WRITE ON DIRECTORY EXP_DIR TO Monitor;
impdp monitor/M0n!t0r!ng456 directory=EXP_DIR dumpfile=SPM_STAGETAB.dmp logfile=imp_SPM_STAGETAB_050521.log remap_schema=MONITOR:MONITOR
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 => 'MONITOR');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
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_f84301a0cf7826ca',
PLAN_NAME => 'SQL_PLAN_ghhs1n37rh9qa49455cd7',
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='0zwsk3sanq3mp';
Comments