oracleSPM执行计划管理(三)
据字典复制到舞台表
declare
k int;
begin
k:=dbms_spm.pack_stgtab_baseline(
TABLE_NAME=>'BASELINE_STG01',
TABLE_OWNER=>'SCOTT'
);
end;
/
declare
v_ret number(100);
begin
v_ret := dbms_spm.pack_stgtab_baseline(
table_name =>'mystgtab',
table_owner=>user,
sql_handle=>'SQL_e436abaac44f99d8',
--plan_name=>'SQL_PLAN_f8dpbpb24z6fs94ecae5c',
);
end;
/
/**********语法:
DBMS_SPM.PACK_STGTAB_BASELINE (
table_name IN VARCHAR2,
table_owner IN VARCHAR2 := NULL,
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
sql_text IN CLOB := NULL,
creator IN VARCHAR2 := NULL, origin IN VARCHAR2 := NULL,
enabled IN VARCHAR2 := NULL,
accepted IN VARCHAR2 := NULL,
fixed IN VARCHAR2 := NULL,
module IN VARCHAR2 := NULL,
action IN VARCHAR2 := NULL)
RETURN NUMBER;
*****/
----3.用expdp\impdp或exp,imp工具从测试库将表移到目标库
----4.将sql plan baseline从舞台表复制到数据字典
---4.1 将所有sql plan baseline从舞台表复制到数据字典
SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name =>'BASELINE_STG01',
table_owner => 'SCOTT');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
---4.2 将sql plan baseline中有关T1表的从舞台表复制到数据字典
declare
v_ret varchar2(100);
begin
v_ret := dbms_spm.unpack_stgtab_baseline(
table_name =>
'BASELINE_STG01',
table_owner=>'SCOTT',
sql_text=>'%FROM t1%'
);
end;
/
/*********语法:
DBMS_SPM.UNPACK_STGTAB_BASELINE (
table_name IN VARCHAR2,
table_owner IN VARCHAR2 := NULL,
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
sql_text IN CLOB := NULL,
creator IN VARCHAR2 := NULL, origin IN VARCHAR2 := NULL,
enabled IN VARCHAR2 := NULL,
accepted IN VARCHAR2 := NULL,
fixed IN VARCHAR2 := NULL,
module IN VARCHAR2 := NULL,
action IN VARCHAR2 := NULL)
RETURN NUMBER;
如果只指定table_name与table_owner,就是处理所有sql plan baseline。
sql_handle与plan_name一起能精确识别一个sql plan baseline,plan_name为可选项。
sql_text里面区分大小写
/********
SELECT sql_handle, plan_name,enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%select sid,sname from t4 where sid<=:v%';
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SQL_4e6155ac1d5b5962 SQL_PLAN_4wsapphfpqqb214816fa9 YES YES
---删除sql计划基线
declare
v_ret varchar2(100);
begin
v_ret := dbms_spm.unpack_stgtab_baseline(
sql_handle=>'mystgtab',
plan_name=>'swew223'
);
end;
/
这两个参数至少要指定一个。