绑定执行计划步骤

绑定执行计划
1、某个库中已有好的执行计划，进行绑定
1）查询某SQL各种执行计划情况

--mem_1d表示该计划在内存中，具体在哪个实例的内存中需要自行通过 sql_id,plan_hash_value 去 gv$sql 中查询 inst_id
--awr_30d表示该计划在历史中
select (select instance_name from v$instance where rownum=1) instance_name,'mem_1d' mem_awr_flag,sql_id,plan_hash_value,round(sum_ELAPSED_TIME/1000000/total_exec_num,3) avg_exe_sec,sum_EXECUTIONS,round(sum_ROWS_PROCESSED/total_exec_num) avg_ROWS_PROCESSED,
--sum_ELAPSED_TIME,EXACT_MATCHING_SIGNATURE,sum(sum_ELAPSED_TIME) over(partition by sql_id) sql_total_ELAPSED_TIME,
(select count(1) from dba_sql_profiles p where p.signature=s.EXACT_MATCHING_SIGNATURE) sql_profile,
(select count(1) from dba_sql_plan_baselines b where b.ENABLED='YES' and b.ACCEPTED='YES' and b.signature=s.EXACT_MATCHING_SIGNATURE) sql_plan_baseline,
(select count(1) from dba_sql_patches p where p.STATUS='ENABLED' and p.signature=s.EXACT_MATCHING_SIGNATURE) sql_patch,
round(sum_CPU_TIME/total_exec_num) avg_CPU_TIME,
round(sum_USER_IO_WAIT_TIME/total_exec_num) avg_USER_IO_WAIT_TIME,
round(sum_CLUSTER_WAIT_TIME/total_exec_num) avg_CLUSTER_WAIT_TIME,
round(sum_CONCURRENCY_WAIT_TIME/total_exec_num) avg_CONCURRENCY_WAIT_TIME,
round(sum_PHYSICAL_READ_BYTES/total_exec_num) avg_PHYSICAL_READ_BYTES,
round(sum_BUFFER_GETS/total_exec_num) avg_BUFFER_GETS,
round(sum_DISK_READS/total_exec_num) avg_DISK_READS,
round(sum_PHYSICAL_WRITE_BYTES/total_exec_num) avg_PHYSICAL_WRITE_BYTES,
-- SQL_TEXT
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') rpt_time
from 
(select ttt.EXACT_MATCHING_SIGNATURE,sql_id,plan_hash_value,max(SQL_TEXT) SQL_TEXT,sum(EXECUTIONS) sum_EXECUTIONS,(case when sum(EXECUTIONS) = 0 then 1 else sum(EXECUTIONS) end) total_exec_num,
sum(ROWS_PROCESSED) sum_ROWS_PROCESSED,
sum(ELAPSED_TIME) sum_ELAPSED_TIME,sum(CPU_TIME) sum_CPU_TIME,sum(USER_IO_WAIT_TIME) sum_USER_IO_WAIT_TIME,sum(CLUSTER_WAIT_TIME) sum_CLUSTER_WAIT_TIME,sum(CONCURRENCY_WAIT_TIME) sum_CONCURRENCY_WAIT_TIME,
sum(PHYSICAL_READ_BYTES) sum_PHYSICAL_READ_BYTES,
sum(BUFFER_GETS) sum_BUFFER_GETS,
sum(DISK_READS) sum_DISK_READS,
sum(PHYSICAL_WRITE_BYTES) sum_PHYSICAL_WRITE_BYTES
from gv$sql ttt  
where 1=1
and LAST_ACTIVE_TIME >= sysdate - 1
and sql_id in ('8x39su864t49r','797wfyuqmfcud')   -- 替换参数
--and plan_hash_value!=0
group by ttt.EXACT_MATCHING_SIGNATURE,sql_id,plan_hash_value
) s
union all
select (select instance_name from v$instance where rownum=1) instance_name,
'awr_30d' mem_awr_flag,sql_id,plan_hash_value,round(sum_ELAPSED_TIME/1000000/total_exec_num,3) avg_exe_sec,sum_EXECUTIONS,round(sum_ROWS_PROCESSED/total_exec_num) avg_ROWS_PROCESSED,
--sum_ELAPSED_TIME,EXACT_MATCHING_SIGNATURE,sum(sum_ELAPSED_TIME) over(partition by sql_id) sql_total_ELAPSED_TIME,
(select count(1) from dba_sql_profiles p where p.signature=s.EXACT_MATCHING_SIGNATURE) sql_profile,
(select count(1) from dba_sql_plan_baselines b where b.ENABLED='YES' and b.ACCEPTED='YES' and b.signature=s.EXACT_MATCHING_SIGNATURE) sql_plan_baseline,
(select count(1) from dba_sql_patches p where p.STATUS='ENABLED' and p.signature=s.EXACT_MATCHING_SIGNATURE) sql_patch,
round(sum_CPU_TIME/total_exec_num) avg_CPU_TIME,
round(sum_USER_IO_WAIT_TIME/total_exec_num) avg_USER_IO_WAIT_TIME,
round(sum_CLUSTER_WAIT_TIME/total_exec_num) avg_CLUSTER_WAIT_TIME,
round(sum_CONCURRENCY_WAIT_TIME/total_exec_num) avg_CONCURRENCY_WAIT_TIME,
round(sum_PHYSICAL_READ_BYTES/total_exec_num) avg_PHYSICAL_READ_BYTES,
round(sum_BUFFER_GETS/total_exec_num) avg_BUFFER_GETS,
round(sum_DISK_READS/total_exec_num) avg_DISK_READS,
round(sum_PHYSICAL_WRITE_BYTES/total_exec_num) avg_PHYSICAL_WRITE_BYTES,
-- SQL_TEXT
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') rpt_time 
from (
select 
--(select dbms_sqltune.sqltext_to_signature(s.sql_text) from dba_hist_sqltext s where s.sql_id = ttt.sql_id and rownum=1) EXACT_MATCHING_SIGNATURE,
FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE,
sql_id,plan_hash_value,
--SQL_TEXT,
sum(EXECUTIONS_DELTA) sum_EXECUTIONS,(case when sum(EXECUTIONS_DELTA) = 0 then 1 else nvl(sum(EXECUTIONS_DELTA),1) end) total_exec_num,
sum(ROWS_PROCESSED_DELTA) sum_ROWS_PROCESSED,
sum(ELAPSED_TIME_DELTA) sum_ELAPSED_TIME,sum(CPU_TIME_DELTA) sum_CPU_TIME,sum(IOWAIT_DELTA) sum_USER_IO_WAIT_TIME,sum(CLWAIT_DELTA) sum_CLUSTER_WAIT_TIME,sum(CCWAIT_DELTA) sum_CONCURRENCY_WAIT_TIME,
sum(PHYSICAL_READ_BYTES_DELTA) sum_PHYSICAL_READ_BYTES,
sum(BUFFER_GETS_DELTA) sum_BUFFER_GETS,
sum(DISK_READS_DELTA) sum_DISK_READS,
sum(PHYSICAL_WRITE_BYTES_DELTA) sum_PHYSICAL_WRITE_BYTES
from
(
select
dhs.begint,dhs.endt,SNAP_ID,DBID,INSTANCE_NUMBER,t.FORCE_MATCHING_SIGNATURE,
-- (select i.instance_name from gv\$instance i where i.INSTANCE_NUMBER=t.INSTANCE_NUMBER) instance_name,
SQL_ID,PLAN_HASH_VALUE,SQL_PROFILE,
 (select s.SQL_TEXT from dba_hist_sqltext s where s.SQL_ID=t.sql_id and rownum=1) sql_text,
EXECUTIONS_DELTA,FETCHES_DELTA,ROWS_PROCESSED_DELTA,
ELAPSED_TIME_DELTA,CPU_TIME_DELTA,IOWAIT_DELTA,CLWAIT_DELTA,CCWAIT_DELTA,
PHYSICAL_READ_BYTES_DELTA,BUFFER_GETS_DELTA,DISK_READS_DELTA,PHYSICAL_WRITE_BYTES_DELTA,
sysdate tim
from dba_hist_sqlstat t,
( 
SELECT min(dhs.snap_id) begin_snap_id,max(dhs.SNAP_ID) end_snap_id,
 min(to_char(dhs.BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss')) begint,
 max(to_char(dhs.END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss')) endt
  FROM DBA_HIST_SNAPSHOT dhs
 WHERE dhs.BEGIN_INTERVAL_TIME >=sysdate - 30
   AND dhs.END_INTERVAL_TIME <=sysdate
) dhs
where 1=1
--and t.PLAN_HASH_VALUE!=0
and t.snap_id between dhs.begin_snap_id and dhs.end_snap_id
and t.SQL_ID in ('8x39su864t49r','797wfyuqmfcud')  -- 替换参数
order by t.snap_id desc
) ttt
group by FORCE_MATCHING_SIGNATURE,sql_id,plan_hash_value
having sum(EXECUTIONS_DELTA) > 1
--order by exec_sec desc,sql_id
) s
order by --sql_total_ELAPSED_TIME desc,
sql_id,avg_exe_sec;
复制

2）如果计划还在内存中，绑定SPM

--如果执行成功后没有在baseline表查到，那么请注意执行节点的内存中是否有该sql执行计划，具体在哪个实例的内存中有该执行计划需要通过 sql_id,plan_hash_value 去 gv$sql 中查询 inst_id
-- 如果库中已经存在相同信息（sql标识和plan_hash_value）的baseline了，重新导入不会产生多个，只会做更新
DECLARE 
  TEMP VARCHAR(2000);
BEGIN
  TEMP :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'gbps2stbdkdqm',PLAN_HASH_VALUE=>'1249762277');
  DBMS_OUTPUT.PUT_LINE(TEMP);
END;
/
复制

3)如果该计划不在内存中，在历史中，绑定SPM

declare
pls number;
begin
   pls := dbms_spm.load_plans_from_awr(begin_snap =>49538 ,end_snap =>49539 ,basic_filter => 'sql_id=''74cp1hm4p3f6f'' and plan_hash_value=1631529782');
end;
/
select * from dba_hist_sqlstat t
where sql_id='74cp1hm4p3f6f' and plan_hash_value=1631529782
order by t.SNAP_ID desc;
复制

2、批量导入SPM到多个机构库
需要自己在一个库绑定好的SPM，然后按步骤执行脚本，一键导入其他机构库
步骤见附件 批量异库导入SPM
3、绑定或导入SPM后需要检查当前执行SQL是否已经走了新的执行计划及执行效率

-- 抓取正在执行中的SQL的PLAN及效率，可用于固化后查看效果，观察执行次数看有没有增长
select (select instance_name from v$instance where rownum=1) instance_name,
'mem_1d' mem_awr_flag,sql_id,plan_hash_value,round(sum_ELAPSED_TIME/1000000/total_exec_num,3) avg_exe_sec,sum_EXECUTIONS,round(sum_ROWS_PROCESSED/total_exec_num) avg_ROWS_PROCESSED,
--sum_ELAPSED_TIME,EXACT_MATCHING_SIGNATURE,
(select count(1) from dba_sql_profiles p where p.signature=s.EXACT_MATCHING_SIGNATURE) sql_profile,
(select count(1) from dba_sql_plan_baselines b where b.ENABLED='YES' and b.ACCEPTED='YES' and b.signature=s.EXACT_MATCHING_SIGNATURE) sql_plan_baseline,
(select count(1) from dba_sql_patches p where p.STATUS='ENABLED' and p.signature=s.EXACT_MATCHING_SIGNATURE) sql_patch,
round(sum_CPU_TIME/total_exec_num) avg_CPU_TIME,
round(sum_USER_IO_WAIT_TIME/total_exec_num) avg_USER_IO_WAIT_TIME,
round(sum_CLUSTER_WAIT_TIME/total_exec_num) avg_CLUSTER_WAIT_TIME,
round(sum_CONCURRENCY_WAIT_TIME/total_exec_num) avg_CONCURRENCY_WAIT_TIME,
round(sum_PHYSICAL_READ_BYTES/total_exec_num) avg_PHYSICAL_READ_BYTES,
round(sum_BUFFER_GETS/total_exec_num) avg_BUFFER_GETS,
round(sum_DISK_READS/total_exec_num) avg_DISK_READS,
round(sum_PHYSICAL_WRITE_BYTES/total_exec_num) avg_PHYSICAL_WRITE_BYTES,
-- SQL_TEXT
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') rpt_time
from 
(select ttt.EXACT_MATCHING_SIGNATURE,sql_id,plan_hash_value,max(SQL_TEXT) SQL_TEXT,sum(EXECUTIONS) sum_EXECUTIONS,(case when sum(EXECUTIONS) = 0 then 1 else sum(EXECUTIONS) end) total_exec_num,
sum(ROWS_PROCESSED) sum_ROWS_PROCESSED,
sum(ELAPSED_TIME) sum_ELAPSED_TIME,sum(CPU_TIME) sum_CPU_TIME,sum(USER_IO_WAIT_TIME) sum_USER_IO_WAIT_TIME,sum(CLUSTER_WAIT_TIME) sum_CLUSTER_WAIT_TIME,sum(CONCURRENCY_WAIT_TIME) sum_CONCURRENCY_WAIT_TIME,
sum(PHYSICAL_READ_BYTES) sum_PHYSICAL_READ_BYTES,
sum(BUFFER_GETS) sum_BUFFER_GETS,
sum(DISK_READS) sum_DISK_READS,
sum(PHYSICAL_WRITE_BYTES) sum_PHYSICAL_WRITE_BYTES
from gv$sql ttt 
where 1=1
and LAST_ACTIVE_TIME >= sysdate - 5/24/3600
/* LAST_ACTIVE_TIME会一直刷新不管SQL是否卡住 最后刷新时间距今5s内可以认为是EXECUTING状态，要想取最近一天的也可以改为 3600*24*/
and sql_id in ('cy57cc0s3n0nb','0dxyfbg8f00qc') -- 替换变量
--and plan_hash_value!=0
group by ttt.EXACT_MATCHING_SIGNATURE,sql_id,plan_hash_value
) s;
复制

如果是高频的SQL那么直接观察即可，如果是低频的SQL找业务触发，如果业务无法触发，且如果是查询那么自行构造执行，方法如下。

构造执行
1）获取之前某次执行的绑定变量值
若在内存中
（where条件值都能从gv$sql、gv$sql_monitor中获取）
select 
sbc.SQL_ID,sbc.NAME,sbc.POSITION,sbc.DATATYPE_STRING,sbc.MAX_LENGTH,sbc.LAST_CAPTURED,sbc.VALUE_STRING,
(case when sbc.VALUE_STRING='NULL' then null 
when sbc.DATATYPE_STRING like '%CHAR%' then ''''||sbc.VALUE_STRING||''''
when sbc.DATATYPE_STRING = 'NUMBER' then 'to_number('||sbc.VALUE_STRING||')' 
when sbc.DATATYPE_STRING = 'DATE' then 'to_date('''||sbc.VALUE_STRING||''',''mm/dd/yyyy hh24:mi:ss'')'
else sbc.VALUE_STRING end) kv
 from gv$sql_bind_capture sbc
where sbc.SQL_ID='g65khmhbdcp9d' 
and sbc.INST_ID = 1
and sbc.HASH_VALUE='383145261'
-- and sbc.ADDRESS='00000019BE33EC58'  
and sbc.CHILD_ADDRESS='00000017D487D3E8';

若在执行历史中
select  
SQL_ID,NAME,POSITION,DATATYPE_STRING,MAX_LENGTH,LAST_CAPTURED,VALUE_STRING,
 (case when sd.VALUE_STRING='NULL' then null 
when sd.DATATYPE_STRING like '%CHAR%' then ''''||sd.VALUE_STRING||''''
when sd.DATATYPE_STRING = 'NUMBER' then 'to_number('||sd.VALUE_STRING||')' 
when sd.DATATYPE_STRING = 'DATE' then 'to_date('''||sd.VALUE_STRING||''',''mm/dd/yyyy hh24:mi:ss'')'
else sd.VALUE_STRING end) kv
  from dba_hist_sqlbind sd
 where 1=1 
 and sd.SNAP_ID = 111547
 and sd.sql_id = 'g65khmhbdcp9d'
 and sd.instance_number=1
order by sd.POSITION ;

2）构造执行
execute immediate sql_text_c USING 的参数值来源上上一步的结果，另外如果下面PLSQL报错说是对象不存在，那么说明当前执行用户和SQL中的对象用户不一致或没权限，可以通过以下SQL处理不一致的情况 alter session set current_schema=KD_SALE_DX;

declare
sql_text_c CLOB;
v_cnt number;
Begin
  -- sql in cursor
  select count(1) into v_cnt from gv$sql t where t.sql_id = '54zfabgu9w056' and rownum=1;
  if v_cnt >0 then
  select sql_fulltext into sql_text_c from gv$sql t where t.sql_id = '54zfabgu9w056' and rownum=1;
  else
  -- sql not in cursor
  SELECT sql_text into sql_text_c FROM DBA_HIST_SQLTEXT DHST WHERE DHST.SQL_ID='54zfabgu9w056'; 
 end if;

  execute immediate sql_text_c USING 
  'C11361225096',
to_number(200),
to_date('05/18/2021 00:00:00','mm/dd/yyyy hh24:mi:ss'),
to_date('06/18/2021 00:00:00','mm/dd/yyyy hh24:mi:ss')
  ;
end;
/
复制

4、如果绑定SPM后仍没有走新的执行计划，可以尝试purge，然后观察是否走新的计划

-- 处理脚本 purge_cursor和purge_plan 都可以执行，等执行完后过一段时间就能起作用
---------------注意sys.dbms_shared_pool.purge只能处理当前实例内存中的cursor-----------------
-----------------------------purge_cursor
set linesize 1000
set long 2000000000
col PURGE_CURSOR for a200
col purge_plan for a200

select 'begin'||chr(10)|| xmlagg(xmlparse(content purge_cursor||chr(10) wellformed) order by 1).getclobval() || chr(10)||'end;'||chr(10)||'/' as purge_cursor
from
(
select sql_id,child_number,plan_hash_value,substr(sql_text,1,100) sql_text,executions,parse_calls,sql_plan_baseline
,' sys.dbms_shared_pool.purge('''||address||','||hash_value||''',''C'',1);' purge_cursor ,
'sys.dbms_shared_pool.purge('''||address||','||hash_value||''',''C'',64);' purge_plan 
from gv$sql 
where sql_id='gbps2stbdkdqm'  -- 替换变量
and plan_hash_value != 1249762277 -- 替换变量
);

-----------------------------purge_plan
set linesize 1000
set long 2000000000
col PURGE_CURSOR for a200
col purge_plan for a200

select 'begin'||chr(10)|| xmlagg(xmlparse(content purge_plan||chr(10) wellformed) order by 1).getclobval() || chr(10)||'end;'||chr(10)||'/' as purge_plan
from
(
select sql_id,child_number,plan_hash_value,substr(sql_text,1,100) sql_text,executions,parse_calls,sql_plan_baseline
,' sys.dbms_shared_pool.purge('''||address||','||hash_value||''',''C'',1);' purge_cursor ,
'sys.dbms_shared_pool.purge('''||address||','||hash_value||''',''C'',64);' purge_plan 
from gv$sql 
where sql_id='gbps2stbdkdqm'  -- 替换变量
and plan_hash_value != 1249762277 -- 替换变量
);

复制

5、如果以上都无效，那么请绑定SQL PROFILE
优先使用coe_xfr_sql_profile.sql脚本进行绑定，这个脚本是MOS上的，比较完善。
也可以用 同库一键化绑定sqlprofile.txt 同库异库文本法绑定sqlprofile.txt 这个是我写的，用起来更方便灵活但是可能有bug。

6、查询SQL PROFILE

SELECT *  -- name,sql_text,status,created,last_modified
FROM dba_sql_profiles
WHERE signature IN (
  SELECT exact_matching_signature FROM gv$sql WHERE sql_id='c65dr02yz2vxv'
);
或者
select *
from dba_sql_profiles s
where s.signature in 
(select dbms_sqltune.sqltext_to_signature(sql_text)
from dba_hist_sqltext
where sql_id = '2t03uwzspksvs')
order by s.created desc;

复制

7、查询SPM

SELECT *  -- sql_handle, plan_name
FROM dba_sql_plan_baselines
WHERE signature IN (
  SELECT exact_matching_signature FROM gv$sql WHERE sql_id='c65dr02yz2vxv'
);
或者
SELECT *  -- sql_handle, plan_name
FROM dba_sql_plan_baselines
WHERE signature IN   
(select dbms_sqltune.sqltext_to_signature(sql_text)
from dba_hist_sqltext
where sql_id = '2t03uwzspksvs');

复制

8、获取执行计划

-- 查看内存中的 SQL 的执行计划
select * from table(dbms_xplan.display_cursor(sql_id=>'6jfrr5qfw2mxt',cursor_child_no => 0,format => 'advanced')); 
 -- cursor_child_no 是 gv$sql.child_number
--历史执行计划
select * from table(dbms_xplan.display_awr(sql_id => '6jfrr5qfw2mxt',plan_hash_value => '2513919667',format => 'advanced'));
--获取 baseline 的执行计划
select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SQL_PLAN_gjdf359pwduqf7cdf74c6',format=>'basic'));

复制

9、SQL PROFILE管理

1)删除SQL PROFILE
exec DBMS_SQLTUNE.DROP_SQL_PROFILE (   name =>'spf_6jfrr5qfw2mxt_2513919667');

2)导出sql profile
2.1）
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
   table_name            IN VARCHAR2,                    -- 'SQLPROF_20211014'
   schema_name           IN VARCHAR2 := NULL,   -- 'DBMGR'
   tablespace_name       IN VARCHAR2 := NULL);
2.2）
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
   profile_name          IN VARCHAR2 := '%',
   profile_category      IN VARCHAR2 := 'DEFAULT',
   staging_table_name    IN VARCHAR2,  -- 'SQLPROF_20211014'
   staging_schema_owner  IN VARCHAR2 := NULL);  -- 'DBMGR'
2.3）
exp导出表DBMGR.SQLPROF_20211014

3)导入sql profile
3.1）imp导入表DBMGR.SQLPROF_20211014
3.2）
BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
      replace            => TRUE
  ,   staging_table_name => 'SQLPROF_20211014',
staging_schema_owner => 'DBMGR'
);
END;

DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
   profile_name          IN VARCHAR2 := '%',
   profile_category      IN VARCHAR2 := 'DEFAULT',
   replace               IN BOOLEAN,
   staging_table_name    IN VARCHAR2,
   staging_schema_owner  IN VARCHAR2 := NULL);

复制

10、SPM管理

1）删除SPM
DECLARE 
  TEMP VARCHAR(2000);
BEGIN
  TEMP :=DBMS_SPM.DROP_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_8401224F1686809B',PLAN_NAME=>'SQL_PLAN_880929WB8D04V24C6DBB6');
DBMS_OUTPUT.PUT_LINE(TEMP);
END;

2）修改SPM
DECLARE 
  TEMP VARCHAR(2000);
BEGIN
  TEMP :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_612d7e9c1a30c15c',PLAN_NAME=>'SQL_PLAN_62bbymhd31haw75c98d9d',ATTRIBUTE_NAME=>'ENABLED',ATTRIBUTE_VALUE=>'NO');
DBMS_OUTPUT.PUT_LINE(TEMP);
END; 

Fixed   “YES”意味着SQL计划基线不会随着时间的推移而变化。固定的计划优先于不固定的计划

3）SPM演变    
（对比不可接受和可接受的基线，发现不可接受的基线效率更高，则将其改为可接受，verify=no 表示不执行只是修改为可接受）
DECLARE 
  TEMP VARCHAR(2000);
BEGIN
  TEMP :=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_8401224F1686809B',PLAN_NAME=>NULL,VERIFY=>'NO',COMMIT=>'YES');
DBMS_OUTPUT.PUT_LINE(TEMP);
END;

4）导出SPM
BEGIN
 DBMS_SPM.CREATE_STGTAB_BASELINE(table_name=>'SPM_20211124173500',table_owner=> 'DBMGR',tablespace_name => 'USERS');
   END;
   /
DECLARE
   l_plans_packed  PLS_INTEGER;
   CURSOR spm_cursor IS select sql_handle,plan_name from dba_sql_plan_baselines  where PLAN_NAME IN ('SQL_PLAN_66bbf4kgd57kte71130b9');
   BEGIN
     FOR v_spm_record IN spm_cursor LOOP
       l_plans_packed := DBMS_SPM.pack_stgtab_baseline(table_name      =>'SPM_20211124173500',   table_owner     => 'DBMGR',   sql_handle =>v_spm_record.sql_handle,   plan_name =>v_spm_record.plan_name);  
        END LOOP;   
        DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
   end;
   /
exp dbmgr/"xxxx"@lucs01.db.paic.com.cn:1528/lucs0 file=SPM_20211124173500.dmp tables=DBMGR.SPM_20211124173500

5）导入SPM
imp dbmgr/"XXXX"@lucd01.db.paic.com.cn:1528/lucd0 file=SPM_20211124173500.dmp fromuser=DBMGR touser=DBMGR
DECLARE
    l_plans_packed  PLS_INTEGER;
    BEGIN
    l_plans_packed := DBMS_SPM.unpack_stgtab_baseline(
       table_name      =>'SPM_20211124173500',
       table_owner     => 'DBMGR');
       DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
    end;
    /
select b.creator, b.PLAN_NAME,b.created,b.last_executed,b.ENABLED,b.ACCEPTED,b.FIXED,b.executions,b.elapsed_time,b.parsing_schema_name,b.last_modified
         from dba_sql_plan_baselines b where  b.signature in (select to_char(s.EXACT_MATCHING_SIGNATURE) from gv$sql s where sql_id='dcdcw5d0jx7vq' and rownum=1)
    order by b.last_modified desc;
复制