一个执行计划异常变更的案例 – 外传之SQL Profile(上)

之前的几篇文章：
《一个执行计划异常变更的案例 - 前传》
《一个执行计划异常变更的案例 - 外传之绑定变量窥探》
《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》
《一个执行计划异常变更的案例 - 外传之rolling invalidation》
《一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)》
《一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法》
《一个执行计划异常变更的案例 - 外传之AWR》
《一个执行计划异常变更的案例 - 外传之ASH》
《一个执行计划异常变更的案例 - 外传之SQL AWR》
《一个执行计划异常变更的案例 - 外传之直方图》

这个系列文章已经连载了多篇，节后这几天工作生活都比较忙，只能利用碎片时间继续，接下来的两篇博文都是关于SQL Profile的，然后就可以进入正传了:)

首先，老熊的两篇SQL Profile的博文，以及dbsnake书中第二章关于SQL Profile的介绍，是我认为中文介绍SQL Profile最好的两个学习来源，本文中的一些观点和案例会从中借鉴一些。

从MOS上我们看下什么是SQL Profile，

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans..

SQL Profile是一组存储在数据字典中的信息，目的就是为了创建最优的SQL执行计划，通过使用自动SQL调优工具，提高优化器对cardinality和selectivity的预估，以纠正错误的执行计划。

说白了，SQL Profile是一组数据字典信息，可以提高优化器对cardinality和selectivity的预估进而影响执行计划的成本计算，达到选择正确执行计划的目的。

SQL Profile是从10g开始引入的，SQL Profile相比于Outline的优点：
1.SQL Profiles更容易生成、更改和控制。
2.SQL Profiles在对SQL语句的支持上做得更好，也就是适用范围更广。

使用SQL Profiles的两个目的：
1.锁定或者说是稳定执行计划。
2.在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。

SQL Profile有两种类型，一种是Automatic类型，另一种是Manual类型，本篇文章会介绍前者，下一篇会介绍后者。

创建测试表，t1表10000条记录，t2表50000条记录。

t2表的id列创建非唯一索引，收集t1和t2表和索引的统计信息，不收集直方图，

执行这条SQL语句，Oracle选择了两表全表扫描，再做Hash Join的执行计划，逻辑读221，

实际t1表符合t1.name like ‘%ABC%’条件的记录只有3条，优化器预估则是500条，即500/10000*100%=5%，应该是Oracle的默认选择率，但关于这点，略有疑问，按照MOS(68992.1)的说法

无直方图情况下，选择率计算方法如下：  c1 = ‘4076’              1/NDV    c1 ‘4076’              1 - (High - Value / High - Low)    c1 = ‘4076’             1 - (High - Value / High - Low) + 1/NDV  c1 like ‘4076’           1/NDV

有绑定变量情况下，选择率计算方法如下：  c1 =    :bind1           1/NDV  c1    :bind1           Default of 5%  c1 =   :bind1           Default of 5%  c1 like :bind1           Default of 25%

这有一个清晰的总结(http://www.ordba.net/Articles/PredicateSel.htm)：

如果按照这个说法，上面SQL未使用绑定变量，没有直方图，like选择率应该是1/NDV，这里就是1/10000*100%=0.01%，11g的库，可能还是我什么地方没有理解正确，还请各位指教。

因为实际t1使用like的结果集很小，一个大表和一个小的结果集关联，而且大表关联字段有索引，比较适合于nested loop连接。我们看下使用这种连接的执行计划，

使用use_nl和index两个hint，强制SQL使用t2表的索引检索，并让t1和t2做nested loop连接，成本值确实要大于上面HASH JOIN连接的成本，但看其逻辑读只有40，远小于Hash Join的221，再看t1表的返回行数预估是500，远高于实际like过滤后其实际的结果集3，因此会对nested loop连接产生了一个过高的成本预估，导致优化器选择了Hash Join的执行计划，所以这条SQL目前的执行计划并不是最优的，不改动SQL，如何才能correct这条SQL的执行计划？

接下来我们用STA(SQL Tuning Advisor)查找是否有更好的执行计划，主要会用到dbms_sqltune包，相应方法的参数很多，这里举出两种用法，

方法1：利用sql id来做STA，
首先查找要纠正的SQL对应的sql id值，

接下来执行如下PLSQL，

可以看出这次STA执行的任务名称是TASK_4516。使用dbms_sqltune.report_tuning_task来查看执行结果，

方法2：使用SQL文本来做STA，
由于SQL原文中有引号字符，直接使用会报错，

使用q’[…]’，使用create_tuning_task更多的参数，命名这次任务为my_sql_tuning_task，

以上使用了sql id和SQL原文两种方法创建STA任务，其实create_tuning_task还支持很多种参数，

无论什么方法，结果是一样的，都可以直接执行结果集指出的存储过程，第一条是方法一的存储过程，第二条是方法二的存储过程，

唯一不同的是都增加了force_match=true的参数，这个参数类似于cursor_sharing，true则会将文本值自动转换为绑定变量(不包括文本值和绑定变量混用的SQL)，目的就是可以重用SQL Profile，

接着我们再来执行上面的SQL语句，

发现此时选择的就是之前使用hint得到的执行计划，即t2表索引扫描t2表，t1和t2表使用nested loop连接。

从10053的trace可以看出对t1表的返回行数预估做了修正，从500调整为3，

另外，需要注意一下执行计划中，就是在下面Note部分提示，

SQL profile “SYS_SQLPROF_015a06ed92930000” used for this statement

说明这条SQL使用了名称为SYS_SQLPROF_015a06ed92930000的SQL PROFILE，查询DBA_SQL_PROFILES视图可以得到其他信息，

这里只是指出了SQL Profile的一些属性，SQL Profile究竟是什么东东？老熊的文章中曾经指出可以用sys.sqlprof$attr数据字典查询其定义，但只有10g才有定义这个数据字典，11g下没有这个名称的数据字典，这块在网上(MOS、百度)搜了半天也没找到任何线索，后来经野花总指点，从bing上搜到了非常对应的线索，可以对比下百度和bing使用相同关键词进行搜索的结果，只能说… 

1.百度搜索， 

 

2.bing搜索(建议仅英文)，

11g下，以下这两个数据字典接管了sys.sqlprof$attr的信息了，

sys.sqlobj$

sys.sqlobj$data

使用如下SQL，

或者使用这条更精妙的SQL，

可以看出这儿有一个词(这和10g中sys.sqlprof$attr的ATTR_VAL等价)：SCALE_ROWS=0.006。

他的含义是针对T1表评估返回行数与原始评估返回行数的放大缩小倍数是0.006。这条SQL使用nested loop执行计划中T1表原始其预估行数是500，计算500*0.006=3，这就是为什么10053的trace以及执行计划中表t1的预估行数是3的原因，我们没有对SQL做任何一些改动，而是让优化器得到一些更准确的信息，以让其选择正确的执行计划，amazing？

但这有一个问题，就是其并未锁定执行计划，我们强制让Oracle知道t1表有500万记录(实际并未如此)，

此时再次执行SQL，发现依旧使用了SQL Profile，但执行计划变为了全表扫描后做Hash Join，

t1表预估行数是1500，计算方式是50000000.0065%=1500。说明虽然使用了SQL Profile，但执行计划并未锁定，因此对于一些统计信息可能会有较大变化的表来说，使用这种Automatic类型的SQL Profile还是可能有隐患。 

注：5%的默认可选择率，和上面的疑问相同。

总结：
1.本文主要介绍了Automatic类型的SQL Profile创建过程，以及其未改变SQL却会让优化器选择最优执行计划的原理，同时对于一些统计信息可能会有较大变化的表来说，这种类型的SQL Profile未锁定执行计划，因此还是可能有隐患。
2.下篇会介绍Manual类型的SQL Profile，看看他是如何锁定执行计划，解决这种Automatic类型不能解决的问题。
3.对于未使用绑定变量未有直方图的SQL，like谓词对应表其默认可选择率是5%的说法，我还是有疑问，还请各位能指教一二。

如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)