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自动捕捉

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

2.2 批量导入

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

1. 从 SQL Tuning Set STS 导入：DBMS_SPM.LOAD_PLANS_FROM_SQLSET
2. 从Cursor Cache中装载：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
3. 从Stored Outlines中导入： DBMS_SPM.MIGRATE_STORED_OUTLINE
4. 从staging table表中导入：dbms_spm.create_stgtab_baseline
5. 通过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就会起作用。

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