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

编辑手记:在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 。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-07-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏MongoDB中文社区

优化MongoDB复合索引

对于一个MongoDB的复杂查询,如何才能创建最好的索引?在本篇文章中,我将展现一种给读请求定制的索引优化方法,这种方法会考虑读请求中的比较,排序以及范围过滤运...

1352
来自专栏Java3y

数据库面试题(开发者必看)

数据库常见面试题(开发者篇) ? ? 这里写图片描述 什么是存储过程?有哪些优缺点? 什么是存储过程?有哪些优缺点? 存储过程就像我们编程语言中的函数一样,封装...

5705
来自专栏乐百川的学习频道

使用 pymysql 操作MySQL数据库

安装PyMySQL PyMySQL是一个Python编写的MySQL驱动程序,让我们可以用Python语言操作MySQL数据库。 首先,使用pip安装PyMyS...

3285
来自专栏光变

MySQL编程规范

871
来自专栏java一日一条

优化SQL查询:如何写出高性能SQL语句

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,比如一条SQL语句如果用来从一个 10万条记录的表中查...

713
来自专栏同步博客

MySQL查询优化

     一个好的web应用,最重要的一点是有着优秀的访问性能。数据库MySQL是web应用的组成部分,也是决定其性能的重要部分。所以提升MySQL的性能至关重...

1613
来自专栏维C果糖

史上最简单的 MySQL 教程(三十)「联合查询」

联合查询:union,将多次查询(多条select语句)的结果,在字段数相同的情况下,在记录的层次上进行拼接。

3569
来自专栏java达人

认识九大经典sql模式

小结果集,源表较少,查询条件直接针对源表 对于典型的OLTP应用,多为返回小结果集的查询。如果过滤条件直接针对源表,我们必须保证这些过滤条件高效,对于重要的字...

2198
来自专栏学习有记

阅读查询计划:SQL Server 索引进阶 Level 9

1176
来自专栏杨建荣的学习笔记

一个执行计划解析的小问题分析(r9笔记第37天)

前几天,一个开发的同学让我帮忙做一个大查询,给了我一个数据列表,里面的ID有几万个,提供了一个SQL语句,看这情况还得我自己来解析生成相关的SQL了。 假设ID...

2996

扫码关注云+社区