(题图授权来自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 的执行计划,只是提供了更多、更准确的统计信息给优化器。看下面的测试:
将 T1 表的统计信息中的表行数改为 500 万,Oracle 就会评估为返回 5000000*5%*0.01161091426=2903 行。这里执行计划又变回为 full scan+hash join 。可以看到,虽然 SQL Profile 起作用了,但是并没有锁定执行计划。
小结:本文简单介绍了什么是 SQL Profiles 及其作用,如何使用 SQL Tuning Advisor 来生成 SQL Profile ,以及生成的 SQL Profile 产生的 Hint 。同时也介绍了 SQL 的 signature 。如何加入"云和恩墨大讲堂"微信群