SQL Plan Baseline执行计划基线总结

1.SQL Plan Baseline（执行计划基线）概论

 

    如果应用程序中强行加了hints（提示），可以使用SQL Profile来稳定执行计划，除了使用SQL Profile方式，还可以使用SQL Plan Baseline（执行计划基线），该特性从11g开始支持，同时取代了10g之前的stored outline功能，因为stored outline（存储提纲）不够智能，而且其操作也比较繁琐。

 

主要作用：

    稳定给定SQL语句的执行计划，防止执行环境或对象统计信息等等因子的改变对SQL语句的执行计划产生影响！

     减少数据库中出现SQL语句性能退化的概率，理论上不允许一条语句切换到一个比已经执行过的执行计划慢很多的新的执行计划上！

    SQL执行计划基线保存在数据字典中，查询优化器会自动判断使用他们。

 

2.工作机制

    从Oracle 11g开始，由于基线的存在，一条语句的解析过程大概如下：

1> SQL语句被硬解析后，CBO（优化器）会产生很多个的执行计划，CBO从中选择一个成本最低执行计划。

2> 基于SQL语句的文本形成一个哈希值（signature)，通过这个哈希值来检查数据字典中是否存在同样的基线。

3> 如果基线存在，优化器会对刚刚产生的执行计划和保存在SQL plan baseline中的执行计划进行比较。

4> 如果基线中有与CBO刚产生的执行计划匹配的SQL执行计划存在，并且被标记为可接受（‘accepted’），则这个CBO生成的执行计划被启用。

5> 如果基线中没有匹配的SQ执行计划存在，CBO评估基线中被标记为‘accepted’的的多个执行计划，并选择其中cost最低的执行计划。（注意，一个语句的基线可以有多个执行计划被保存，这是与其他Outline和SQL profiel都不同的地方）

6> 如果刚刚硬解析过程中CBO选择的执行计划比保存在基线中的执行计划COST都低，这个新生成的执行计划被标记为‘not-accepted’并保存在基线中。直到这个执行计划被演化且验证后才会被考虑使用，即标记为accepted(演化和验证，可以简单理解为Oracle确认这个执行计划可以带来更好的性能)。

 

3.基线的一些特点

1> 通过OPTIMIZER_USE_SQL_PLAN_BASELINE来控制Oracle是否使用基线，默认值为TRUE，即会自动使用基线。

2> 11g中默认是不会自动创建基线

3> 与OUTLINE和SQL Profile不同，基线中不存在分类的概念

4> 与OUTLINE和SQL Profile不同，每个SQL语句可以有多个基线。Oracle根据制定的规则来判断具体是否哪个基线

5> 基线针对RAC中所有的实例都生效

6> 基线有两个表示，一个为sql_handle，可以理解为表示语句文本的唯一标识，一个为sql_plan_name可以理解为执行计划的唯一标识

7> 不能像sql profile一样通过force_matching属性将字面值不一样的SQL语句使用一个基线应用多个语句。

 

4.创建基线的几种方式

 

1> 自动捕获基线，通过将optimizer_cature_sql_plan_baselines设置为true，优化器会重复执行两次以上的SQL语句生成并保存基线（可以系统级或会话级修改）

 

 2> 从SQL调优集合中加载，通过使用包dbms_spm.load_plans_from_sqlset来从SQL调优集合中加载基线

 

DECLARE 

  l_plans_loaded  PLS_INTEGER;

 BEGIN

   l_plans_loaded := DBMS_SPM.load_plans_from_sqlset(

     sqlset_name => 'my_sqlset');

 END;

/

 

3> 从库缓存中加载，通过包dbms_spm.load_plans_from_cursor_cache函数为一条已经在游标缓存中的语句创建基线

 

DECLARE 

  l_plans_loaded  PLS_INTEGER;

 BEGIN

   l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '84nabsvnwf9kb',plan_hash_value=>null);

 END;

 /

select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

 

备注：可以有多种方式加载，例如可以根据sql文本进行模糊匹配、SQL语句解析的用户名等等方式

 

5.基线的几种状态

一个SQL语句对应的基线，我将它们归纳为三种状态

 accepted（可接受），只有这种状态的基线，优化器才会考虑此基线中的执行计划

 

no-accepted（不可接受），这种状态的基线，优化器在SQL语句解析期间不会考虑。这种状态的基线必须通过演化和验证通过后，转变为accepted状态后，才会被优化器考虑使用

 

fixed为yes（固定），这种状态的基线固有最高优先级！比其他两类基线都要优先考虑

 

6.查看基线

 

1> 基本视图：dba_sql_plan_baselines、dba_sql_management_config

sys@ORCL>select SQL_HANDLE,PLAN_NAME from dba_sql_plan_baselines;

SQL_HANDLE                     PLAN_NAME

------------------------------ ------------------------------------------------------------

SQL_17dc586bdecfee90           SQL_PLAN_1gr2sdggczvnhc74815fa

 

2> 底层视图：sqlobj$data 、 sqlobj$  （保存具体的hint），如下查看基线中保存的执行计划语句：

 

select extractvalue(value(d), '/hint') as outline_hints from

         xmltable('/outline_data/hint' passing

                   (select  xmltype(comp_data) as xmlval from

                            sqlobj$data sod, sqlobj$ so

                                     where so.signature = sod.signature

                                     and so.plan_id = sod.plan_id

                                     and comp_data is not null

                                     and name like '&baseline_plan_name'

                            )

 ) d;

 

3> 通过函数来查看基线的详细信息：

 

select * from table (dbms_xplan.display_sql_plan_baseline (sql_handle => 'SQL_17dc586bdecfee90',plan_name => 'SQL_PLAN_1gr2sdggczvnhc74815fa'));

 

 

7.演化基线

    为了验证基线中一个处于不可接受状态的执行计划是否比一个处于可接受状态的执行计划具有更高的效率，必须通过演化来验证，需要让优化器以不同的执行计划来执行这条SQL语句，观察不可接受状态的执行计划基线是否会带来更好的性能，如果性能确实更高，这个不可接受状态的基线将会转换为可接受状态。

    演化的方式有两种：

 1> 手工执行运行

 SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_17dc586bdecfee90') From dual;

 ----还有time_limit/verify/commit几个参数

 

2> 调优包实现基线的自动演化，可以理解为，启动一个调度任务，周期性的检查是否有不可接受状态的基线可以被演化

 

8.修改基线

    可以通过dbms_spm.alter_sql_plan_baseline包来修改基线的一些属性，主要有如下几个属性：

ENABLED：设置该属性的值为NO告诉Oracle 11g临时禁用某个计划，一个SQL计划必须同时标记为ENABLED和ACCEPTED，否则CBO将忽略它

FIXED：设置为YES，那个计划将是优化器唯一的选择[最高优先级]，即使如果某个计划可能拥有更低的成本。这让DBA可以撤销SMB的默认行为，对于转换一个存储概要进入一稳定的SQL计划基线特别有用，注意当一个新计划被添加到被标记为FIXED的SQL计划基线，该新计划不能被利用除非它申明为FIXED状态

AUTOPURG：设置这个属性的值为NO告诉Oracle 11g无限期保留它，从而不用担心SMB的自动清除机制

plan_name : 改变SQL plan 名字

description : 改变SQL plan描述

 

语法：

 

SET SERVEROUTPUT ON

 

DECLARE

   v_text  PLS_INTEGER;

 BEGIN

   v_text  := DBMS_SPM.alter_sql_plan_baseline(sql_handle  => 'SYS_SQL_xxxxxx',plan_name => 'SYS_SQL_PLAN_xxxxxxxxx',

       attribute_name  => 'fixed',attribute_value => 'YES');

   DBMS_OUTPUT.put_line('Plans Altered: ' || v_text  );

END;

 /

 

var rtn number

exec :rtn :=dbms_spm.load_plans_from_cursor_cache(sql_id =>'1zy136qv3g3k9',sql_handle => 'SQL_17dc586bdecfee90');

 

 

9.迁移基线

dbms_spm提供了多个过程来在数据库之间迁移SQL计划基线

1.create_stgtab_baseline创建一个计划基线保存表

2.pack_stgtab_baseline将基线从数据字典复制到第一步的表中

3.unpack_stgtab_baseline将基线从保存表中复制到迁移数据库的数据字典中

 

大概过程如下：

1> 创建一张保存数据字典中基线表内容的用户表

exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TEST',table_owner => 'SCOTT',tablespace_name =>'');

 

2> 将数据字典中基线表的内容 插入到 第一步创建的用户表中

var i number

exec :i := dbms_spm.pack_stgtab_baseline(table_name => 'BASELINE_TEST', table_owner => 'SCOTT');

 

备注：可以支持多种方式插入,例如包含特定字符的SQL相关的基线，sql_handle来精确识别一个基线

 

3> 通过迁移工具迁移用户表

exp/imp or expdp/impdp

 

4> 将迁移过来的用户表中保存的基线内容 插入到当前库的数据字典中，从而实现迁移

exec :i := dbms_spm.unpack_stgtab_baseline(table_name => 'BASELINE_TEST',table_owner => 'SCOTT');

 

备注：可以支持多种方式，与步骤2一样

 

10.删除基线

1> 可以通过dbms_SPM.drop_sql_plan_baseline包来手工删除数据字典里的基线

select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

sys@ORCL>var rtn number

sys@ORCL>exec :rtn :=dbms_spm.drop_sql_plan_baseline(plan_name => 'SQL_PLAN_1gr2sdggczvnhc74815fa');

 

2> 为使用的基线，fixed为no的基线，将在一定的保留期后自动删除(可查看dba_sql_management_config视图）

 

手工删除方法如下

select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

 

SET SERVEROUTPUT ON

DECLARE

  v_text PLS_INTEGER;

BEGIN

  v_text := DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SQL_17dc586bdecfee90',plan_name => NULL);

  DBMS_OUTPUT.put_line(v_text);

END;

/

select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;

 

11.将一个SQL语句固定为我们期望的执行计划

 

一般通过如下几步实现（仅供参考）

1> 为这个SQL语句创建基线

2> 给这个SQL语句添加hint，确保SQL语句添加hint后的执行计划与我们期望一样

3> 将第2步产生的执行计划，添加到第一步创建的基线中（注意，前面已经说过，一个SQL语句可以有多个基线！）

4> 删除基线中第1步创建的那个执行计划（这样，我们就可以确保基线中只有我们期望的执行计划，即保存第2步SQL语句的执行计划）

5> 验证是否生效

 

 

 

 

reference                                                        http://www.th7.cn/db/Oracle/201407/61054.shtml

 

 

 

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

转载于:http://blog.itpub.net/30130773/viewspace-2121227/