Oracle 11g 执行计划管理概述

以下内容来源于：http://www.51cto.com/art/200806/76223.htm

35.2  执行计划管理

35.2.1  概述

同一SQL语句的执行计划可能因为优化器的版本、优化统计、优化参数、系统设置的不同而不同。而SQL语句的执行计划自动改变，通常情况下会带来性能提升，但是在某些情况下可能导致系统性能的下降。在11g之前，DBA使用存储大纲（Stored Outline）和SQL 概要（Profile）来固定某些SQL语句的执行计划，防止因为系统自动更改执行计划而导致的性能下降。但是，它们都需要手工的主动配置。

在11gr1版本中，Oracle引入SQL执行计划管理（SQL Plan Management），让系统自动来控制、使用更好的执行计划，进而防止执行计划发生变化而导致的性能下降。通过设置OPTIMIZER_CAPTURE_ SQL_PLAN_BASELINES为TRUE激活这个选项。当激活这个执行计划管理新特性后，如果某条语句产生了一个新的执行计划，那么仅在比参考的执行计划更优的情况下新计划才会被使用。

为了实现执行计划管理，优化器自动为所有执行次数超过一次的SQL语句维护执行的计划历史（Plan History）。优化器通过维护一个语句执行的声明日志（Statement Log）来识别该SQL语句是否为第二次执行。计划历史中包含了优化器能够重新生成执行计划的所有信息，包括SQL脚本、存储大纲、绑定变量以编译环境等。Oracle同时支持手工维护SQL语句的计划历史。

为了得到更好的执行计划，系统定义了一个计划基线。执行计划基线是执行计划历史的一个子集，而执行计划基线里面的执行计划是用来与新执行计划比较的参考基准。一条SQL语句的新执行计划与计划基线里的所有计划进行比较，如果性能是最优的，则执行新的执行计划。某个SQL语句的执行计划属于计划历史，但是不一定属于计划基线。

声明日志、执行计划历史以及计划基线都存储在SQL管理库（SMB）中，它也包括了SQL概要。SMB是数据字典的一部分，存储在SYSAUX表空间中。这些元素之间的关系如图35-3所示。

图35-3  相关元素关系图

有两种方法可以将SQL语句的执行计划加载到执行计划基线中，即席捕获和块加载。

（1）即席捕获：将初始化参数OPTIMIZER_CAPTURE_PLAN_BASELINES设置为TURE，则会自动地捕获SQL的执行计划，它默认为FALSE。设置为TRUE后，它自动识别重复的SQL语句，并自动地创建其计划历史，同时也会将第一次生成的执行计划放入到计划基线中，这就构成了最初的基线。

（2）块加载：使用DBMS_SPM包手动管理计划基线。利用包提供的功能，你可以直接将SQL的执行计划从游标缓冲（Cursor Cache）或者存在的调优集中加载到计划基线里。DBMS_SPM能够改变基线计划的状态（可用和不可用），也可以将计划基线里的执行计划清除。它同样可以导出计划基线，以便于加载到其他的数据库中使用。

在计划基线的变化阶段，Oracle数据库会估计新计划的性能，并将更好性能的执行计划放到计划基线中。当优化器发现SQL语句的新执行计划时，新计划被增加到计划历史中，但是这时属性是不可接受的，当被自动或手动地证实不会造成性能的下降后，计划属性被修改为可以接受的，并且被加载到计划基线中。

有两种方式触发计划基线的变化：

（1）执行DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE函数。

例如，如下脚本返回一个报告，列出存在的历史计划是否放到了计划基线中。

variable report clob;
exec :report:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle=> ‘SYS_SQL_456sd76kjhiuykh’);
print report

（2）运行SQL调优顾问（Advisor）：在利用SQL调优顾问优化SQL脚本时，可以手动或自动地触发计划基线的变化。当调优顾问发现一个优化的计划而且证实性能优于计划基线中的一个执行计划时，则它会推荐接受这个SQL概要（Profile），当SQL概要被接受时，调优的计划也被增加到相应的计划基线中。

在DBA_SQL_PLAN_BASE_LINES视图中，包含了有关执行计划的相关信息。几个重要的属性包括：

·ORIGIN：表示执行计划的来源，如自动捕获、手动加载、手动调优和自动调优等。

·ENABLED：表示执行计划是否被启用，优化器可以使用。

·ACCEPTED：表示执行计划是否被自动或手动地确认为好的执行计划。一个执行计划只有当ENABLED而且ACCEPTED时，优化器才考虑使用。可以通过DBMS_SPM.ALTER_SQL_PLAN_BASELINE函数改变它的状态。

·FIXED：表示优化器仅仅考虑这些执行计划。例如，当计划基线中存在10个执行计划，而只有3个被设置为FIXED时，则优化器只使用这3个计划中最优的。

例如，查看执行计划信息：

select signature, sql_handle, sql_text, plan_name, origin, enabled, 
accepted, fixed, autopurge from dba_sql_plan_baselines 
where sql_text like 'select  %';

此外，可以改变执行计划属性值。例如：

variable cnt number;
exec :cnt := dbms_spm.alter_sql_plan_baseline( 
sql_handle        => 'SYS_SQL_0a9d872600ece455', 
plan_name         => 'SYS_SQL_PLAN_00ece45554bc8843', 
attribute_name    => 'ENABLED', 
attribute_value   => 'NO');

也可以清除某个执行计划。例如：

variable cnt number;
exec :cnt := dbms_spm.drop_sql_plan_baseline('SYS_SQL_6fe28d438dfc352f');

通过DBA_SQL_MANAGEMENT_CONFIG视图，可以得到SMB的空间配置和清除策略等信息，并且可以通过DBMS_SPM.CONFIGURE过程修改。例如：

SQL>exec dbms_spm.configure(‘SPACE_BUDGET_PERCENT’,20);
SQL>exec dbms_spm.configure(‘PLAN_RETENTION_WEEKS’,100);

分别设置执行计划占用SYSAUX空间的20％（默认为10%）和保留周期为100周。