执行计划：如何手工创建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，这里不做介绍，有兴趣的朋友请参考文档。