执行计划：Oracle的Profile特性与SQL执行计划的稳定性

熊军 2016-07-20

968

（题图授权来自Oracle VP , Sally Piao的摄影佳作）

编辑手记：在Oracle数据库中，版本变化带来的一大挑战就是SQL执行计划的稳定性，为此Oracle经历了从Outline到Profile的特性演进，本文带大家一起来了解一下Profile的特性和使用。

SQL Profiles 是 Oracle 10g 引入的一项新特性，并且在11g中被广泛的使用，其核心功能可以说是 Outlines 的进化。Outlines 能够实现的功能 SQL Profiles 也完全能够实现，而 SQL Profiles 具有 Outlines 不具备的优化，个人认为最重要的有2点：

•SQL Profiles 更容易生成、更改和控制；
•SQL Profiles 在对SQL语句的支持上做得更好，也就是适用范围更广；

关于这2方面的优点，我后面会详细地阐述。

现在我在使用 Outlines 的场合，均使用 SQL Profiles 来替代。有一次准备对1条 SQL 语句使用 Outline 进行执行计划的稳定，结果使用 Outline 之后，系统出现大量的 library cache latch 的争用，不得不关闭 Outline 的使用，但是改用 SQL Profiles 不再有这个问题。这或许是个 BUG，不过既然能用 SQL Profiles 代替，也就没再深入去研究这个问题。

使用 SQL Profiles 无非是两个目的：

•锁定或者说是稳定执行计划。
•在不能修改应用中的 SQL 的情况下使 SQL 语句按指定的执行计划运行。

那么 SQL Profile 到底是什么？

在我看来，SQL Profile 就是为某一 SQL 语句提供除了系统统计信息、对象（表和索引等）统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器为 SQL 语句选择更适合的执行计划。这些说法显得比较枯燥，还是来看看下面的测试。

首先建2个测试表：

然后看看下面这一条 SQL：

这里省略了 SELECT 出来的具体数据，但是我们关心的是返回的结果行数、执行计划以及逻辑读这些信息。

首先从执行计划可以看到，这条 SQL 语句在 2 个表上都是全表扫描。在第 1 个表 T1 上，有 like '%T1%' 这样的条件，导致只能全表扫描，这没有问题。但是第 2 个表，也是全表扫描，这里有没有问题呢？或者说是有没有优化的余地，答案显然是肯定的。

这里的问题在于执行计划 ID=1 的那一行，Oracle 优化器评估 T1 like '%T1%' 返回的结果行数为 2500 行，即 T1 表总行数的 5%，如果 2 个表采用 index range scan+nested loop连接，oracle 评估的成本会高于 full table scan+hash join 。下面可以看到Oracle优化器评估的 indexrange_scan+nested loop 的成本：

从执行计划可以看到 Oracle 优化器评估的成本为 5071，远远高于原来的 325 。

但是实际的逻辑读是多少呢？

加了 HINT 之后实际的逻辑读只有 308，低于原始 SQL 的 1186。所以这里可以看出来，由于 Oracle 优化器过高地估计了 T1 表经过 like 操作过滤返回的行数，也就过高地估计了nest loop 的成本，最终也就选择了不是最优的执行计划。

下面我们用 Oracle 的 SQL Tuning Advisor 来尝试这条 SQL：

上面代码中的sql_id是从v$sql来，对应的是没有加hint的SQL。

结果看起来非常棒，SQL Tuning Advisor为我们找到了理想的执行计划，T1表上经过谓词过滤后返回的行数评估为29，相当地精确。我们要做的就是Accept SQL Profile，接受这个SQL Profile。

那么我们再执行其他的类似 SQL 看看：

这一次，尽管我们更改了LIKE 后面的值，但是执行计划与 SQL Tuning Advisor 产生的执行计划完全一样。从执行计划的 "Note“ 一节也可以看到，SQL Profile 起作用了。SQL Profile 的名字为 "SYS_SQLPROF_014b39f084c88000" 。

一些复杂的 SQL，我经常会先通过 SQL Tuning Advisor 来分析一下，看能不能让 Oracle自已找出一个更好的执行计划。

我们来看看，SQL Profiles 实际上是些什么（11g 查询如下几个字典表：SQLOBJ$、SQLOBJ$AUXDATA、SQLOBJ$DATA）：

从 sys.sqlprof$attr 这个数字字典里面，我们可以看到两样东西：signature 和 attr 。

signature 是什么？可以理解为与 sql_id、sql_hash_value 类似的值，用来标识 SQL。在10g 以上的版本中，查看 v$sql 的定义就可以发现 2 列：exact_matching_signature、force_matching_signature。通过下面的数据可以看出区别：

从上面的数据可以看出：

第2、3条 SQL 的 exact_matching_signature 相同，第7、8、9、10条 SQL 的 exact_matching_signature 相同。
第2、3条 SQL 的 force_matching_signature 相同，第4、5条 SQL 的 force_matching_signature 相同，第7、8、9、10条的 SQL 的 force_matching_signature 相同。第1、6条 SQL 的 force_matching_signature 相同

有如下的结论：对 SQL 语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量）后，如果 SQL 相同，那么 SQL 语句的 exact_matching_signature 就是相同的。对 SQL 语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量），然后去掉 SQL 中的常量，如果 SQL 相同，那么 SQL 语句的 force_matching_signature 就是相同的。但是例外的情况是：如果SQL中有绑定变量，force_matching_signature 就会与 exact_matching_signature 一样的生成标准。

可以看到，现在 exact_matching_signature 与 force_matching_signature 完全一样了。

从 force_matching_signature 的特性，我们可以想到一个用途，用于查找没有使用绑定变量的 SQL 语句，类似于使用 plan_hash_value 来查找。

回到前面，accept_sql_profile 这个过程，force_match 参数设为 TRUE，那么 dba_sql_profiles 中的 signature 则是由 SQL 的 force_matching_signature 而来，否则便是 exact_matching_signature 。对于 Outlines 来说，则只能是 exact_matching_signature 。从这个角度上讲，Sql Profiles 比 Outlines 的使用范围更广，因为 Sql profiles 对没有使用绑定变量的 SQL 也支持得很好。值得注意的是，Sql profiles的force_match 属性是不能更改的，只能在创建时指定，如果要更改，则只能重新创建改 Sql Profile 。

下面来看看 sys.sqlprof$attr 数据字典。这里面没有 SQL Profile 的名字，而是用的 sql 的 signature。大家从 attr_val 的结果发现了什么？

可以看到，SQL Profiles 的 attr_val 实际上就是一些 Hints，这跟 Outlines 没有本质上的区别。只是 SQL Profiles 中的 Hint，没有指定 SQL 使用哪个索引，也没有指定表的连接方法和连接顺序。这里只指定了 T1 表评估返回的行数，与原始的评估返回的行数的放大缩小的倍数。2498*0.01161091426 正好为 29 。这里就是告诉 Oracle 优化器，T1 表经过谓语过滤后返回行数应该为评估的 0.01161091426 倍。从这里可以看出，SQL Profiles 并不会锁定 SQL 的执行计划，只是提供了更多、更准确的统计信息给优化器。看下面的测试：