分类： Oracle
2008-04-24 21:20:04
但这说起来容易做起来难。当数据模式改变时会发生什么？以自适应游标一节中显示的示例为例。现在，CUSTOMERS 表中填充了来自纽约的客户，因此 STATE_CODE 大部分为 "NY"。因此，当执行含有如下所示谓词的查询时：
where state_code = 'CT'
系统执行一次全表扫描而非索引扫描。当谓词为：
where state_code = 'CT'
由于仅将返回几行结果，因此系统使用索引。然而，如果模式发生改变 － 假设，突然出现大量来自康涅狄格 (state_code = 'CT') 的客户，导致含有 CT 的结果的百分比升至 70％，此时会发生什么？在该情况下，CT 查询应使用全表扫描。但是，由于您已禁止收集优化程序统计数据，优化程序不会了解模式的更改，并且会继续提供无效率的索引扫描路径。您可以做些什么？
如果 Oracle 使用最优计划，但在统计数据收集或数据库参数等底层因素更改时重新评估该计划，此时，当且仅当新计划更有效时数据库才会使用，结果如何？该方案非常理想，不是吗？它在 Oracle 数据库 11g 中已成为可能。让我们看一看这种方案的实现方式。
SQL 计划基准线设定
在 Oracle 数据库 11g 中，当一个已经计算好的优化程序计划由于底层因素的更改而需要更新时，新计划不会立即实施。Oracle 会对这个新计划进行评估。仅当它比原有计划更有效时，Oracle 才会实施新计划。此外，还可以使用工具和接口来查看为每个查询计算的计划的历史，以及这些计划的对比情况。
当 Oracle 将一个语句确定为多次执行或“可重复的”语句，声明周期开始。一旦确定了一个可重复语句，数据库即会捕获它的计划，并将该计划作为 SQL 计划基准线存储在数据库一个称为 SQL 管理库 (SMB) 的逻辑结构中。当出于任何原因为该查询计算新计划时，新计划也存储在 SMB 中。因此，SMB 用于存储查询的每个计划、计划的生成方式等等。
计划不会自动存储在 SMB 中。如果上述情况属实，SMB 将存储每类查询的所有计划，并将变得十分庞大。因此，您可以并且应该控制 SMB 存储的查询的数量。执行该操作有两种方法：自动为 SMB 中的所有可重复查询设定基准线，或手动加载应设定基准线的查询
让我们先看一个简单的示例：通过将数据库参数 optimizer_capture_sql_plan_baselines（默认值为 FALSE）的值设置为 TRUE，您可以使 SQL 计划管理特性自动捕获所有可重复查询的 SQL 计划基准线。很幸运，这是一个动态参数。
SQL> alter system optimizer_capture_sql_plan_baselines = true;
该语句执行后，所有可重复语句的执行计划都作为 SQL 计划基准线存储在 SMB 中。SQL 计划基准线存储在名为 DBA_SQL_PLAN_BASELINES 的视图中。您也可以在 Enterprise Manager 中看到这些内容。要检查设定了基准线的计划，请打开 EM 并单击 "Server" 选项卡，如下图所示：
在该页单击 Query Optimizer 部分中的 SQL Plan Control，这将打开下方显示的 SPM 主页面。
单击 SQL Plan Baseline 选项卡，该操作将打开如下所示的屏幕：
这是 SQL 计划基准线的主屏幕。您将在屏幕左上角看到配置参数。Capture SQL Plan Baselines 显示为 TRUE，该值是您使用 ALTER SYSTEM 命令启用的。该参数下方是设置为 TRUE（默认值）的 Use SQL Plan Baselines。它表示，如果存在 SQL 计划基准线，则为查询使用该基准线。
每当为查询生成一个新计划，原有计划就保留在 SMB 的历史中。然而，这也意味着 SMB 中将挤满计划历史。一个参数可以控制计划保留的星期数，它显示在 Plan Retention (Weeks) 的文本框中。在本屏幕中，该参数设置为 53 周。如果一个 SQL 计划基准线的未使用时间超过 53 周，该基准线将被自动清除。
该屏幕的中间部分有一个搜索框，可用于搜索 SQL 语句。在此处输入一个搜索字符串，然后按下 Go，您将看到如上图中显示的 SQL 语句和相关计划。每个设定了基准线的计划都有大量与之相关的状态信息。让我们看一看这些信息：
DBA_SQL_PLAN_BASELINES 视图中提供了上述信息和额外的信息：
SQL> desc DBA_SQL_PLAN_BASELINES
Name Null? Type
----------------------------------------- -------- ---------------
SIGNATURE NOT NULL NUMBER
SQL_HANDLE NOT NULL VARCHAR2(30)
SQL_TEXT NOT NULL CLOB
PLAN_NAME NOT NULL VARCHAR2(30)
CREATOR VARCHAR2(30)
ORIGIN VARCHAR2(14)
PARSING_SCHEMA_NAME VARCHAR2(30)
DESCRIPTION VARCHAR2(500)
VERSION VARCHAR2(64)
CREATED NOT NULL TIMESTAMP(6)
LAST_MODIFIED TIMESTAMP(6)
LAST_EXECUTED TIMESTAMP(6)
LAST_VERIFIED TIMESTAMP(6)
ENABLED VARCHAR2(3)
ACCEPTED VARCHAR2(3)
FIXED VARCHAR2(3)
AUTOPURGE VARCHAR2(3)
OPTIMIZER_COST NUMBER
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
EXECUTIONS NUMBER
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
END_OF_FETCH_COUNT NUMBER
![]()
如果单击计划的名称，则将显示计划的详细信息。以下是输出结果：
在这些详细信息中，您可以看到查询的解释计划，以及其他相关信息，如该计划是否为可接受、已启用或固定计划等等。另一个重要的属性是 "Origin"，它显示 AUTO-CAPTURE，表示由于已将 optimizer_capture_sql_plan_baselines 设置为 TRUE，因此系统自动捕获该计划。
单击 Return，返回到如上图中显示的计划列表。现在，选择一个状态不为可接受的计划并单击 Evolve，查看系统是否会检查该计划来获取一个可能更有效的计划。弹出以下屏幕。
此屏幕中需要注意的重点是 Verify Performance 单选按钮。如果您希望检查计划，并将其性能与该查询当前 SQL 计划基准线的性能进行比较，您应该选中该按钮。单击 OK。屏幕显示比较报告：
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
![]()
Inputs:
-------
PLAN_LIST = SYS_SQL_PLAN_b5429522ee05ab0e
SYS_SQL_PLAN_b5429522e53beeec
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
![]()
Plan: SYS_SQL_PLAN_b5429522e53beeec
-----------------------------------
It is already an accepted plan.
![]()
Plan: SYS_SQL_PLAN_b5429522ee05ab0e
-----------------------------------
Plan was verified: Time used 3.9 seconds.
Failed performance criterion: Compound improvement ratio <= 1.4.
![]()
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): 3396 440 7.72
CPU Time(ms): 1990 408 4.88
Buffer Gets: 7048 5140 1.37
Disk Reads: 4732 53 89.28
Direct Writes: 0 0
Fetches: 4732 25 189.28
Executions: 1 1
![]()
这是一份较好的比较报告，显示了计划的对比情况。如果显示特定计划拥有更好的性能，优化程序将使用该计划。如果新计划的性能改进并不明显，系统不会接收或使用该计划。SQL 性能管理允许您直接看到各计划的对比情况，从而使用最适当的计划。
通过执行 DBMS_SPM 程序包，您可以手动更改计划的可执行状态：
declare
ctr binary_integer;
begin
ctr := dbms_spm.alter_sql_plan_baseline (
sql_handle => 'SYS_SQL_e0b19f65b5429522',
plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e',
attribute_name => 'ACCEPTED',
attribute_value => 'NO'
);
end;
您可以禁用一个 SQL 计划基准线，使优化程序不能使用该计划。稍后，您可以再次启用该计划，使该计划重新获得使用。要禁用计划，使用以下命令：
declare
ctr binary_integer;
begin
ctr := dbms_spm.alter_sql_plan_baseline (
sql_handle => 'SYS_SQL_e0b19f65b5429522',
plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e',
attribute_name => 'ENABLED',
attribute_value => 'NO'
);
end;
如果一个特定 SQL 语句的计划由一条基准线固定，解释计划会清楚地显示出来。在计划的末尾，您将看到一行内容，确定该计划已由一条基准线固定。
差别与存储大纲
如果您熟悉存储大纲，您一定在考虑它与 SQL 计划管理有何差别。它们似乎在做同一件事：为查询强制一个特定的执行计划。但他们确实有细微的差别，即，使用 SQL 计划管理，系统可以评估基准线以获得更好的计划，也可以激活新计划来代替原有计划。而大纲是固定的。除非禁用或使用其他概要文件代替，否则大纲不能被覆盖。此外，计划基准线还拥有历史数据，您可以通过这些数据了解一段时间内计划的发展情况。
相关问题可能包括：如果查询上具有一个存储大纲，而基准线找到一个更好的方案，这时将出现什么情况？将会产生冲突，不是吗？其实并非如此。当使用大纲分析查询时，系统将捕获大纲强制的执行计划，将其作为查询的 SQL 计划基准线。如果优化程序为该语句找到其他的执行计划，系统也会捕获此计划，并将其存储在 SMB 中。但它不会成为可接受的计划。在使用该计划之前，您必须执行发展流程，证明新的执行计划优于当前的 SQL 计划基准线（原有存储大纲）。
差别与存储概要文件
概要文件不是“计划”，而是基于数据、作为执行计划的一部分进行存储的元数据。因此，在使用概要文件时，查询计划可以根据谓词发生变更。而在使用 SQL 计划基准线时，无论谓词中的值是什么，计划都是相同的。
用例
那么，您可以在哪些示例场景中使用此特性？最好的示例就是升级或其他参数发生变化时。为一组查询设定基准线的一种方法是，使用 SQL 调整工具集，然后将语句从 STS 加载到 SPM。这样，您可以在 Oracle 数据库 10g 中生成一个 STS，将其导出，然后导入 Oracle 数据库 11g 中，随后运行 DBMS_SPM.UNPACK_STGTAB_BASELINE 程序包，导入作为 SQL 计划基准线的执行计划。稍后，当优化程序找到更好的计划时，会将该计划添加到 SMB 中，允许您进行比较。
结论
要了解数据库目前如何智能地对待收到的各种请求以及如何作出回应，自适应游标和 SQL 计划管理仅仅是其中的两个示例。这两个特性允许您在两个方面都获得最大的好处 － 利用自适应游标，您可以使用绑定变量，且不存在使用非最佳计划的风险；使用 SQL 计划管理时，执行计划并不是固定不变的，而会在保持短期内稳定性的同时，随时间的推移不断变化。
