Oracle固定SQL的执行计划(二)---SPM

简介:

之前写了一篇博客介绍的是用SQL Profile来调整、稳定目标SQL的执行计划,即使无法修改目标SQL的SQL文本。但SQL Profile实际上只是一种亡羊补牢、被动的技术手段,应用在那些执行计划已经发生了不好的变更的SQL上,即当我们发现这些SQL的执行计划已经出了问题时通过创建SQL Profile来纠正、稳定这些SQL的执行计划。即便通过创建SQL Profile解决了目标SQL执行计划变更的问题,依然不能保证系统后续执行的SQL的执行计划就不再发生不好的变更。这种不确定性会给Oracle数据库大版本升级(比如从Oracle 10g升级到Oracle 11g)带来一系列的麻烦,因为不清楚升级之后原先系统中哪些SQL的执行计划会发生不好的变更。

为了解决上述问题,Oracle在11g中推出了SPM(SQL Plan Management)。SPM是一种主动的稳定执行计划的手段,能够保证只有被验证过的执行计划才会被启用,当由于种种原因(如统计信息的变更)而导致目标SQL产生了新的执行计划后,这个新的执行计划并不会被马上启用,直到它已经被我们验证过其执行效率会比原先执行计划高才会被启用。

随着Oracle数据库版本的不段推进,其CBO的算法、功能也在一直不断进化和增加,所以同样的SQL有可能在新版本的Oralce数据库中执行效率更高,如果我们使用了SQL Profile(特别是使用了Manual类型的SQL Profile)来稳定目标SQL的执行计划,那就意味着可能失去了继续优化上述SQL的执行效率的机会。而SPM的推出可以说彻底解决了执行计划稳定性的问题,它既能主动地稳定执行计划,又保留了继续使用新的执行效率可能更高的执行计划的机会。

当启用了SPM后,每一个SQL都会存在对应的SQL Plan Baseline,这个SQL Plan Baseline里存储的就是该SQL的执行计划,如果一个SQL有多个执行计划,那么该SQL就可能会有多个SQL Plan Baseline,可以从DBA_SQL_PLAN_BASELINES中查看目标SQL所有的SQL Plan Baseline。

DBA_SQL_PLAN_BASELINES中的列ENABLED和ACCEPTED用来描述一个SQL Plan Baseline所对应的执行计划是否能被Oracle启用,只有ENABLED和ACCEPTED的值均为“YES”的SQL Plan Baseline所对应的执行计划才会被Oracle启用,如果一具SQL有超过1个以上的SQL Plan Baseline的ENABLED和ACCEPTED的值均为YES,则Oracle会从中选择成本值最小的一个所对应的执行坟墓来作为该SQL的执行计划。

在Oracle 11g及其以上的版本中,有如下两种方法可以产生目标SQL的SQL Plan Baseline。

  • 自动捕获

  • 手工生成/批量导入(批量导入尤其适用于Oracle数据库大版本的升级,它可以确保升级后原有系统所胡SQL的执行计划不会发生变化)

下面分别介绍如何自动捕获和手工的方式来产生SQL Plan Baseline。

1 自动捕获SQL Plan Baseline

参数OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES用于控制是否开启自动捕获SQL Plan Baseline,其默认值为FALSE,表示在默认情况下,Oracle并不会自动捕获SQL Plan Baseline。这个参数可以在session或系统级别动态修改。当修改为TRUE后,则Oracle会对上述参数影响范围内所有重复执行的SQL自动捕获其SQL Plan Baseline,并且针对目标SQL第一次捕获的SQL Plan Baseline的ENABLED和ACCEPTED的值均为“YES”。随后如果该SQL的执行计划发生了变更,则再次捕获到的SQL Plan Baseline的ENABLED的值依然为YES,但ACCEPTED的值变为了NO,这表示后续变更的执行计划虽然被捕获了,但Oracle不会将其作为该SQL的执行计划来执行,即此时Oracle会永远沿用该SQL第一次被捕获的SQL Plan Baseline所对应的执行计划(除非后续做了手工调整)。

参数OPTIMIZER_USE_SQL_PLAN_BASELINES用于控制是否启用SQL Plan Baseline,其默认值为TRUE,表示在默认情况下,Oracle在生成执行计划时就会启用SPM,使用已有的SQL Plan Baseline,这个参数也可以在session或系统级别动态修改。

下面看一下实例:

查看上述两个参数的默认值

1
2
3
4
5
6
zx@MYDB>show parameter sql_plan
 
NAME                                  TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_capture_sql_plan_baselines boolean                            FALSE
optimizer_use_sql_plan_baselines     boolean                            TRUE

在当前session中禁掉SPM并同时开启自动捕获SQL Plan Baseline:

1
2
3
4
5
6
7
zx@MYDB> alter  session  set  optimizer_use_sql_plan_baselines= FALSE ;
 
Session altered.
 
zx@MYDB> alter  session  set  optimizer_capture_sql_plan_baselines= TRUE ;
 
Session altered.

创建测试表T2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
zx@MYDB> create  table  t2  as  select  from  dba_objects;
 
Table  created.
 
zx@MYDB> create  index  idx_t2  on  t2(object_id);
 
Index  created.
 
zx@MYDB> exec  dbms_stats.gather_table_stats(ownname=> USER ,tabname=> 'T2' ,estimate_percent=>100, cascade => true );
 
PL/SQL  procedure  successfully completed.
 
zx@MYDB> select  object_id,object_name  from  t2  where  object_id  between  103  and  108;
 
  OBJECT_ID OBJECT_NAME
---------- ------------------------------
        103 MIGRATE$
        104 DEPENDENCY$
        105 ACCESS$
        106 I_DEPENDENCY1
        107 I_DEPENDENCY2
        108 I_ACCESS1
 
rows  selected.

wKioL1i1djrRH9N_AAA32GVLpeo751.png

从执行计划上看,走的是索引IDX_T2上的索引范围扫描,因为SQL只执行了一次,所以Oracle不会自动捕获SQL Plan Baseline,DBA_SQL_PLAN_BASELINES中没有记录

1
2
3
4
5
6
7
zx@MYDB>col sql_handle  for  a30
zx@MYDB>col plan_name  for  a30
zx@MYDB>col origin  for  a20
zx@MYDB>col sql_text  for  a70
zx@MYDB> select  sql_handle,plan_name,origin,enabled,accepted,sql_text  from  dba_sql_plan_baselines;
 
no  rows  selected

再次执行上述SQL,因为重复执行该SQL,Oracle自动捕获了这个SQL的SQL Plan Baseline 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
zx@MYDB> select  object_id,object_name  from  t2  where  object_id  between  103  and  108;
 
  OBJECT_ID OBJECT_NAME
---------- ------------------------------
        103 MIGRATE$
        104 DEPENDENCY$
        105 ACCESS$
        106 I_DEPENDENCY1
        107 I_DEPENDENCY2
        108 I_ACCESS1
 
rows  selected.
 
zx@MYDB> select  sql_handle,plan_name,origin,enabled,accepted,sql_text  from  dba_sql_plan_baselines;
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN                ENABLED   ACCEPTED  SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ------------------------------------------------------------
SYS_SQL_ac526b1e4be74880       SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE          YES       YES        select  object_id,object_name  from  t2  where  object_id  between  103  and  108

现在将索引IDX_T2的聚簇因子修改为2400万,目的是为了能让SQL的执行计划变为对表T2的全表扫描(为何修改聚簇因子,参考http://hbxztc.blog.51cto.com/1587495/1901258)。修改完后再执行上述SQL,并查看执行计划:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
zx@MYDB> exec  dbms_stats.set_index_stats(ownname=> USER ,indname=> 'IDX_T2' ,clstfct=>24000000,no_invalidate=> false );
 
PL/SQL  procedure  successfully completed.
 
zx@MYDB> select  index_name,clustering_factor  from  dba_indexes  where  index_name= 'IDX_T2' ;
 
INDEX_NAME                                                                                 CLUSTERING_FACTOR
------------------------------------------------------------------------------------------ -----------------
IDX_T2                                                                                              24000000
 
zx@MYDB> select  object_id,object_name  from  t2  where  object_id  between  103  and  108;
 
  OBJECT_ID OBJECT_NAME
---------- ------------------------------
        103 MIGRATE$
        104 DEPENDENCY$
        105 ACCESS$
        106 I_DEPENDENCY1
        107 I_DEPENDENCY2
        108 I_ACCESS1
 
rows  selected.

wKioL1i1e3_CKEWzAAAvWrViOHQ942.png

从执行计划中可以看出该SQL的执行计划已经变为全表扫描。因为目标SQL已经重复执行且同时又产生了一个新的执行计划,所以现在Oracle就会自动捕获并创建这个新的执行计划所对应的SQL Plan Baseline了。从如下查询可以看出Oracle对新的执行计划产生了一个新的SQL Plan Baseline,其ENABLED的值依然为YES,但ACCEPTED的值变为了NO:

wKiom1i1fKawm8HlAAA-8lMjR2w622.png现在我们对当前Session关闭自动捕获SQL Plan Baseline并同时开启SPM,现在索引IDX_T2的聚簇因子依然为2400万,再次执行目标SQL,并查看执行计划:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
zx@MYDB> alter  session  set  optimizer_use_sql_plan_baselines= TRUE ;
 
Session altered.
 
zx@MYDB> alter  session  set  optimizer_capture_sql_plan_baselines= FALSE ;
 
Session altered.
 
 
zx@MYDB> select  index_name,clustering_factor  from  dba_indexes  where  index_name= 'IDX_T2' ;
 
INDEX_NAME                                                                                 CLUSTERING_FACTOR
------------------------------------------------------------------------------------------ -----------------
IDX_T2                                                                                              24000000
 
zx@MYDB> select  object_id,object_name  from  t2  where  object_id  between  103  and  108;
 
  OBJECT_ID OBJECT_NAME
---------- ------------------------------
        103 MIGRATE$
        104 DEPENDENCY$
        105 ACCESS$
        106 I_DEPENDENCY1
        107 I_DEPENDENCY2
        108 I_ACCESS1
 
rows  selected.

wKiom1i1fbyDv_8uAAA50HboFX4394.png

wKioL1i1fbyBQ8hfAAAOoTRrHrs335.png

从上面的显示内容可以看出,现在目标SQL的执行又从全表扫描恢复为了索引范围扫描,并且执行计划中的Note部分有“SQL plan baseline SQL_PLAN_asnmb3t5yfk4024c6dbb6 used for this statement”内容,说明SPM开启的情况下,即便目标SQL产生了新的执行计划,Oracle依然只会应用该SQL的ENABLED和ACCEPTED的值均为YES的SQL Plan Baselline。

如果想启用目标SQL新的执行计划(即全表扫描),应该如何做呢?

针对不同的Oracle版本,会有不同的处理方法。比如这里想启用目标SQL的新的执行计划,如果是11gR1的环境,则只需要将目标SQL所采用的名为SQL_PLAN_asnmb3t5yfk4024c6dbb6的SQL Plan Baseline(即索引范围扫描)的ACCEPTED的值设为NO就可以了。但对于11gR2环境,上述方法会报错,因为在11gR2中,所有已经被ACCEPTED的SQL Plan Baseline的ACCEPTED的值将不再能够被设为NO:

1
2
3
4
5
6
7
8
9
zx@MYDB>var  temp  varchar2(1000);
zx@MYDB> exec  : temp  := dbms_spm.alter_sql_plan_baseline(sql_handle=> 'SYS_SQL_ac526b1e4be74880' ,plan_name=> 'SQL_PLAN_asnmb3t5yfk4024c6dbb6' ,attribute_name=> 'accepted' ,attribute_value=> 'NO' );
BEGIN  : temp  := dbms_spm.alter_sql_plan_baseline(sql_handle=> 'SYS_SQL_ac526b1e4be74880' ,plan_name=> 'SQL_PLAN_asnmb3t5yfk4024c6dbb6' ,attribute_name=> 'accepted' ,attribute_value=> 'NO' );  END ;
 
*
ERROR  at  line 1:
ORA-38136: invalid attribute  name  ACCEPTED specified
ORA-06512:  at  "SYS.DBMS_SPM" , line 2469
ORA-06512:  at  line 1

在11gR2中,我们可以联合使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE和DBMS_SPM.ALTER_SQL_PLAN_BASELINE达到启用目标SQL新的执行计划的目的。

先用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE将新的执行计划(全表扫描)所对应的SQL Plan Baseline的ACCEPTED值设为“YES”:

1
2
3
zx@MYDB> exec  : temp  := dbms_spm.evolve_sql_plan_baseline(sql_handle=> 'SYS_SQL_ac526b1e4be74880' ,plan_name=> 'SQL_PLAN_asnmb3t5yfk40b860bcf2' ,verify=> 'NO' , commit => 'YES' );
 
PL/SQL  procedure  successfully completed.

wKioL1i2VxqjOvvpAABM2ewZX24436.png

从上面显示的内容看到如下信息:“Plan: SQL_PLAN_asnmb3t5yfk40b860bcf2----Plan was changed to an accepted plan.”,这表明已经将新的执行计划(全表扫描)所对应的SQL Plan Baseline的ACCEPTED值设为YES

从下面的查询结果也可以证明:

1
2
3
4
5
6
7
8
zx@MYDB> select  sql_handle,plan_name,origin,enabled,accepted,sql_text  from  dba_sql_plan_baselines  where  sql_text  like  'select object_id%' ;
 
SQL_HANDLE             PLAN_NAME           ORIGIN         ENABLED   ACCEPTED  SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ----------------------------------------------------------------------
SYS_SQL_ac526b1e4be74880       SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE     YES        YES        select  object_id,object_name  from  t2  where  object_id  between  103  and  108
 
 
SYS_SQL_ac526b1e4be74880       SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE     YES        YES        select  object_id,object_name  from  t2  where  object_id  between  103  and  108

然后再使用DBMS_SPM.ALTER_SQL_PLAN_BASELINE将原先的执行计划(索引范围扫描)对应的SQL Plan Baseline的ENABLED的值设为NO:

1
2
3
4
5
6
7
8
9
10
11
12
zx@MYDB> exec  : temp  := dbms_spm.alter_sql_plan_baseline(sql_handle=> 'SYS_SQL_ac526b1e4be74880' ,plan_name=> 'SQL_PLAN_asnmb3t5yfk4024c6dbb6' ,attribute_name=> 'enabled' ,attribute_value=> 'NO' );
 
PL/SQL  procedure  successfully completed.
 
zx@MYDB> select  sql_handle,plan_name,origin,enabled,accepted,sql_text  from  dba_sql_plan_baselines  where  sql_text  like  'select object_id%' ;
 
SQL_HANDLE             PLAN_NAME           ORIGIN         ENABLED   ACCEPTED  SQL_TEXT
------------------------------ ------------------------------ -------------------- --------- --------- ----------------------------------------------------------------------
SYS_SQL_ac526b1e4be74880       SQL_PLAN_asnmb3t5yfk4024c6dbb6 AUTO-CAPTURE      NO          YES        select  object_id,object_name  from  t2  where  object_id  between  103  and  108
 
 
SYS_SQL_ac526b1e4be74880       SQL_PLAN_asnmb3t5yfk40b860bcf2 AUTO-CAPTURE     YES        YES        select  object_id,object_name  from  t2  where  object_id  between  103  and  108

再次执行目标SQL

1
2
3
4
5
6
7
8
9
10
11
12
zx@MYDB> select  object_id,object_name  from  t2  where  object_id  between  103  and  108;
 
  OBJECT_ID OBJECT_NAME
---------- ------------------------------
        103 MIGRATE$
        104 DEPENDENCY$
        105 ACCESS$
        106 I_DEPENDENCY1
        107 I_DEPENDENCY2
        108 I_ACCESS1
 
rows  selected.

wKiom1i2Vv2iwub_AAAvSnV71kE481.png

wKioL1i2Vv7hc8GWAAAPcRB3DjE878.png

从上述显示可以看出,现在SQL的执行计划已经变为了全表扫描,我们要启用新的执行计划(全表扫描)的目的已经实现,Note部分也有了提示。

从上述测试结果可以看出,实际上我们可以轻易地在目标SQL的多个执行计划中切换,所以SPM确实是既能够主动地稳定执行计划,又保留了继续使用新的执行计划的机会,并且我们很容易就能启用新的执行计划。

下面介绍手工生成SQL Plan Baseline:

手工生成目标SQL的SQL Plan Baseline其实非常简单,其核心就是调用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE。这里只讨论针对单个SQL的SQL Plan Baseline的手工生成。

之前介绍过用Manual类型的SQL Profile可以在不改变目标SQL的SQL文本的情况下调整其执行计划。实际上,用手工生成SQL Plan Baseline的方式也完全可以实现同样的目的,甚至会比使用Manual类型的SQL Profile更加简洁。

手工生成目标SQL的SQL Plan Baseline的具体步骤为:

1)针对目标SQL使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手工生成其初始执行计划所对应的SQL Plan Baseline。此时,使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE传入的参数如下所示:

dbms_spm.load_plans_from_cursor_cache(sql_id=>'原目标SQL的SQL_ID',plan_hash_value=>原目标SQL的PLAN HASH VALUE)

2)改写原目标SQL的SQL文本,在其中加入合适的Hint,直到加入Hint后的所改写的SQL能走出我们想要的执行计划,然后对改写后的SQL使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE手工生成新的执行计划所对应的SQL Plan Baseline。此时传入的参数如下所示:

dbms_spm.load_plans_from_cursor_cache(sql_id=>'加入合适Hint后改写SQL的SQL_ID',plan_hash_value=>加入合适Hint后改写SQL的PLAN HASH VALUE,sql_handle=>'原目标SQL在步骤(1)中所产生的SQL Plan Baseline的sql_handle')

3)使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE删除步骤(1)中手工生成的原目标SQL的初始执行计划所对应的SQL Plan Baseline。此时传入的参数如下所示:

dbms_spm.drop_sql_plan_baseline(sql_handle=>'原目标SQL在步骤(1)中所产生的SQL Plan Baseline的sql_handle',plan_name=>'原目标SQL在步骤(1)中所产生的SQL Plan Baseline的plan_name')

下面使用一个实例演示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
zx@MYDB> select  /*+ no_index(t2 idx_t2) */ object_name,object_id  from  t2  where  object_id=4;
 
OBJECT_NAME         OBJECT_ID
------------------------------ ----------
TAB$                    4
 
zx@MYDB> select  from  table (dbms_xplan.display_cursor( null , null , 'advanced' ));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0n5z3wmf8qpgn, child number 0
-------------------------------------
select  /*+ no_index(t2 idx_t2) */ object_name,object_id  from  t2  where
object_id=4
 
Plan hash value: 1513984157
 
--------------------------------------------------------------------------
| Id  | Operation     |  Name  Rows   | Bytes | Cost (%CPU)|  Time      |
--------------------------------------------------------------------------
|   0 |  SELECT  STATEMENT  |  |  |  |   287 (100)|    |
|*  1 |   TABLE  ACCESS  FULL | T2   |     1 |    30 |   287   (1)| 00:00:04 |
--------------------------------------------------------------------------
 
Query Block  Name  / Object Alias (identified  by  operation id):
-------------------------------------------------------------
 
    1 - SEL$1 / T2@SEL$1
 
Outline Data
-------------
 
   /*+
       BEGIN_OUTLINE_DATA
       IGNORE_OPTIM_EMBEDDED_HINTS
       OPTIMIZER_FEATURES_ENABLE( '11.2.0.1' )
       DB_VERSION( '11.2.0.1' )
       ALL_ROWS
       OUTLINE_LEAF(@ "SEL$1" )
       FULL (@ "SEL$1"  "T2" @ "SEL$1" )
       END_OUTLINE_DATA
   */
 
Predicate Information (identified  by  operation id):
---------------------------------------------------
 
    1 - filter( "OBJECT_ID" =4)
 
Column  Projection Information (identified  by  operation id):
-----------------------------------------------------------
 
    1 -  "OBJECT_NAME" [VARCHAR2,128],  "OBJECT_ID" [NUMBER,22]
 
 
43  rows  selected.
 
zx@MYDB> select  sql_handle,plan_name,origin,enabled,accepted,sql_text  from  dba_sql_plan_baselines  where  sql_text  like  'select /*+ no_index(t2 idx_t2)%' ;
 
no  rows  selected
 
zx@MYDB>var  temp  number
zx@MYDB> exec  : temp  :=dbms_spm.load_plans_from_cursor_cache(sql_id=> '0n5z3wmf8qpgn' ,plan_hash_value=>1513984157);
 
PL/SQL  procedure  successfully completed.
 
zx@MYDB> select  sql_handle,plan_name,origin,enabled,accepted,sql_text  from  dba_sql_plan_baselines  where  sql_text  like  'select /*+ no_index(t2 idx_t2)%' ;