执行计划:如何手工创建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 条评论
登录 后参与评论

相关文章

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

看似诡异的tablespace online问题(r5笔记第95天)

今天在做一些演示的时候,在虚拟机上装了两套数据库软件,10g和11g的。还是在演示普通数据文件迁移的时候还是碰到了一些意料之外的问题,从当时的情况来看感觉还是比...

3007
来自专栏破晓之歌

Python接入mysql数据库 原

右侧有个database,点开后左上角有个“+”符号,选择Data Source-Mysql

551
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(90)-EF 扩展操作

这次我们来看 EntityFramework-Plus(免费开源) 库的用法相比其他扩展库,这个更加新并且用法更加简单

760
来自专栏北京马哥教育

初识mysql:基本原理和使用

一、 数据库的出现 1. 数据库是什么: 数据库简单来说,就是存储数据的地方(废话),对于用户认证这个过程来说,当用户登录服务器时, 系统需要把用户的输入的...

3845
来自专栏乐沙弥的世界

如何使用 orachk 工具

      Oracle RAC 安装完毕后的健壮性是一个令人头疼的问题。之前Oracle为之专门推出了raccheck工具,确实方便了我们这些个苦逼的DBA。...

752
来自专栏野路子程序员

整合用户篇—Oauth2理解与构造简单的系统

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

利用shell脚本生成动态sql(67天)

在一些分布式环境中,可能涉及到的数据库有很多,相关的数据库用户也不少,有些看似简单的变更可能需要在不同的库,不同的用户间要进行复杂的操作。 现在我们有3套环境,...

2757
来自专栏逸鹏说道

SQL Server安全(3/11):主体和安全对象(Principals and Securables)

在保密你的服务器和数据,防备当前复杂的攻击,SQL Server有你需要的一切。但在你能有效使用这些安全功能前,你需要理解你面对的威胁和一些基本的安全概念。这篇...

2454
来自专栏c#开发者

ado.net data services开发框架学习

ado.net data services开发框架学习 什么是ado.net data services Ado.net 数据服务可以很方便的将企业内部数据发...

2566
来自专栏乐沙弥的世界

RAC 环境下参数文件(spfile)管理

    RAC环境下,初始化参数文件与但实例下参数文件有些异同,主要表现在初始化参数可以为多个实例公用,也可以单独设置各个实例的初始化参数。对于那些非共用的初始...

884

扫码关注云+社区