熊军 oracle 迁移 spm,Oracle固定SQL的执行计划(二)---SPM

之前写了一篇文章介绍的是用SQL Profile来调整、稳定目标SQL的执行计划，即使无法修改目标SQL的SQL文本。但SQL Profile实际上只是一种亡羊补牢、被动的技术手段，应用在那些执行计划已经发生了不好的变更的SQL上，即当我们发现这些SQL的执行计划已经出了问题时通过创建SQL Profile来纠正、稳定这些SQL的执行计划。即便通过创建SQL Profile解决了目标SQL执行计划变更的问题，依然不能保证系统后续执行的SQL的执行计划就不再发生不好的变更。这种不确定性会给Oracle数据库大版本升级(比如从Oracle 10g升级到Oracle 11g)带来一系列的麻烦，因为不清楚升级之后原先系统中哪些SQL的执行计划会发生不好的变更。

为了解决上述问题，Oracle在11g中推出了SPM(SQL Plan Management)。SPM是一种主动的稳定执行计划的手段，能够保证只有被验证过的执行计划才会被启用，当由于种种原因(如统计信息的变更)而导致目标SQL产生了新的执行计划后，这个新的执行计划并不会被马上启用，直到它已经被我们验证过其执行效率会比原先执行计划高才会被启用。

随着Oracle数据库版本的不段推进，其CBO的算法、功能也在一直不断进化和增加，所以同样的SQL有可能在新版本的Oralce数据库中执行效率更高，如果我们使用了SQL Profile(特别是使用了Manual类型的SQL Profile)来稳定目标SQL的执行计划，那就意味着可能失去了继续优化上述SQL的执行效率的机会。而SPM的推出可以说彻底解决了执行计划稳定性的问题，它既能主动地稳定执行计划，又保留了继续使用新的执行效率可能更高的执行计划的机会。

当启用了SPM后，每一个SQL都会存在对应的SQL Plan Baseline，这个SQL Plan Baseline里存储的就是该SQL的执行计划，如果一个SQL有多个执行计划，那么该SQL就可能会有多个SQL Plan Baseline，可以从DBA_SQL_PLAN_BASELINES中查看目标SQL所有的SQL Plan Baseline。

DBA_SQL_PLAN_BASELINES中的列ENABLED和ACCEPTED用来描述一个SQL Plan Baseline所对应的执行计划是否能被Oracle启用，只有ENABLED和ACCEPTED的值均为“YES”的SQL Plan Baseline所对应的执行计划才会被Oracle启用，如果一具SQL有超过1个以上的SQL Plan Baseline的ENABLED和ACCEPTED的值均为YES，则Oracle会从中选择成本值最小的一个所对应的执行坟墓来作为该SQL的执行计划。

在Oracle 11g及其以上的版本中，有如下两种方法可以产生目标SQL的SQL Plan Baseline。

自动捕获

手工生成/批量导入(批量导入尤其适用于Oracle数据库大版本的升级，它可以确保升级后原有系统所胡SQL的执行计划不会发生变化)

下面分别介绍如何自动捕获和手工的方式来产生SQL Plan Baseline。

1 自动捕获SQL Plan Baseline

参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES用于控制是否开启自动捕获SQL Plan Baseline，其默认值为FALSE，表示在默认情况下，Oracle并不会自动捕获SQL Plan Baseline。这个参数可以在session或系统级别动态修改。当修改为TRUE后，则Oracle会对上述参数影响范围内所有重复执行的SQL自动捕获其SQL Plan Baseline，并且针对目标SQL第一次捕获的SQL Plan Baseline的ENABLED和ACCEPTED的值均为“YES”。随后如果该SQL的执行计划发生了变更，则再次捕获到的SQL Plan Baseline的ENABLED的值依然为YES，但ACCEPTED的值变为了NO，这表示后续变更的执行计划虽然被捕获了，但Oracle不会将其作为该SQL的执行计划来执行，即此时Oracle会永远沿用该SQL第一次被捕获的SQL Plan Baseline所对应的执行计划(除非后续做了手工调整)。

参数OPTIMIZER_USE_SQL_PLAN_BASELINES用于控制是否启用SQL Plan Baseline，其默认值为TRUE，表示在默认情况下，Oracle在生成执行计划时就会启用SPM，使用已有的SQL Plan Baseline，这个参数也可以在session或系统级别动态修改。

下面看一下实例：

查看上述两个参数的默认值

在当前session中禁掉SPM并同时开启自动捕获SQL Plan Baseline：

创建测试表T2

从执行计划上看，走的是索引IDX_T2上的索引范围扫描，因为SQL只执行了一次，所以Oracle不会自动捕获SQL Plan Baseline，DBA_SQL_PLAN_BASELINES中没有记录

再次执行上述SQL，因为重复执行该SQL，Oracle自动捕获了这个SQL的SQL Plan Baseline

现在将索引IDX_T2的聚簇因子修改为2400万，目的是为了能让SQL的执行计划变为对表T2的全表扫描(为何修改聚簇因子，参考http://www.linuxidc.com/Linux/2017-02/141071.htm )。修改完后再执行上述SQL，并查看执行计划：

从执行计划中可以看出该SQL的执行计划已经变为全表扫描。因为目标SQL已经重复执行且同时又产生了一个新的执行计划，所以现在Oracle就会自动捕获并创建这个新的执行计划所对应的SQL Plan Baseline了。从如下查询可以看出Oracle对新的执行计划产生了一个新的SQL Plan Baseline，其ENABLED的值依然为YES，但ACCEPTED的值变为了NO：

现在我们对当前Session关闭自动捕获SQL Plan Baseline并同时开启SPM，现在索引IDX_T2的聚簇因子依然为2400万，再次执行目标SQL，并查看执行计划：

从上面的显示内容可以看出，现在目标SQL的执行又从全表扫描恢复为了索引范围扫描，并且执行计划中的Note部分有“SQL plan baseline SQL_PLAN_asnmb3t5yfk4024c6dbb6 used for this statement”内容，说明SPM开启的情况下，即便目标SQL产生了新的执行计划，Oracle依然只会应用该SQL的ENABLED和ACCEPTED的值均为YES的SQL Plan Baselline。

如果想启用目标SQL新的执行计划(即全表扫描)，应该如何做呢？

针对不同的Oracle版本，会有不同的处理方法。比如这里想启用目标SQL的新的执行计划，如果是11gR1的环境，则只需要将目标SQL所采用的名为SQL_PLAN_asnmb3t5yfk4024c6dbb6的SQL Plan Baseline(即索引范围扫描)的ACCEPTED的值设为NO就可以了。但对于11gR2环境，上述方法会报错，因为在11gR2中，所有已经被ACCEPTED的SQL Plan Baseline的ACCEPTED的值将不再能够被设为NO：

在11gR2中，我们可以联合使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE和DBMS_SPM.ALTER_SQL_PLAN_BASELINE达到启用目标SQL新的执行计划的目的。

先用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE将新的执行计划(全表扫描)所对应的SQL Plan Baseline的ACCEPTED值设为“YES”：

从上面显示的内容看到如下信息：“Plan: SQL_PLAN_asnmb3t5yfk40b860bcf2----Plan was changed to an accepted plan.”，这表明已经将新的执行计划(全表扫描)所对应的SQL Plan Baseline的ACCEPTED值设为YES

从下面的查询结果也可以证明：

然后再使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE将原先的执行计划(索引范围扫描)对应的SQL Plan Baseline的ENABLED的值设为NO：

再次执行目标SQL

从上述显示可以看出，现在SQL的执行计划已经变为了全表扫描，我们要启用新的执行计划(全表扫描)的目的已经实现，Note部分也有了提示。

从上述测试结果可以看出，实际上我们可以轻易地在目标SQL的多个执行计划中切换，所以SPM确实是既能够主动地稳定执行计划，又保留了继续使用新的执行计划的机会，并且我们很容易就能启用新的执行计划。

下面介绍手工生成SQL Plan Baseline：

手工生成目标SQL的SQL Plan Baseline其实非常简单，其核心就是调用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE。这里只讨论针对单个SQL的SQL Plan Baseline的手工生成。

之前介绍过用Manual类型的SQL Profile可以在不改变目标SQL的SQL文本的情况下调整其执行计划。实际上，用手工生成SQL Plan Baseline的方式也完全可以实现同样的目的，甚至会比使用Manual类型的SQL Profile更加简洁。

手工生成目标SQL的SQL Plan Baseline的具体步骤为：

1)针对目标SQL使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手工生成其初始执行计划所对应的SQL Plan Baseline。此时，使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE传入的参数如下所示：

dbms_spm.load_plans_from_cursor_cache(sql_id=>'原目标SQL的SQL_ID',plan_hash_value=>原目标SQL的PLAN HASH VALUE)

2)改写原目标SQL的SQL文本，在其中加入合适的Hint，直到加入Hint后的所改写的SQL能走出我们想要的执行计划，然后对改写后的SQL使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手工生成新的执行计划所对应的SQL Plan Baseline。此时传入的参数如下所示：

dbms_spm.load_plans_from_cursor_cache(sql_id=>'加入合适Hint后改写SQL的SQL_ID',plan_hash_value=>加入合适Hint后改写SQL的PLAN HASH VALUE,sql_handle=>'原目标SQL在步骤(1)中所产生的SQL Plan Baseline的sql_handle')

3)使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE删除步骤(1)中手工生成的原目标SQL的初始执行计划所对应的SQL Plan Baseline。此时传入的参数如下所示：

dbms_spm.drop_sql_plan_baseline(sql_handle=>'原目标SQL在步骤(1)中所产生的SQL Plan Baseline的sql_handle',plan_name=>'原目标SQL在步骤(1)中所产生的SQL Plan Baseline的plan_name')

下面使用一个实例演示：

从上述显示目标SQL初始执行计划为全表扫描，sql_id和plan hash value可以从执行计划中找到，由于没有启用自动捕获SQL Plan Baseline，一开始没有查到目标SQL对应的SQL Plan Baseline，手工生成后，可以查到全表扫描对应的SQL Plan Baseline。

改写原目标SQL，加入Hint后重新执行：

从上述输出可以看出把改写过的SQL的新的执行计划所对应的SQL Plan Baseline已经成功生成，而且所有手工生成的SQL Plan Baseline的ENABLED和ACCEPTED的值均为YES，这是和自动捕获的SQL Plan Baseline不一样的地方。

Drop掉原执行计划(全表扫描)所对应的SQL Plan Baseline：

再次执行原目标SQL，并查看执行计划

从上述输出可以看出，原目标SQL已经走了新的执行计划(索引范围扫描)，而且Note部分也有提示“SQL plan baseline SQL_PLAN_7bc3aw1b24guz24c6dbb6 used for this statement”说明走了SPM。