专栏首页数据和云执行计划:如何手工创建Profile维持SQL计划的稳定性

执行计划:如何手工创建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),作者:熊军

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 审核 - z3 产品理念与功能介绍

    我们都知道,在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的,一个开发环境中,众多的程序员难免引入一个又一个的或初级或高端的...

    数据和云
  • 杨廷琨Oracle Code大会分享:如何编写高效SQL(含PPT)

    2018 Oracle Code 于5月17日在新加坡拉开帷幕。作为全球开发者交流分享的年度盛会,为吸引所有领域的开发者,Oracle今年将自1996年开始的J...

    数据和云
  • 为什么用尽了办法你的系统性能还是不见改善

    随着业务数据的增长,以及新业务的推出,很多企业都面临着系统性能的问题,并且日益凸显。我们曾遇到很多这样的用户,似乎用尽了所有招数,但性能就是不见改善,问题到底出...

    数据和云
  • SQL 审核 - z3 产品理念与功能介绍

    我们都知道,在 DBA 所优化的数据库环境中,绝大多数性能问题其实是由于 SQL 编写不当导致的,一个开发环境中,众多的程序员难免引入一个又一个的或初级或高端的...

    数据和云
  • 【DB笔试面试603】在Oracle中,固定SQL执行计划的方法有哪些?

    在实际项目中,通常在开发环境下,一些SQL执行没有任何功能问题,而当到了生产环境或生产环境的数据量发生较大的变量时,其SQL的执行效率非常低。此时如果更改SQL...

    小麦苗DBA宝典
  • 为什么用尽了办法你的系统性能还是不见改善

    随着业务数据的增长,以及新业务的推出,很多企业都面临着系统性能的问题,并且日益凸显。我们曾遇到很多这样的用户,似乎用尽了所有招数,但性能就是不见改善,问题到底出...

    数据和云
  • 通过闪回事务查看数据dml的情况 (r2笔记69天)

    昨天有一个网友问我,怎么能够查询一个表中最后一条插入的记录,我大概回复了,可以通过闪回事务来实现,但是得看什么时候插入的数据,也需要一定的运气。 如果通过闪回事...

    jeanron100
  • Oracle Real Time SQL Monitoring

    术语说明 TableQueue,消息缓冲区,在并行操作中使用,用于PX进程之间的通信,或者PX进程与QC进程之间的通信,是内存中的一些page,每个消息缓冲区的...

    沃趣科技
  • 【SQL】历史SQL监控(Historical SQL Monitoring ) 功能(12c)

    Oracle 11g版本 推出了实时SQL监控功能(Real-Time SQL Monitoring),用于实时地监视执行中SQL的性能;Oracle 12c ...

    TeacherWhat
  • 【SQL Performance】实时SQL监控功能(Real-Time SQL Monitoring)

    实时SQL监控功能(Real-Time SQL Monitoring)是Oracle11g推出的功能,通过这个功能可以实时地监视执行中的SQL性能。

    TeacherWhat

扫码关注云+社区

领取腾讯云代金券