实验目标：

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迁移-杨卓》的文档，根据文档步骤进行了模拟。

Tags：oracle 记录