实验目标：
11g环境中针对某条sql语句进行执行计划固定，通过spm及sqlprofile两种方式，并模拟数据迁移到19c环境后同时实现执行计划的不变。
数据模拟
以下操作再11g环境中进行操作，模拟元数据
create user yz11 identified by yz default tablespace users;
grant connect,resource to yz11;
create table yz11.a1 as select * from dba_objects;
create index yz11.a_ind1 on yz11.a1(object_id);
手动修改index的统计信息，使优化器选择错误的执行计划
begin
sys.dbms_stats.set_index_stats(ownname=>'YZ11',
INDNAME=>'A_IND1',
numrows=>'999999999999999999999',
numdist=>1,
indlevel=>8
);
end;
/全表扫描查看（由于index的统计信息不正确，执行计划错误的选择了全表扫描）
全表扫描查看object_id=20 yz11表，查看此时的执行计划
set linesize 500
set termout off
alter session set statistics_level=all; 设置统计信息级别
select object_id,object_name from yz11.a1 a where object_id=20; 执行的sql语句
查看执行计划
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));此时记录如下信息用于后面的spm的绑定
SQL_ID 1cbv8w5134zkt, child number 0
-------------------------------------
select object_id,object_name from yz11.a1 a where object_id=20
Plan hash value: 2676145672
通过hint强制使用索引扫描
set linesize 200
set termout off
alter session set statistics_level=all;
select /*+ index(a,A_IND1) */ object_id,object_name from yz11.a1 a where object_id=20;
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
记录新的sqlid及hash value
SQL_ID gdt647y1wjr1z, child number 0
-------------------------------------
select /*+ index(a,A_IND1) */ object_id,object_name from yz11.a1 a
where object_id=20
Plan hash value: 59315297汇总如下：目标是创建spm测试使sql语句的查询使用快的执行计划。（索引访问）
针对A1表的id=20的查看语句
全表扫描：sqlid:1cbv8w5134zkt hash:2676145672 慢：
索引：sqlid:gdt647y1wjr1z hash:59315297 快：绑定执行计划的格式
--绑定执行计划格式
declare
m_clob clob;
begin
select sql_fullteXt
into m_clob
from v$sql
where sql_id = '-------' --需要绑定的SQL ID 慢sql
and child_number = 0; --需要绑定的SQL ID对应的子游标编号 慢sql
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '-----', --参考需要的执行计划SQL ID 及好的执行计划对应的SQL ID
plan_hash_value => -------, --参考需要的执行计划 Hash value
sql_text => m_clob,
fixed => 'YES',
enabled => 'YES'));
end;
/绑定执行计划
查看当前的baseline信息
select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines;数据库中没有对应的baseline
执行如下sql进行baseline绑定
declare
m_clob clob;
begin
select sql_fulltext
into m_clob
from v$sql
where sql_id = '1cbv8w5134zkt'
and child_number = 0;
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => 'gdt647y1wjr1z',
plan_hash_value => 59315297,
sql_text => m_clob,
fixed => 'YES',
enabled => 'YES'));
end;
/执行计划验证
再次执行sql操作select object_id,object_name from yz11.a1 a where object_id=20
sql语句不再使用全表扫描，即便统计信息不正确的情况下。而是使用了索引访问。
本文参考了《Oracle绑定的执行计划sql_profile_spm迁移-杨卓》的文档，根据文档步骤进行了模拟。