Oracle 11g 执行计划管理1

1. 执行计划管理的工作原理

1.1控制执行计划的稳定性

11g之前，可以使用存储大纲（stored outline）和SQL Profile来固定某条SQL语句的执行计划，防止由于执行计划发生变化而导致的性能下降.
11g开始，oracle引入了SQL执行计划管理，从而可以让系统自动的来控制SQL语句执行计划的稳定性，进而防止由于执行计划发生变化而导致的性能下降

1.2 11g执行计划管理

优化器会为所有执行次数超过一次的SQL语句维护该SQL语句的每个执行计划的历史列表（plan history）。
优化器通过维护一个语句执行的日志条目（statement log）来识别该SQL语句是否为第二次执行。一旦优化器认出某条SQL语句为第二次执行，则优化器将该语句所生成的所有不同的执行计划插入到plan history的相关表里。
准线（plan baseline）是plan history的一个子集，plan baseline里面的执行计划是用来比较性能好坏的一个依据。

　　凭什么来判断是否可以使用一个新产生的执行计划呢？就是把该新的执行计划与plan baseline里的计划进行比较来判断。某个SQL语句的执行计划可以属于plan history，但是不一定属于plan baseline。

1.3 相关名词说明

SQL Plan Management（SPM）：oracle11g 中提供的新特性，用来更好地控制执行计划。
Plan History：优化器生成的所有执行计划的总称。
SQL Plan Baseline： Plan History里那些被标记为“ACCEPTED”的执行计划的总称。
Plan Evolution：把一条执行计划从Plan History里标记为“ACCEPTED”的过程。
SQL Management Base（SMB）：字典表里保存的执行计划的总称，包括Plan History，SQL Plan Baseline和SQL profile

2.SPM如何捕捉（加载）执行计划

2.1自动捕捉

首先把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES设置成TRUE。
从这个时刻开始，所有执行两次以上的SQL语句会被观测，执行计划会进入Plan History。有个别例外的，参见note 788853.1。
生成的第一个执行计划被标记为ENABLED并且是ACCEPTED，后续的执行计划会被标记为ENABLED但不是ACCEPTED。
这时把OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES设置会FALSE，新的语句将不会创建Baseline。
需要注意的是，即使关闭了自动捕捉，针对存在baseline的SQL，由于ACS（自适应游标共享）的作用，仍旧会有新的PLAN生成，新的Plan仍会进入Plan History，标记为ENABLED但不是ACCEPTED。

2.2 批量导入

导入的baseline都会被自动标记为ACCEPTED， Oralce提供六种方式把计划导入到sql plan baseline中：

从 SQL Tuning Set STS 导入：DBMS_SPM.LOAD_PLANS_FROM_SQLSET
从Cursor Cache中装载：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
从Stored Outlines中导入： DBMS_SPM.MIGRATE_STORED_OUTLINE
从staging table表中导入：dbms_spm.create_stgtab_baseline
通过staging table从另外一个系统中移植：

DBMS_SPM.CREATE_STGTAB_BASELINE
DBMS_SPM.PACK_STGTAB_BASELINE
DBMS_SPM.UNPACK_STGTAB_BASELINE

3.执行计划的选择过程

在OPTIMIZER_USE_SQL_PLAN_BASELINES被设置成默认值TRUE，SQl Plan Baseline就会起作用。

首先，无论是否存在baseline，oracle都会正常进行硬解析或者软解析，为SQL生成一个执行计划。由于ACS和bindpeeking的作用，存在baseline的SQL有可能在这时生成一个不同于baseline的执行计划。
如果baseline不存在，就按生成的计划执行。如果baseline存在，那么要查看history里是否有这个计划，如果没有，就将这个计划插入，并标记为ENABLED,NON-ACCEPTED.
在baseline中查看是否有FIXED的计划存在，如果存在，执行FIXED的计划，如果存在多个FIXED的计划，根据统计信息重新计算cost，选择cost小的那个。
如果FIXED的计划不存在，就选择ACCEPTED的计划执行。如果存在多个ACCEPTED的计划，根据统计信息重新计算cost，选择cost小的那个。

注意：这里每次重新计算cost的代价不大，因为执行计划是已知的，优化器不必遍历所有的可能，只需根据算法计算出已知计划的cost便可。

4.执行计划的演化(evolution)

　　演化的过程就是把non-accepted 的plan 改成accepted的过程。对于手工load的执行计划，会自动执行evolving的过程，因此默认就是accepted；而对于自动装载的执行计划，就需要使用EVOLVE_SQL_PLAN_BASELINE函数来实现演化过程

使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE这个API来控制执行计划的演化。语法：

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (

  sql_handle IN VARCHAR2 := NULL, --> NULL表示针对所有SQL

  plan_name  IN VARCHAR2 := NULL,

  time_limit IN INTEGER  := DBMS_SPM.AUTO_LIMIT,

  verify     IN VARCHAR2 := 'YES',

  commit     IN VARCHAR2 := 'YES' )

RETURN CLOB;

这里由两个标记控制：

（1）Verify：

  + YES (只有性能更好的计划才会被演化)

  + NO (演化所有的计划)

（2）Commit：

  + YES (直接演化)

  + NO (只生成报告)

这里可以通过不同的排列组合，达到不同的效果：

（1）自动接收所有性能更好的执行计划(Verify->YES, Commit->YES)

（2）自动接收所有新的执行计划 (Verify->NO,Commit->YES)

（3）比较性能，生成报告，人工确认是否演化(Verify->NO, Commit->NO)

5.修改已有的Baseline

通过DBMS_SPM.ALTER_SQL_PLAN_BASELINE来完成。

语法：

DBMS_SPM.ALTER_SQL_PLAN_BASELINE (

  sql_handle      IN VARCHAR2 := NULL,

  plan_name       IN VARCHAR2 := NULL,

  attribute_name  IN VARCHAR2,

  attribute_value IN VARCHAR2 )

RETURN PLS_INTEGER;

如把某个baseline 标记为FIXED：

SET SERVEROUT ON;

DECLARE

  x NUMBER;

BEGIN

  x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (

    sql_handle      => '&&sql_handle',

    plan_name       => '&&plan_name',

    attribute_name  => 'FIXED',

    attribute_value => 'YES' );

END;

/

整理自：

http://tech.it168.com/db/2007-07-23/200707231104640.shtml

http://blog.****.net/tianlesoftware/article/details/8296524

秒客网