点评Oracle 11g新特性之执行计划管理

摘自：http://doc.chinaunix.net/oracle/200707/156806.shtml

【内容导航】
第1页：执行计划管理的工作原理
第2页：执行计划管理的测试
摘要：本文描述了11g的新特性之一：执行计划管理，介绍了引入该新特性的原因，以及该新特性的相关特点、工作原理等。最后通过引入一个测试案例来介绍如何使用该新特性。


1. 执行计划管理的工作原理
我们知道，SQL语句的性能很大程度上依赖于SQL语句的执行计划。如果SQL语句的执行计划发生改变，则SQL的性能很有可能发生大的变化。而SQL执行计划发生变化的原因有很多种，包括优化器版本的变化、统计信息的变化、优化器相关的各种参数的变化、添加或删除了索引、添加或删除了物化视图、修改了系统设置、以及是否应用了10g引入的SQL profile等。


在11g之前，我们可以使用存储大纲（stored outline）和SQL Profile来帮助我们固定某条SQL语句的执行计划，防止由于执行计划发生变化而导致的性能下降。不过这些技术需要DBA人为的处理，比如存储大纲，需要DBA手工创建，而SQL Profile来说，则要DBA手工应用才能生效。


从11g开始，oracle引入了SQL执行计划管理（SQL Plan Management）这个新特性，从而可以让系统自动的来控制SQL语句执行计划的稳定性，进而防止由于执行计划发生变化而导致的性能下降。


通过启用该特性，某条语句如果产生了一个新的执行计划，只有在它的性能比原来的执行计划好的情况下，才会被使用。
为了实现执行计划管理，优化器会为所有执行次数超过一次的SQL语句维护该SQL语句的每个执行计划的历史列表（plan history）。优化器通过维护一个语句执行的日志条目（statement log）来识别该SQL语句是否为第二次执行。一旦优化器认出某条SQL语句为第二次执行，则优化器将该语句所生成的所有不同的执行计划插入到plan history的相关表里，而plan history里包含了优化器能够用于重新生成执行计划的所有信息，这些信息包括SQL文本、存储大纲、绑定变量以及解析环境（比如optimizer_mode之类影响优化器解析SQL语句的参数）等。


当然，11g也支持手工维护SQL语句的plan history，作为对自动维护plan history的功能补充。
但是并不是说plan history里任何的执行计划都可以拿来使用。这里需要先介绍一下所谓的执行计划基准线（plan baseline）这个概念。plan baseline是plan history的一个子集，plan baseline里面的执行计划是用来比较性能好坏的一个依据。也就是说，凭什么来判断是否可以使用一个新产生的执行计划呢？就是把该新的执行计划与plan baseline里的计划进行比较来判断。某个SQL语句的执行计划可以属于plan history，但是不一定属于plan baseline。


注意：每个SQL语句都会有它自己的执行计划历史以及执行计划基准线。


那么某个SQL语句的执行计划是如何进入执行计划历史，乃至执行计划基准线的呢？
有两种方法可以将SQL语句的执行计划纳入到执行计划管理体系中去：
1) 将初始化参数：OPTIMIZER_CAPTURE_PLAN_BASELINES设置为true，则会自动的捕获SQL的执行计划。该参数缺省为false。设置为true以后，当某条SQL语句第一次执行时，该SQL语句的plan history是空的，显然该SQL语句的plan baseline也是空的。那么当该SQL第二次再次执行时，优化器会自动将该SQL语句以及相关的执行计划放入plan history，同时也会放入到plan baseline里。这就构成了最初的plan baseline，也就是说最初进入plan history的执行计划会直接自动进入plan baseline里。那么当你做了某些修改，比如添加了一个索引等，然后第三次执行该同样的SQL语句，则会产生另外一个不同的执行计划，这时新的执行计划会自动进入plan history，但是不会自动进入plan baseline。是否使用该新的执行计划，则要把该新的执行计划与plan baseline里现存的第二次执行SQL时的执行计划进行比较，如果新的执行计划成本更低，则会使用新的执行计划，否则使用plan baseline里的执行计划。


2) 使用dbms_spm包手工处理，这可以让你手工管理SQL plan baseline。使用该包，你可以直接将SQL的执行计划从shared pool里加载到plan baseline里，也可以使用dbms_spm包将已经存在的SQL Tuning Set加载到plan baseline里。同时，dbms_spm可以让你把plan history里的执行计划加入到plan baseline里。反之，你也可以使用该包将plan baseline里的执行计划移出去。


注意，某条SQL语句的plan baseline里的第一个执行计划可以像上面说的通过设置初始化参数来自动加入，但是如果你希望在plan baseline里添加该SQL语句的其他新的执行计划时，则必须使用dbms_spm包手动完成。


那么plan baseline里的执行计划是如何被使用的呢？
Oracle提供了一个初始化参数：OPTIMIZER_USE_PLAN_BASELINES，该参数缺省为true，表示要求优化器考
虑使用plan baseline里的执行计划，如果设置为false，则不使用执行计划管理的特性，而又回到了11g之前的状况。
以下描述基于的前提是plan baseline里已经存在了一个SQL的执行计划。


每次优化器解析SQL语句的时候，首先仍然使用11g之前的传统方式产生一个成本最低的执行计划，然后看初始化参数：OPTIMIZER_USE_PLAN_BASELINES是否设置为true，如果为false，则直接返回所生成的执行计划。否则如果为ture，则去plan history里找是否存在相同的执行计划，如果找到了，则再去plan baseline里找是否存在相同的执行计划，如果也找到了，则直接返回该执行计划。如果在plan history里没找到相同的执行计划，则将产生的执行计划加入到plan history里，然后将执行计划与plan baseline里已经存在的执行计划进行比较，看哪个执行计划的成本低就取哪个执行计划。


如果你为某个SQL语句保存了存储大纲，则为了向下兼容，该语句会使用存储大纲。另外，即使你通过设置初始化参数：OPTIMIZER_CAPTURE_PLAN_BASELINES为true来启动自动捕获执行计划到plan baseline里，对于使用存储大纲所生成的执行计划也不会放入plan baseline里。

SQL语句的plan history以及plan baseline所涉及到的表是存放在SQL Management Base (SMB)里的，SMB里同样也存放了SQL Profiles。而SMB是数据字典的一部分，存放在SYSAUX表空间里。SMB所占用的空间会自动定期的删除。

2. 执行计划管理的测试
Oracle 11g提供了一个视图：dba_sql_plan_baselines，可以在该视图里查询某条SQL语句相关的plan
history以及plan baseline。我们来看下面的例子。
首先创建一个测试表。

SQL> create table t1(skew number,padding varchar2(100));
SQL> insert into t1 select rownum,object_name from dba_objects;
SQL> commit;
SQL> set autotrace traceonly exp stat;
SQL> select * from t1 where skew=200;
SQL> select * from t1 where skew=200; 

尽管执行两次，但是这时去查询dba_sql_plan_baselines，试图找到SQL文本为select * from t1 where skew=200的记录时，会发现没有记录，因为optimizer_capture_sql_plan_baselines缺省为false。我们将该参数设置为true以后继续测试。

SQL> alter session set optimizer_capture_sql_plan_baselines=true;
SQL> select * from t1 where skew=200; --全表扫描 
SQL> select * from t1 where skew=200; --全表扫描 
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted, autopurge
from dba_sql_plan_baselines where sql_text like 'select * from t1 where skew=200';
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC AUT
---------- ------------------------ ----------------------------- ----------- --- --- --- 
1.2376E+19 SYS_SQL_abc0a2c042fa089c SYS_SQL_PLAN_42fa089c844cb98a AUTO-CAPTURE YES YES YES 

我们可以看到，文本为“select * from t1 where skew=200”的SQL语句在plan history里产生了一个执行计划。其中，sql_handle表示SQL语句的句柄；plan_name则表示该SQL的执行计划的名字；origin表示该执行计划是如何进入plan history的，该列值为AUTO-CAPTURE则说明是由优化器自动加入的，如果为MANUAL则说明是由DBA手工加入的；enabled表示是否被启用了，YES表示启用，NO表示禁用。如果某个执行计划为禁用，则优化器根本就不会考虑使用该执行计划；accepted表示是否接受，也就是是否进入了plan baseline。我们看到这里的accepted为YES，说明该SQL的执行计划进入了plan baseline里；autopurge表示该执行计划是否为定期自动删除，YES表示是，NO表示否。

我们继续测试，在skew上添加一个索引，从而让原来的SQL不走全表扫描，而改走索引扫描。

SQL> create index idx_t1 on t1(skew);
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);
SQL> select * from t1 where skew=200; --索引扫描 
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,fixed,autopurge
from dba_sql_plan_baselines where sql_text like 'select * from t1 where skew=200';
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC AUT
---------- ------------------------ ----------------------------- ----------- --- --- --- 
1.2376E+19 SYS_SQL_abc0a2c042fa089c SYS_SQL_PLAN_42fa089c844cb98a AUTO-CAPTURE YES YES YES
1.2376E+19 SYS_SQL_abc0a2c042fa089c SYS_SQL_PLAN_42fa089cdbd90e8e AUTO-CAPTURE YES NO YES

这时我们可以看到，dba_sql_plan_baselines视图里多了一个执行计划，也就是我们后面那个使用了索引扫描的执行计划。而该执行计划的accepted为NO，说明该计划并没有进入plan baseline里，但是进入了plan history里。
这时，我们可以通过调用dbms_spm包来手工将走索引的执行计划加入到plan baseline里。如下所示，将accepted改为YES。

SQL> dbms_spm.alter_sql_plan_baseline(
sql_handle => 'SYS_SQL_abc0a2c042fa089c',
plan_name => 'SYS_SQL_PLAN_42fa089c844cb98a',
attribute_name => 'ACCEPTED',
attribute_value => 'YES'); 

然后再次查询dba_sql_plan_baselines视图，可以发现后面的执行计划的accepted变为了YES。

SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,fixed,autopurge
from dba_sql_plan_baselines where sql_text like 'select * from t1 where skew=200';
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC AUT
---------- ------------------------ ----------------------------- ----------- --- --- --- 
1.2376E+19 SYS_SQL_abc0a2c042fa089c SYS_SQL_PLAN_42fa089c844cb98a AUTO-CAPTURE YES YES YES
 1.2376E+19 SYS_SQL_abc0a2c042fa089c SYS_SQL_PLAN_42fa089cdbd90e8e AUTO-CAPTURE YES YES YES

如果我们要手工删除plan baseline里的执行计划，则可以调用dbms_spm里的存储过程来实现。
SQL> var cnt number;
SQL> exec :cnt := dbms_spm.purge_sql_plan_baseline('SYS_SQL_abc0a2c042fa089c');

删除指定SQL语句的执行计划以后，再去查询dba_sql_plan_baselines就会发现上面测试SQL语句的执行计划不存在了。

从上面的描述可以看出，SQL Plan Management特性的主要作用就是通过引入一个SQL plan baseline，从而保证SQL语句执行计划的稳定，进而保证系统性能的稳定。本质上，它属于11g之前的存储大纲的升级版，而且是自动实现的，不需要人工干预。