Oracle SQL执行计划基线总结（SQL Plan Baseline）

Oracle SQL执行计划基线总结（SQL Plan Baseline）

一、基础概念 Oracle 11g开始，提供了一种新的固定执行计划的方法，即SQL plan baseline，中文名SQL执行计划基线（简称基线），可以认为是OUTLINE（大纲）或者SQL PROFILE的改进版本，基本上它的主要作用可以归纳为如下两个： 1、稳定给定SQL语句的执行计划，防止执行环境或对象统计信息等等因子的改变对SQL语句的执行计划产生影响！ 2、减少数据库中出现SQL语句性能退化的概率，理论上不允许一条语句切换到一个比已经执行过的执行计划慢很多的新的执行计划上！ 注意： 1、从Oracle的发展角度来看，估计这种方法是Oracle发展和改进的方向，如今outline已经被废弃，sql profile估计在后续的发行版本中也难有改进，因此，对于从11g开始接触Oracle的朋友来说，一定要对sql计划基线有所了解，因为这是以后的主流！ 2、SQL执行计划基线保存在数据字典中，查询优化器会自动判断使用他们。
二、工作机制 从Oracle 11g开始，由于基线的存在，一条语句的解析过程大概如下： Oracle 就是通过上面这种方式来确保SQL语句的性能不会退化（即第一部分中我归纳的第二个主要作用），称为“执行计划保守选择策略”

三、基线的一些特点 简单归纳如下几个 三、创建基线的几种方式 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 => '1fkh93md0802n',plan_hash_value=>null); END; / 备注：可以有多种方式加载，例如可以根据sql文本进行模糊匹配、SQL语句解析的用户名等等方式，具体见文档
四、基线的几种状态 一个SQL语句对应的基线，我将它们归纳为三种状态 五、查看基线 1、基本视图：dba_sql_plan_baselines、dba_sql_management_config 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=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja1790cce5f0e'));
六、演化基线 为了验证基线中一个处于不可接受状态的执行计划是否比一个处于可接受状态的执行计划具有更高的效率，必须通过演化来验证，需要让优化器以不同的执行计划来执行这条SQL语句，观察不可接受状态的执行计划基线是否会带来更好的性能，如果性能确实更高，这个不可接受状态的基线将会转换为可接受状态。演化的方式有两种： 1、手工执行运行 SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_xxxxxxxxxxxxx') From dual; 还有time_limit/verify/commit几个参数，可以参考文档 2、调优包实现基线的自动演化，可以理解为，启动一个调度任务，周期性的检查是否有不可接受状态的基线可以被演化
七、修改基线 可以通过dbms_spm.alter_sql_plan_baseline包来修改基线的一些属性，主要有如下几个属性 语法： 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; /
八、迁移基线 dbms_spm提供了多个过程来在数据库之间迁移SQL计划基线 大概过程如下： 1、创建一张保存数据字典中基线表内容的用户表 exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TEST',table_owner => 'SCOTT',tablespace_name =>''); 2、将数据字典中基线表的内容 插入到 第一步创建的用户表中 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一样，具体见文档
九、删除基线 手工删除方法如下 SET SERVEROUTPUT ON DECLARE   v_text  PLS_INTEGER; BEGIN   v_text := DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9',plan_name  => NULL);    DBMS_OUTPUT.put_line(v_text); END; /
十、将一个SQL语句固定为我们期望的执行计划 我一般通过如下几步实现（仅供参考） 1、为这个SQL语句创建基线 2、给这个SQL语句添加hint赖宇星，确保SQL语句添加hint后的执行计划与我们期望一样 3、将第2步产生的执行计划，添加到第一步创建的基线中（注意，前面已经说过，一个SQL语句可以有多个基线！） 4、删除基线中第1步创建的那个执行计划（这样，我们就可以确保基线中只有我们期望的执行计划，即保存第2步SQL语句的执行计划） 5、验证是否生效 后续有示例，加深理解！
十一、示例（将一个SQL语句固定为我们期望的执行计划） 首先运行两个结构相同的语句，下面的实验通过SQL计划基线，将一个语句的执行计划通过另一个语句的执行计划来固定 SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines; 未选定行 SQL> alter system flush shared_pool; 系统已更改。 SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;         ID NAME                           TYPE ---------- ------------------------------ ---------------        711 I_STREAMS_PROCESS_PARAMS1      INDEX SQL> select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where id=711;         ID NAME                           TYPE ---------- ------------------------------ ---------------        711 I_STREAMS_PROCESS_PARAMS1      INDEX
SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time   2   from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%'; SQL_TEXT                                                SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME ------------------------------------------------------- ------------- ---------- ------------ --------------- -------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548            0      1845196118 12:27:31 h_stat where id=711 select /* outlinetest3 */ /*+ index(dh_stat) */ * from  fm35jcmypb3qu 4250242778            0      2780970545 12:27:41 dh_stat where id=711
SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID  4vaj9fgjysy9c, child number 0 ------------------------------------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711 Plan hash value: 1845196118 ----------------------------------------------------------------------------- | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |         |       |       |   124 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_STAT |     1 |    38 |   124   (1)| 00:00:02 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("ID"=711) 已选择19行。
SQL>  select * from table(dbms_xplan.display_cursor('fm35jcmypb3qu','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID  fm35jcmypb3qu, child number 0 ------------------------------------- select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where id=711 Plan hash value: 2780970545 --------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ID"=711) 已选择20行。

SQL> DECLARE   2   k1 pls_integer;   3  begin   4  k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (   5  sql_id=>'4vaj9fgjysy9c',   6  plan_hash_value=>1845196118   7  );   8  end;   9  / PL/SQL 过程已成功完成。
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                                        ACC ------------------------------ ------------------------------ ------------------------------------------------------- --- SYS_SQL_11bcd50cd51504e9       SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES                                                               h_sta
刚生产sql plan baseline的时候，第一次查询，无法找到执行计划，直到第二次执行的时候，才能看到，如下 SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;         ID NAME                           TYPE ---------- ------------------------------ ---------------        711 I_STREAMS_PROCESS_PARAMS1      INDEX SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','','')); PLAN_TABLE_OUTPUT --------------------------------------------------------- SQL_ID: 4vaj9fgjysy9c cannot be found SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time   2   from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%'; SQL_TEXT                                                SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME ------------------------------------------------------- ------------- ---------- ------------ --------------- -------- select /* outlinetest3 */ /*+ index(dh_stat) */ * from  fm35jcmypb3qu 4250242778            0      2780970545 12:27:41 dh_stat where id=711

SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;         ID NAME                           TYPE ---------- ------------------------------ ---------------        711 I_STREAMS_PROCESS_PARAMS1      INDEX SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,'hh24:mi:ss') time   2   from v$sql a where sql_text like '%outlinetest%' and sql_text not like '%v$sql%';
SQL_TEXT                                                SQL_ID        HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME ------------------------------------------------------- ------------- ---------- ------------ --------------- -------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548            0      1845196118 12:30:54 h_stat where id=711 select /* outlinetest3 */ /*+ index(dh_stat) */ * from  fm35jcmypb3qu 4250242778            0      2780970545 12:27:41 dh_stat where id=711

SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID  4vaj9fgjysy9c, child number 0 ------------------------------------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711
Plan hash value: 1845196118 ----------------------------------------------------------------------------- | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |         |       |       |   124 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_STAT |     1 |    38 |   124   (1)| 00:00:02 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
   1 - filter("ID"=711) Note -----    - SQL plan baseline SQL_PLAN_13g6p1maja17934f41c8d used for this statement 已选择23行。
将符合我们预期的执行计划的加载到第一次生成的sql baseline中！ SQL> DECLARE   2   k1 pls_integer;   3  begin   4  k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (   5  sql_id=>'fm35jcmypb3qu',   6  plan_hash_value=>2780970545,sql_handle=>'SYS_SQL_11bcd50cd51504e9'   7  );   8  end;   9  /
PL/SQL 过程已成功完成。
可以看到，SYS_SQL_11bcd50cd51504e9下目前有两个plan_name SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                                        ACC ------------------------------ ------------------------------ ------------------------------------------------------- --- SYS_SQL_11bcd50cd51504e9       SQL_PLAN_13g6p1maja1790cce5f0e select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES                                                               h_sta
SYS_SQL_11bcd50cd51504e9       SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES                                                               h_sta 删除第一个plan_name，即将我们不需要的执行计划版本去除掉！ SQL> DECLARE   2   k1 pls_integer;   3  begin   4  k1 := DBMS_SPM.drop_sql_plan_baseline (  sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja17934f41c8d');   5  end;   6  /
PL/SQL 过程已成功完成。


通过下面的一部分测试，我们可以看到，新的SQL计划基线已经正常生效，及时语句中包含full提示，执行计划也走索引定位数据 SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711;         ID NAME                           TYPE ---------- ------------------------------ ---------------        711 I_STREAMS_PROCESS_PARAMS1      INDEX
SQL> select * from table(dbms_xplan.display_cursor('4vaj9fgjysy9c','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID  4vaj9fgjysy9c, child number 1 ------------------------------------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711 Plan hash value: 2780970545 --------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ID"=711) Note -----    - SQL plan baseline SQL_PLAN_13g6p1maja1790cce5f0e used for this statement 已选择24行。

可以通过dba_sql_plan_baselines来显示可用的SQL计划基线的一般信息，也可以通过如下这种方式显示执行SQL计划基线的详细信息！ select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja1790cce5f0e'));
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL handle: SYS_SQL_11bcd50cd51504e9 SQL text: select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where           id=711 --------------------------------------------------------------------------------
-------------------------------------------------------------------------------- Plan name: SQL_PLAN_13g6p1maja1790cce5f0e         Plan id: 214851342 Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD --------------------------------------------------------------------------------
Plan hash value: 2780970545
--------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |     1 |    38 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| DH_STAT |     1 |    38 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | IND_1   |     1 |       |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
   2 - access("ID"=711) 已选择26行。
查看SQL计划基线中保存的hint提示集合 SQL> conn /as sysdba 已连接。 SQL> select   2  extractvalue(value(d), '/hint') as outline_hints   3  from   4  xmltable('/outline_data/hint'   5  passing (   6  select   7  xmltype(comp_data) as xmlval   8  from   9  sqlobj$data sod, sqlobj$ so  10  where so.signature = sod.signature  11  and so.plan_id = sod.plan_id  12  and comp_data is not null  13  and name like '&baseline_plan_name'  14  )  15  ) d; 输入 baseline_plan_name 的值:  SQL_PLAN_13g6p1maja1790cce5f0e 原值   13: and name like '&baseline_plan_name' 新值   13: and name like 'SQL_PLAN_13g6p1maja1790cce5f0e'
OUTLINE_HINTS ----------------------------------------------------------------------------------------------------------------------------------------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_RS_ASC(@"SEL$1" "DH_STAT"@"SEL$1" ("DH_STAT"."ID"))
已选择6行。