11g新特性sql plan baseline

11g推荐baseline代替原来的outline

 

下面的官方文档解释了baseline和outline的工作原理差异

102

You can maintain the existing execution plan of SQL statements over time either using stored statistics or stored SQL execution plans. Storing optimizer statistics for tables will apply to all SQL statements that refer to those tables. Storing an execution plan (that is, plan stability) maintains the plan for a single SQL statement. If both statistics and a stored plan are available for a SQL statement, then the optimizer uses the stored plan.

http://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_1016.htm#i23988

112

You can maintain the existing execution plan of SQL statements over time either using stored statistics or SQL plan baselines. Storing optimizer statistics for tables will apply to all SQL statements that refer to those tables. Storing an execution plan as a SQL plan baseline maintains the plan for set of SQL statements. If both statistics and a SQL plan baseline are available for a SQL statement, then the optimizer first uses a cost-based search method to build a best-cost plan, and then tries to find a matching plan in the SQL plan baseline. If a match is found, then the optimizer proceeds using this plan. Otherwise, it evaluates the cost of each of the accepted plans in the SQL plan baseline and selects the plan with the lowest cost.

http://docs.oracle.com/cd/E11882_01/server.112/e16638/sql_overview.htm#i23988

 

简述一下baseline概念及其原理 

why

可以升级数据库或上线新sql时稳固执行计划

 

What

可包含多个执行计划，由hint/plan hash value以及相关运行信息组成；

只对重复运行的sql维护plan history(通过statement log辨别重复的sql)，包含sql text/outline/绑定变量和编译环境，可分为accepted(baseline)和unaccepted

 

How

数据库可自动监测plan change并将其同已有的Baseline比较，如果不会降低性能则添加至baseline，否则添加至baseline history(unaccepted)；

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES =true时自动运行，默认false；

也可从AWR/shared sql area/tuning set挑选已有的plan手工创建，不必验证便可直接加入baseline(accepted)；

  my_plans PLS_INTEGER;

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1');

  my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd');

 

使用baseline时每次编译sql都要实时生成一个当前最优解，当其与baseline不匹配时(unaccepted)，转而遍历baseline并找出最优解；

如果系统变动(如删除索引)导致baseline不可用，优化器会选择当前最优解；

OPTIMIZER_USE_SQL_PLAN_BASELINES =true(默认值)激活使用baseline

 

可调用API将Baseline导出到stage表，然后通过expdp/impdp导入其他数据库

 

Fixed plan

不可被更改，属性fixed=YES；优先被选用；手工加入的plan默认为non-fixed，必须设置为fixed才能被选用；

包含fixed plan的baseline称为fixed baseline

 

协同sql tuning advisor

如果advisor发现比已有baseline更优的plan，会推荐接受sql profile，接受后将其加入baseline；

11g advisor被封装成job可自动运行，

http://docs.oracle.com/cd/E11882_01/server.112/e16638/optplanmgmt.htm#BABEAFGG

 

sql管理基地(management base)

位于sysaux表空间，存储statement log/ plan history/ baseline/ sql profile，定时清除没有使用的plan和log；

 

 

迁移outline至baseline

Why

相比baseline，outline有一系列不足

不能自动改进，一旦使用outline执行计划就会固定，即便当前有更优选项也无法使用；

如果引用的hint失效，将其剔除并继续使用该outline；

区别

Outline为hint集合，baseline为plan集合；

一个sql可包含多个outline(分属不同category)，而baseline只有一个category(default)，但可有多个module；

 

How

迁移完成后将create_stored_outline=false，确保今后不再创建outline

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-759562/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15480802/viewspace-759562/