使用 SQL 计划管理控制执行计划演变

查看屏幕截图

将鼠标置于此图标上可以加载和查看本教程的所有屏幕截图。（警告：因为此操作会同时加载所有屏幕截图，所以网速较慢时，响应时间可能会比较长。）

注：此外，您还可以在下列步骤中将鼠标放在每个单独的图标上，从而仅加载和查看与该步骤相关的屏幕截图。可以通过单击各个屏幕截图来将其隐藏。

概述

当 SQL 语句的执行计划更改时，会出现潜在的性能风险。更改计划的原因多样，包括但不限于系统中的更改。优化程序版本、优化程序统计、优化程序参数、模式/元数据定义、系统设置及 SQL 配置文件创建。有时系统的更改（如删除索引）会导致不可恢复的计划更改。无法确保计划更改后变得更好将使得一些客户不敢更改其执行计划或优化程序统计。

在 Oracle 数据库 11g 中推出了一个新特性 SQL 计划管理 (SPM)，该特性提供了受控的执行计划演变。使用 SPM，优化程序将自动管理执行计划，并确保使用经过验证的计划。当发现一个用于 SQL 语句的的新计划时，需经过验证其比现有的计划表现更好或相当后才能使用。

前提条件

开始学习本教程之前，您应该先完成以下步骤：

1.	安装 Oracle 数据库 11g。
2.	下载 并将其解压缩到一个可用的目录。

自动捕获计划

将 init.ora parameter optimizer_capture_sql_plan_baselines 设置为 true 可开启自动计划捕获。当启动自动计划捕获后，SPM 信息库将自动填充任何可重复的 SQL 语句。为了识别可重复的 SQL 语句，优化程序将在首次编译 SQL 语句时，将其身份记录到语句日志中。将 SQL 语句的身份写入日志后，如果再次处理该语句（如执行或编译），其记录在语句日志中的身份将提示这是一个可重复的语句。系统将创建 SQL 计划历史和并添加当前的或基于成本的计划作为首个计划基线。执行以下步骤：

1.	打开企业管理器数据库控制，以 system 用户身份登录。
2.	选择 Server 选项卡。
3.	在 Query Optimizer 下选择 SQL Plan Control。
4.	选择 SQL Plan Baseline 选项卡。
5.	在 Settings 下，针对 Capture SQL Plan Baselines 选择 FALSE。
6.	针对参数 optimizer_capture_sql_plan_baseline 选择 TRUE 值，然后单击 OK。
7.	参数现在设为 TRUE。
8.	打开 SQL*Plus 会话，以 SYSTEM 身份登录。因为是首次运行该 SQL 语句，所以它现在还不是可重复的，也没有计划基线，您需要执行以下查询 2 次。 @load_auto_select.sql
9.	转换到企业管理器并在 SQL Text 域中输入 %LOAD_AUTO%，然后单击 Go。
10.	计划已经加入到列表中。选择 SQL Text 链接查看详细信息。
11.	SQL Plan Baseline 详细信息显示。注意 Origin 设为 AUTO_CAPTURE，因为自动加载了基线。单击 Return。
12.	现在您希望更改或改变优化程序模式，它将触发 SQL 语句执行一个不同的计划。转换到 SQL*Plus 更改优化程序模式。执行下列命令。 alter session set optimizer_mode = first_rows;
13.	重新运行以下查询。 @load_auto_select.sql
14.	因为 SQL 语句将有一个新计划，因此将自动捕获另一个计划基线。您可以选择计划基线进行确认。切换回 DB Control 并单击 Go。
15.	您可以看到 SQL 查询的两个基线，但注意到第二个计划未获得接受。新计划要经验证后才可接受为可使用的可行计划。选择未接受计划前的复选框并单击 Evolve。
16.	这里我们不考虑新计划的表现是否与当前 SQL 计划基线相当或更好，因此针对 Verify Performance 请选择 No。这表示该计划将变成接受的 SQL 计划基线，不管其性能如何。然后单击 OK。
17.	提交作业。再次单击 Go 刷新列表。
18.	现在该计划获得了接受。要查看解释计划，可选择 SQL Text 链接。
19.	本计划将执行位图索引全扫描。单击 Return。
20.	您现在可将优化程序模式重新设置为默认值并禁用自动计划基线捕获。切换到您的 SQL*Plus 会话，运行以下命令。 alter session set optimizer_mode = all_rows;
21.	切换回 DB Control。针对 Capture SQL PLAN Baselines 选择 TRUE。
22.	针对参数 optimizer_capture_sql_plan_baseline 选择 FALSE 值，然后单击 OK。
23.	参数已更改。
24.	您现在可以删除 SQL 基线。选择每个基线前的复选框，然后单击 Drop。
25.	单击 Yes 确认删除。
26.	基线已删除。

批量捕获计划

批量捕获计划有两种方式:

使用 SQL 调整集 (STS)

您可以使用 SQL 调整集来捕获关键的 SQL 语句集的计划详细信息。这是确保 10g 升级到 11g 过程中不出现意外计划更改的一种方法。执行以下步骤：

1.	要执行的第一个任务是检查解释计划。切换到 SQL*Plus 会话并执行以下脚本： @load_sts_explain_plan
2.	使用以下脚本执行 SQL 语句： @load_sts_select
3.	现在您可以通过执行以下命令更改优化程序模式： alter session set optimizer_mode = first_rows;
4.	现在您可以通过执行以下脚本获得一个不同的计划。 @load_sts_explain_plan @load_sts_select
5.	执行以下命令重新设置优化程序模式： alter session set optimizer_mode = all_rows;
6.	此时，您可以开始创建 SQL 调整集并加载计划了。在 SQL*Plus 会话中执行以下脚本创建 SQL 调整集。 @cr_sts
7.	现在可以使用您刚运行的 SQL 填充 SQL 调整集。执行以下脚本填充 SQL 调整集： @populate_sts
8.	切换到企业管理器。现在您已经准备好加载刚创建的 SQL 调整集了。在 SQL Control Plan > SQL Plan Baseline 选项卡中，单击 Load。
9.	选择 SQL Tuning Set 的手电筒图标。
10.	选择调整集 SPM_STS 然后单击 Select。
11.	单击 OK，提交基线作业。
12.	基线创建完成。单击 Refresh。
13.	在 SQL Text 域中输入 %LOAD_STS%，然后单击 Go。
14.	选择 SQL text 链接
15 .	解释计划显示。此次，由于计划的创建方式，请将 Origin 设置为 MANUAL-LOAD。单击 Return。
16.	现在可以删除 SQL 基线了。选择每个基线前的复选框，然后单击 Drop。
17.	单击 Yes 确认删除。
18.	基线已删除。

使用游标缓存

您可以使用 dbms_spm.load_plans_from_cursor_cache 直接从游标缓存加载计划。可以为游标缓存中的所有语句加载计划，或可以对模块名或模式名等应用过滤器，下次执行这些语句时将使用它们的基线计划。执行以下步骤：

1.	您需要执行的第一个任务是检查解释计划。切换到 SQL*Plus 会话并执行以下脚本： @load_cc_explain_plan
2.	使用以下脚本执行 SQL 语句： @load_cc_select
3.	现在您可以通过执行以下命令更改优化程序模式： alter session set optimizer_mode = first_rows;
4.	现在您可以通过执行以下脚本获得一个不同的计划。 @load_cc_explain_plan @load_cc_select
5.	执行以下命令重新设置优化程序模式： alter session set optimizer_mode = all_rows;
6.	现在游标缓存已经填充完毕，您需要获取您的 SQL 语句的 sql id，该 id 用于在加载基线时筛选游标缓存的内容。执行以下脚本： @load_cc_get_sql_id
7.	现在加载计划并创建基线。切换到企业管理器数据库控制。在 Server > SQL Plan Control > SQL Plan Baseline 页单击 Load。
8.	选择多选按钮 Load plans from cursor cache，然后选择 SQL id 的手电筒图标。
9.	在 SQL Text 域中输入 %LOAD_CC%，然后单击 Go。
10.	选择上一步您在 SQL*Plus 中查询游标缓存时所见的 SQL id 前的复选框，然后单击 Select。
11.	单击 OK。
12.	提交作业。在 SQL Text 域中输入 %LOAD_CC%，然后单击 Go。
13.	选择 SQL 语句的链接。
14.	解释计划显示。单击 Return。
15.	现在可以删除 SQL 基线了。选择每个基线前的复选框，然后单击 Drop。
16.	单击 Yes 确认删除。
17.	基线已删除。

管理计划

每次编译 SQL 语句时，优化程序首先使用传统的基于成本的搜索构建一个最佳成本计划。如果初始化参数 OPTIMIZER_USE_PLAN_BASELINES 设为 TRUE (默认值)，它将在 SQL 计划基线中查找一个修补计划。如果找到将照常继续后续步骤。否则将添加新计划到计划历史，然后在 SQL 计划基线中计算每个接受的计划的成本，然后选出成本最低的计划。执行以下步骤：

1.	您需要执行的第一个任务是检查解释计划。切换到 SQL*Plus 会话并执行以下脚本： @use_opm_explain_plan
2.	使用以下脚本执行 SQL 语句： @use_opm_select
3.	现在您可以通过执行以下命令更改优化程序模式： alter session set optimizer_mode = first_rows;
4.	现在您可以通过执行以下脚本获得一个不同的计划。 @use_opm_explain_plan @use_opm_select
5.	执行以下命令重新设置优化程序模式： alter session set optimizer_mode = all_rows;
6.	现在游标缓存已经填充完毕，您需要获取您的 SQL 语句的 sql id，该 id 用于在加载基线时筛选游标缓存的内容。执行以下脚本： @use_opm_get_sql_id
7.	现在加载计划并创建基线。切换到企业管理器数据库控制。在 Server > SQL Plan Control > SQL Plan Baseline 页单击 Load。
8.	选择多选按钮 Load plans from cursor cache，然后选择 SQL id 的手电筒图标。
9.	在 SQL Text 域中输入 %USE_OPM%，然后单击 Go。
10.	选择上一步您在 SQL*Plus 中查询游标缓存时所见的 SQL id 前的复选框，然后单击 Select。
11.	单击 OK。
12.	提交作业。在 SQL Text 域中输入 %USE_OPM%，然后单击 Go。SQL Plan Baselines 显示。
13.	现在已经加载了计划，您需要检查是否正在对其进行使用。切换到 SQL*Plus 会话并执行以下脚本： @use_opm_explain_plan2 解释输出最末尾的说明指出使用了一个基线。从执行计划您看到使用了第一个基线（全表扫描）。
14.	您可以将优化程序模式更改为 first_rows，以转而使用其他基线。执行以下命令： alter session set optimizer_mode = first_rows;
15.	重新运行解释计划查看其他基线。执行以下脚本： @use_opm_explain_plan2 从执行计划您看到使用了第二个基线（位图索引全扫描）。
16.	这时您想要禁用该计划基线，并检查它是否将使用其他计划基线。切换到企业管理器，选择上一步的解释计划中显示的计划前面的复选框。然后单击 Disable。
17.	计划禁用。
18.	切换到 SQL*Plus 会话并再次执行以下脚本： @use_opm_explain_plan2 在执行计划中，您可以看到使用的计划是索引扫描而不是全表扫描，因此这是第二个基线。
19.	最后一个任务是禁用其他计划基线。然后，优化程序将回到基于成本的方法。切换到企业管理器，选择上一步的解释计划中显示的计划前面的复选框。然后单击 Disable。
20.	现在，两个计划都已禁用。
21.	切换到 SQL*Plus 会话并执行上一次的脚本： @use_opm_explain_plan2 优化程序现在使用默认的基于成本的方法，因为计划的最后没有提示说明使用了某个基线。
22.	现在可以删除 SQL 基线了。选择每个基线前的复选框，然后单击 Drop。
23.	单击 Yes 确认删除。
24.	基线已删除。