执行计划: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 条评论
登录 后参与评论

相关文章

来自专栏Golang语言社区

说说JSON和JSONP,也许你会豁然开朗-转

今天在写底层通信框架的时候,遇到了跨域的问题;随便给不知道的童鞋们分享下基础知识。 前言   由于Sencha Touch 2这种开发模式的特性,基本...

3696
来自专栏瓜大三哥

UVM(十一)之各种port

UVM(十一)之各种port UVM中内置了各种port,用于实现TLM级别的。 1. uvm_component之间的通信 如果要在两个uvm_compone...

2287
来自专栏数据库新发现

MySQL 8.0.12 有什么新特性?

原文链接:http://enmotech.com/web/detail/1/577/1.html

460
来自专栏菩提树下的杨过

将淘宝数据包导入自己的商城系统

淘宝网有一个淘宝助理,可以方便的将淘宝店的商品资源导出成csv格式的数据包。很多商城系统为了能快速输入商品,都会要求开发者能最大限度的利用淘宝数据包直接导入产品...

1849
来自专栏机器学习实践二三事

Hadoop之--HDFS

what is Hadoop The Apache Hadoop software library is a framework that allows for...

1909
来自专栏Golang语言社区

golang微信支付服务端

一般来说,使用golang主要还是写服务端。所以本文主要讲golang在处理微信移动支付的服务端时的统一下单接口和支付回调接口,以及查询接口。 微信支付流程 下...

4827
来自专栏数据和云

18C 也不能避免 SQL 解析的 Bug

作者简介 ? 苏星开 云和恩墨南区交付技术顾问,曾服务过通信、能源生产、金融等行业客户,擅长 SQL 审核和优化,DataGuard 容灾等。 1 概述 在 O...

29310
来自专栏搜云库

Spring Boot 中使用 Java API 调用 Elasticsearch

ElasticSearch 是一个高可用开源全文检索和分析组件。提供存储服务,搜索服务,大数据准实时分析等。一般用于提供一些提供复杂搜索的应用。 Elastic...

89710
来自专栏我是攻城师

SolrCloud6.1.0之SQL查询测试

3175
来自专栏散尽浮华

MongoDB集群运维笔记

前面的文章介绍了MongoDB副本集和分片集群的做法,下面对MongoDB集群的日常维护操作进行小总结:         MongDB副本集故障转移功能得益于它...

5909

扫描关注云+社区