oracle的sql plan详解,Oracle 11g SQL性能的新特性（三）- SQL Plan Management

执行计划的选择过程

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

1.首先，无论是否存在baseline，oracle都会正常进行硬解析或者软解析，为SQL生成一个执行计划。 由于ACS和bind peeking的作用，存在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便可

执行计划的演化(evolution)

执行计划的演化指Plan History里的执行计划从NON-ACCEPTED，变成ACCEPTED的过程。 如果上所述，由于ACS和Bind Peeking的作用，存在baseline的SQL有可能生成新的执行计划，被保存到Plan History中。 Oracle提供了API，通过自动或手工的方式，将一个计划标记为ACCEPTED，这个计划就会被后续的执行所选择。

使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE这个API来控制执行计划的演化。语法：DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (sql_handle IN VARCHAR2 := NULL,--&gt NULL 表示针对所有SQLplan_name  IN VARCHAR2 := NULL,time_limit IN INTEGER  := DBMS_SPM.AUTO_LIMIT,verify     IN VARCHAR2 := 'YES',commit     IN VARCHAR2 := 'YES' )RETURN CLOB;

这里由两个标记控制：o Verify+ YES (只有性能更好的计划才会被演化)+ NO (演化所有的计划)o Commit+ YES (直接演化)+ NO (只生成报告)

这里可以通过不同的排列组合，达到不同的效果：o 自动接收所有性能更好的执行计划 (Verify->YES, Commit->YES)o 自动接收所有新的执行计划 (Verify->NO, Commit->YES)o 比较性能，生成报告，人工确认是否演化 (Verify->NO, Commit->NO)

* 对于性能的验证的方式，oracle会去实际执行来比较buffer gets

修改已有的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;DECLAREx NUMBER;BEGINx := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle      => '&&sql_handle',plan_name       => '&&plan_name',attribute_name  => 'FIXED',attribute_value => 'YES' );END;/

常见应用

o 我们常见的一个场景是，一条SQL在使用hint时会生成一个好的计划，我们需要以此在原SQL上创建一个baseline。 具体方法请参加note 787692.1

注意

o 当您使用多种方式控制执行计划时：+ Stored Outline存在时，它具有最高的优先级。+ 已经实施的SQL profile会被自动加入到SQL plan baseline中+ STA(SQL Tuning Advisor) 会自动接收新的profile，意味着它会生成新的baselineo 如果可能话，尽量移植到SPM，混合多种方式会变得复杂

相关参数

optimizer_capture_sql_plan_baselinesoptimizer_use_sql_plan_baselinescreate_stored_outlineuse_stored_outlines