执行计划:如何手工创建Profile维持SQL计划的稳定性

在上一篇:《执行计划:Oracle的Profile特性与SQL执行计划的稳定性》,向大家介绍了什么是 SQL Profiles 及其作用,如何使用 SQL Tuning Advisor 来生成 SQL Profile,以及生成的 SQL Profile 产生的 Hint 。同时也介绍了 SQL 的 signature 。那么在今天,将向大家介绍如何手工创建 SQL Profiles(即不通过 SQL Tuning Advisor )来达成2个目的:

  • 锁定或者说稳定 SQL 执行计划。
  • 在不能修改应用的 SQL 的情况下,来改变或者说是强制使 SQL 使用我们指定的执行计划,即使原始的 SQL 包含了 Hints 。

那么,这里最关键的一点是,如何来手工创建 SQL Profiles ?

答案是,正如上一篇中有朋友的留言,使用 DBMS_SQLTUNE.IMPORT_SQL_PROFILE过程。

这个过程其名字与实际功能有所差异,其实可以理解为 CREATE OR REPLACE SQL_PROFILE 。过程中的PROFILE参数为 SYS.SQLPROF_ATTR,这种类型其实就是 VARCHAR2 的集合类型( COLLECTION ):

下面我们就用这个过程来创建 SQL PROFILE:

为避免干扰,将上一篇测试中生成的 SQL Profile 删除掉,同时恢复T1表的统计信息中的表行数:

现在我们手工创建一个 SQL Profile:

下面执行SQL Profiles对应的SQL:

可以看到,SQL 使用了 SQL Profile,不过没有达到我们预期的效果。

看起来是 SQL Profile 使用的Hints有问题。我们重新设置 SQL Profile 的 Hints,在 Hints中加上 “Query Block Name" 。这一次在执行 IMPORT_SQL_PROFILE 过程时,将 REPLACE 参数设置为 TRUE,以替换现有的 SQL Profile:

再次执行下面的SQL:

这一次达到了预期的效果。看起来在SQL Profiles中对Hints还有一定的要求。

那么我们再一次手工修改T1表的统计信息,看看结果如何:

可以看到,Oracle 优化器评估表 T1 经过 Like 条件过滤后返回的行数虽然很大,但是这里的执行计划仍然与未修改统计信息之前一样,使用 range scan+ nested loop join。

通过以上的测试,我们明白了 DBMS_SQLTUNE.IMPORT_SQL_PROFILE 的使用,同时也验证了这种方式的有效性,SQL Profiles 能够像 Outlines 一样,能够稳定 SQL 的执行计划。

接下来我们需要完成两个任务。

任务一:对现有的 SQL 稳定其执行计划。

这里的问题是:稳定一条 SQL 语句的 Hints 从哪里来?简单的 sql,没问题,我们可以手工构造,但是复杂的 SQL,手工构造相对比较复杂,同时手工构造的Hints不一定能够保证SQL 的执行计划就会稳定。从 10g 开始,v$sql_plan 中就包括了 SQL 语句 OUTLINE 数据,也就是稳定执行计划的Hints。从下面可以看到:

上面所显示的 “Outline Data” 即是我们稳定 SQL 执行计划需要的Hints(我们甚至可以将这些Hints直接写到我们的 SQL 中)。对需要稳定执行计划的 SQL,我们所要做的就是如前面所示,将 Hints 与 SQL 文本一起创建一个 SQL Profile 。这里不得不提到一个 SQL 脚本,来自 MOS 。"SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly [ID 215187.1]",在这篇文章中,可以下载到 sqlt.zip,这个压缩文件内有一个文件:coe_xfr_sql_profile.sql。这个脚本可以用于从 shared pool 、awr 中提取指定的 SQL ID 的 Outline Data 并创建 SQL Profile 。下面是示例:

coe_xfr_sql_profile.sql 这个脚本首先要求输入 sql id,然后从 shared pool 、awr 中获取 sql 执行的各个执行计划的统计信息(执行计划不稳定的 SQL 通常会有多个不同的执行计划),然后输入你认为是正确的、需要稳定的执行计划的 hash value,脚本就会生成另一个脚本,这里为 coe_xfr_sql_profile_b4zvp712np1bp_2959412835.sql,然后运行这个脚本,就会创建出稳定执行计划所需要的 SQL Profile,SQL Profile 的名字为:coe+sql_id+plan_hash_value,这里为 coe_b4zvp712np1bp_2959412835。注意,这里创建的 SQL Profile,force match 默认为 FALSE,我们可以手工修改脚本将其改为 TRUE,同时我们也可以按意愿来修改生成的脚本的其他内容。

除了上面提到的脚本,http://kerryosborne.oracle-guy.com 这个 BLOG 里面也有许多与 SQL Profiles 相关的脚本。其中 create_sql_profile.sql 可完成类似的功能,只不过功能相对简单,只能从 shared pool 中生成 SQL Profile,因此也更方便。

任务二:在不能修改 SQL 的情况下改变并固定 SQL 的执行计划,即使原始的 SQL 使用了Hints。

常常遇到这样的情况,SQL 语句其执行计划有问题,或者是 SQL 使用了错误的 Hints (比如 /*+ RULE */)导致 SQL 性能较差,但是应用又不能修改或者时间内不能修改,那么我们怎么来改变SQL的执行计划呢。有3种办法,一种是调整统计信息,这个不建议使用,因为比较复杂、不稳定可靠(统计信息可能会重新收集),影响面广(会影响其他访问此对象的 SQL)。第二种是使用 OUTLINE,这种方法比较复杂。第三种就是我们今天要介绍的使用 SQL Profiles 了。

使用 SQL Profiles 来改变 SQL 的执行计划,其本质上就是使用 Hints 来改变 SQL 的执行计划。对于简单的 SQL,我们同样可以像前面一样手工构造Hints然后再使用 DBMS_SQLTUNE.IMPORT_SQL_PROFILE 来实现。但是这种方法还是略显烦琐。那么通常的方法就是"乾坤大挪移“了:

  • 取得原始 SQL 的文本(如有可能还包括sql id)
  • 构造一个与原始 SQL 在逻辑上、结构上完全相同的 SQL 。这里强制逻辑上和结构上相同,SQL 解析的用户名、SQL 中引用对象的用户名甚至是一些 predicate 条件都可以不同。当然能够与原始 SQL 完全一样就更省事。
  • 执行我们构造的 SQL,并取得构造的 SQL 的 Outline Data。
  • 使用原始 SQL 的文本和构造的 SQL 的 Outline Data 创建 SQL Profile。

下面我们来演示一下整个过程。我们这里要修改执行计划的 SQL 是:

我们首先需要执行这一条 SQL,然后取得的 SQL ID为gmvb9bp7f9kqd:

然后我们构造一条 SQL,让这条 SQL 按我们希望的执行计划运行,构造的 SQL 其 ID 为 cymak300cycmd:

然后使用 coe_xfr_sql_profile.sql 脚本来提取我们构造的 SQL 的 Outline Data ,生成的结果为 coe_xfr_sql_profile_cymak300cycmd_3787413387.sql,打开结果文件,可以看到有这么一段:

再针对 gmvb9bp7f9kqd 使用 coe_xfr_sql_profile.sql,生成的结果文件为 coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql 。手工修改这个文件,将里面 h := SYS.SQLPROF_ATTR... 那一段替换成我们之前得到的那一段。这一次我们将这个文件中的 force_match从FALSE 改成 TRUE。

最后我们运行 coe_xfr_sql_profile_gmvb9bp7f9kqd_2959412835.sql 这个脚本文件:

这样就完成了我们所需要的 SQL Profile 的创建。下面再看看原来的 SQL 执行情况(这里我故意将 like 条件改了一下,以查看 force match 是否起作用):

可以看到 SQL Profile 起作用了。

最后一步,生成 SQL Profile 时稍显复杂,不过我们可以修改之前提到的 create_sql_profile.sql 文件来达到同样的目的,只不过前几个步骤仍然是不可省略的。将里面的代码:

改为

注意这里 modi_sql_id和modi_child_no 为我们构造的 SQL 执行后的 id 及 child_number。同时这2个变量在文件前面需要定义,此处不再细述。

小结:本文承接上一篇,介绍了如何利用 SQL Profile 来稳定执行计划;如何利用 SQL Profile 来改变 SQL 的执行计划。对于 SQL Profiles 来说,不属于任何一个用户,比 Outlines 更具有操控性灵活性。对于 SQL Profiles 的 category,这里不做介绍,有兴趣的朋友请参考文档。

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

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏不想当开发的产品不是好测试

mysql 删表引出的问题

背景 将测试环境的表同步到另外一个数据库服务器中,但有些表里面数据巨大,(其实不同步该表的数据就行,当时没想太多),几千万的数据!! 步骤 1. 既然已经把数据...

1957
来自专栏MYSQL轻松学

MySQL Innodb MTR源码解析

最近看了下Mysql innodb源码MTR模块,了解源码能帮助DBA更熟悉数据库运行原理、更容易定位排查问题。那么什么是Mtr?Mtr究竟是用来做什么的?围绕...

3636
来自专栏JackeyGao的博客

五个很实用的Django 项目推荐

很多Django的包都能很大的增加我们的开发效率或者增加我们项目的功能, 比如: django-rest-framework 、wagtail, 它们带来了很棒...

703
来自专栏joycl

c#面试题汇总

下面的参考解答只是帮助大家理解,不用背,面试题、笔试题千变万化,不要梦想着把题覆盖了,下面的题是供大家查漏补缺用的,真正的把这些题搞懂了,才能“以不变应万变”。...

641
来自专栏斑斓

Redux框架reducer对状态的处理

前言 在react+redux项目里,关于reducer处理state的方式,在redux官方文档中有这样一段描述: 不要修改 state。 使用 Objec...

3475
来自专栏Python入门

使用Python这么多年,才发现Python还有这些实用的功能和特点

在使用Python多年以后,我偶然发现了一些我们过去不知道的功能和特性。一些可以说是非常有用,但却没有充分利用。考虑到这一点,我编辑了一些你应该了解的Pytho...

671
来自专栏CDA数据分析师

不能不懂的 Python 7大功能和特点

在使用Python多年以后,我偶然发现了一些我们过去不知道的功能和特性。一些可以说是非常有用,但却没有充分利用。考虑到这一点,我编辑了一些你应该了解的Pytho...

1778
来自专栏企鹅号快讯

python中any和all如何使用

python中any()和all()如何使用 和 对于检查两个对象相等时非常实用,但是要注意, 和 是python内置函数,同时numpy也有自己实现的 和 ,...

1755
来自专栏AndroidTv

分享两个提高效率的AndroidStudio小技巧

这次分享两个 Android Studio 的小技巧,能够有效提高效率和减少犯错,尤其是在团队协作开发中。

40813
来自专栏文渊之博

探索SQL Server元数据(一)

  在数据库中,我们除了存储数据外,还存储了大量的元数据。它们主要的作用就是描述数据库怎么建立、配置、以及各种对象的属性等。本篇简单介绍如何使用和查询元数据,如...

942

扫描关注云+社区