sql_plan_baseline

https://blog.csdn.net/u010719917/article/details/52002679

为土默特左旗等地区用户提供了全套网页设计制作服务，及土默特左旗网站建设行业解决方案。主营业务为成都做网站、网站设计、土默特左旗网站设计，以传统方式定制建设网站，并提供域名空间备案等一条龙服务，秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求，就会得到认可，从而选择与我们长期合作。这样，我们也可以走得更远！

1、查看错误的SQL 所执行 的执行计划，确认是否需要使用基线控制执行计划
（前提，我们无法直接修改SQL，否则直接通过SQL改写方式即可）

2、打开基线捕获参数  alter session set optimizer_capture_sql_plan_baselines=TRUE  （关闭FALSE）

执行 SQL   执行2次，捕获执行计划，然后关闭alter session set optimizer_capture_sql_plan_baselines=FALSE；

查看基线视图select * from dba_sql_plan_baselines 进行确认；

定制 正确的 SQL 计划，强制加HINT ，然后执行生成新的SQL_ID ，新的执行计划PLAN_ID

select * from table(dbms_xplan.display_cursor) 获取SQL_PLAN ID和SQL_ID 

导入SQL基线到基线基表中（数据字典）  
declare  
 k1 pls_integer;
begin  
 k1:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
 sql_id=>'b3h69kwfphm3j',（新SQL的ID）
 sql_handle=>'SQL_7313a4bc2778b751',  
 plan_hash_value=>'2949544139');  （新的SQL计划）
end;  
删除原来SQL基线
declare  
 k1 pls_integer;    
 begin  
 k1:=DBMS_SPM.drop_SQL_PLAN_BASELINE(  
 sql_handle=>'SQL_7313a4bc2778b751',  
  plan_name=>'SQL_PLAN_764x4rhmrjdujd8a279cc');   
end;  
执行原来的SQL 进行验证，是否优化器已经采用基线； 同理前台的SQL 在执行过程中执行了新的执行计划； 调整基线保留时间和基线空间占用率select * from dba_sql_management_config

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

创建基线的几种方式

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;

4.
    execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_4929', owner_name => 'HBJZT', plan_hash_value => 333597355);

四、基线的几种状态
 
一个SQL语句对应的基线，我将它们归纳为三种状态
1.accepted（可接受），只有这种状态的基线，优化器才会考虑此基线中的执行计划
2.no-accepted（不可接受），这种状态的基线，优化器在SQL语句解析期间不会考虑。这种状态的基线必须通过演化和验证通过后，转变为accepted状态后，才会被优化器考虑使用
3.fixed为yes（固定），这种状态的基线固有最高优先级！比其他两类基线都要优先考 五、查看基线
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包来修改基线的一些属性，主要有如下几个属性
1.ENABLED ：设置该属性的值为NO告诉Oracle 11g临时禁用某个计划，一个SQL计划必须同时标记为ENABLED和ACCEPTED，否则CBO将忽略它
2.FIXED：设置为YES，那个计划将是优化器唯一的选择[最高优先级]，即使如果某个计划可能拥有更低的成本。这让DBA可以撤销SMB的默认行为，对于转换一个存储概要进入一稳定的SQL计划基线特别有用，注意当一个新计划被添加到被标记为FIXED的SQL计划基线，该新计划不能被利用除非它申明为FIXED状态
3.AUTOPURG：设置这个属性的值为NO告诉Oracle 11g无限期保留它，从而不用担心SMB的自动清除机制
4.plan_name : 改变SQL plan 名字
5.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;
/

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

    create_stgtab_baseline创建一个计划基线保存表
    pack_stgtab_baseline将基线从数据字典复制到第一步的表中
    unpack_stgtab_baseline将基线从保存表中复制到迁移数据库的数据字典中

大概过程如下：
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一样，具体见文档
 
九、删除基线

    可以通过dbms_SPM.drop_sql_plan_baseline包来手工删除数据字典里的基线
    为使用的基线，fixed为no的基线，将在一定的保留期后自动删除(可查看dba_sql_management_config视图）

手工删除方法如下
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,'hh34: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,'hh34: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,'hh34: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
 k1 pls_integer;
 begin
 k1 := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>'SYS_SQL_11bcd50cd51504e9',plan_name=>'SQL_PLAN_13g6p1maja17934f41c8d');
 end;
 /
 
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行。