Oracle SQL Profile及SPM使用

IT数据库行业小学生，记录日常工作中数据库知识及一些故障案例，如有不对请指正，欢迎关注小编，小编微信xh870545795,CSDN:dba_notes

---

一、区别

       我们知道oracle稳定执行计划有两种方式，一种是使用SQL profile（oracle 10g以前通常使用这种方式），还有一种是SPM(SQL Plan Management,oracle 11g推出的新功能)，两者的主要区别是什么呢？

      通俗来讲，由于某些原因，比如统计信息陈旧导致优化器对执行计划的错误选择，导致sql的执行计划发生变更，此时可以通过创建SQL Profile来纠正、稳定这些sql的执行计划。但是通过SQL Profile固定执行计划后，依然不能保证系统后续执行的sql的执行计划就不再发生不好的变更。如果绑定的执行计划在后续不再适用此sql，那么依然会产生性能问题。因此在oracle 11g 推出了SPM,通俗来讲，SPM绑定执行计划后，会以此执行计划作为基线，当sql的执行计划产生变更，如果我们验证了变更的执行计划效率高于基线，那么才会启用此执行计划。

二、Sql Profile实验

实验环境为oracle 11.2.0.1.0 WINDOWS

1.创建测试表并插入数据

create table xh_test as select * from dba_objects;
insert into xh_test select * from xh_test;--尽量多插入些数据

2.查看当前查询语句的执行计划

测试语句：select * from xh_test where owner='PUBLIC';

查看执行计划：

SQL> set timing on;
SQL> set autotrace traceonly;
SQL> select * from xh_test where owner='PUBLIC';

3.创建索引、收集统计信息并查看计划

SQL> create index xh_test_owner_idx on xh_test(owner);
SQL> execute dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname =>'XH_TEST',estimate_percent => 100,degree => 4,cascade => true, method_opt => 'for all columns size repeat');

可见，建完索引，oracle优化器选择走索引，但速度并没有走全表的快，是因为owner列数据重复率高，索引扫描速度还没全表快。

4.使用Sql Profile绑定执行计划

4.1 使用hint方式强制走全表扫

select /*+ full(xh_test)*/* from xh_test where owner='PUBLIC';

4.2 绑定走全表扫的执行计划

4.2.1 查询sql_id 及PLAN_HASH_VALUE

select sql_text,sql_id,PLAN_HASH_VALUE from v$sql where sql_text like '%from xh_test where owner%';

通过上面的执行计划可得走全表扫的PLAN_HASH_VALUE为2771665069，对应的sql_id如上图所示为5km454qb6pvxq，走索引的PLAN_HASH_VALUE为1865429603，对应的sql_id如上图所示为akanjz3tzph8y

4.2.2绑定全表扫的执行计划到sql_id为akanjz3tzph8y的sql上

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
select extractvalue(value(d), '/hint') as outline_hints
bulk collect into ar_profile_hints
from xmltable('/ */outline_data/hint' passing
(select xmltype(other_xml) as xmlval
from v$sql_plan
where sql_id = '5km454qb6pvxq'    --执行效率高的sql_id（sql_id可以和下面的相同,因为有存在同样的sql_id有不同的执行计划的情况）
and PLAN_HASH_VALUE = 2771665069 --执行效率高的的sql_id hash value
and rownum=1
and other_xml is not null)) d;
select sql_fulltext
into cl_sql_text
from v$sql
where sql_id = 'akanjz3tzph8y'     --原始SQL（效率差）的sql_id
and rownum=1
and PLAN_HASH_VALUE = 1865429603;   --原始SQL（效率差）的hash value
dbms_sqltune.import_sql_profile(sql_text => cl_sql_text,
profile => ar_profile_hints,
name => 'profile_akanjz3tzph8y_sql', ---自定义名称，不能和现有的冲突
force_match => true,
replace => true);
end;
/

验证：

4.2.3 其他操作

查看绑定sql_profile

select * from dba_sql_profiles;

删除Sql Profile

begin
dbms_sqltune.drop_sql_profile(
name => 'profile_akanjz3tzph8y_sql'); ---自定义名称，不能和现有的冲突
end;
/

三、SPM实验

3.1模拟一条sql语句多个执行计划

测试sql和上面一样，清除表上的Sql Profile,方法如上

select * from xh_test where owner='PUBLIC';

修改表上索引的聚簇因子,目的是为了能让SQL的执行计划变为对表xh_test的全表扫描(为何修改聚簇因子，参考http://www.linuxidc.com/Linux/2017-02/141071.htm )。修改完后再执行上述SQL，并查看执行计划：

SQL> exec dbms_stats.set_index_stats(ownname=>'SCOTT',indname=>'XH_TEST_OWNER_IDX',clstfct=>2400000,no_invalidate=>false);
PL/SQL 过程已成功完成。
SQL> select index_name,clustering_factor from dba_indexes where index_name='XH_TEST_OWNER_IDX';
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
XH_TEST_OWNER_IDX                        2400000

此时走了全表扫描，查看对应的sql_id和plan hash value

select sql_text,sql_id,PLAN_HASH_VALUE from v$sql where sql_text like '%from xh_test where owner%';

SELECT sql_id,plan_hash_value,operation,options FROM v$sql_plan WHERE sql_id='akanjz3tzph8y';

3.2 绑定SPM

将认为好的执行计划装载到执行计划管理器（创建基线）：

variable retrst number;
exec :retrst:=dbms_spm.load_plans_from_cursor_cache(sql_id => 'akanjz3tzph8y',plan_hash_value => 2771665069);

查询基线:

select sql_handle, plan_name,ENABLED,ACCEPTED, origin,SQL_TEXT from dba_sql_plan_baselines;

enabled和accepted同为yes，对应的执行计划才会被启用，同样我们可以把走索引的执行计划也装载到执行计划管理器中然后不起用：

exec :retrst:=dbms_spm.load_plans_from_cursor_cache(sql_id => 'akanjz3tzph8y',plan_hash_value => 1865429603);

注意，所有手工生成的SQL Plan Baseline的ENABLED和ACCEPTED的值均为YES

设置ENABLED的值设为NO

exec :retrst:= dbms_spm.alter_sql_plan_baseline(sql_handle=>'SYS_SQL_32a0a9efb3dd9b5b',plan_name=>'SQL_PLAN_35859xytxv6uv90f15403',attribute_name=>'enabled',attribute_value=>'NO');

查看SQL装载到执行计划管理器的执行计划:

select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SQL_PLAN_35859xytxv6uv4d4d47db',format=>'basic'));

再次查看执行计划，显示SPM已经启用，将索引的聚簇因子修改回来仍然走全表扫。

3.3 自动捕获SQL Plan Baseline

       参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES用于控制是否开启自动捕获SQL Plan Baseline，其默认值为FALSE，表示在默认情况下，Oracle并不会自动捕获SQL Plan Baseline。这个参数可以在session或系统级别动态修改。当修改为TRUE后，则Oracle会对上述参数影响范围内所有重复执行的SQL自动捕获其SQL Plan Baseline，并且针对目标SQL第一次捕获的SQL Plan Baseline的ENABLED和ACCEPTED的值均为“YES”。随后如果该SQL的执行计划发生了变更，则再次捕获到的SQL Plan Baseline的ENABLED的值依然为YES，但ACCEPTED的值变为了NO，这表示后续变更的执行计划虽然被捕获了，但Oracle不会将其作为该SQL的执行计划来执行，即此时Oracle会永远沿用该SQL第一次被捕获的SQL Plan Baseline所对应的执行计划(除非后续做了手工调整)。

       参数OPTIMIZER_USE_SQL_PLAN_BASELINES用于控制是否启用SQL Plan Baseline，其默认值为TRUE，表示在默认情况下，Oracle在生成执行计划时就会启用SPM，使用已有的SQL Plan Baseline，这个参数也可以在session或系统级别动态修改。

 3.4 其他操作命令

SPM删除：

variable retrst number;
exec :retrst:=dbms_spm.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_097094edc8b9486b',plan_name=>'SQL_PLAN_0kw4nxr4bkk3b2a45d6aa');

设置对应的SQL Plan Baseline的ACCEPTED值设为YES：

var temp varchar2(1000);
exec :temp:= dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_356e77773103ae41',plan_name=>'SQL_PLAN_3avmrfwsh7bk19a1f256e',verify=>'NO',commit=>'YES');

   本次就分享到这里，如有不对，请指正，谢谢！