设为首页 收藏本站
查看: 746|回复: 0

[经验分享] ORACLE 11G SPM(SQL PLAN manager)浅析

[复制链接]

尚未签到

发表于 2018-10-21 13:14:41 | 显示全部楼层 |阅读模式
  11g推出的SPM是一种主动的稳定执行计划的手段,能够保证只有被验证过的执行计划才会被启用。SPM既能够主动的稳定执行计划,又保留了继续使用新的执行效率更高的执行计划的机会。
  启用SPM后,每一个SQL都会存在对应的SQL PLAN Baseline,存储在DBA_SQL_PLAN_BASELINES视图。
  该视图的enable和accept列均为YES的SQL PLAN Baseline所对应的执行计划才会被执行。如果有超过1个以上的均为YES,那么oracle会选择其中cost值最小的为执行计划。
  可以有2种方法产生SQL PLAN Baseline
  1.自动捕获
  2.手动生成/批量导入
  下面先介绍自动捕获。参数optimizer_capture_sql_plan_baselines用于控制是否开启自动捕获,默认为false。参数optimizer_use_sql_plan_baselines用于控制是否启用SPM,默认为TRUE,表示默认情况下Oracle在生成执行计划时就会启用SPM,使用已有的SQL PLAN Baseline。
  创建一个自动捕获SQL PLAN Baseline,并据此来稳定执行计划。
  默认参数
sys@TEST:test> show parameter sql_plan  

  
NAME                                 TYPE        VALUE
  
------------------------------------ ----------- ------------------------------
  
optimizer_capture_sql_plan_baselines boolean     FALSE
  
optimizer_use_sql_plan_baselines     boolean     TRUE
  在当前session禁用SPM,同时开启自动捕获。
aaa@TEST:test> alter session set optimizer_capture_sql_plan_baselines=true;  

  
Session altered.
  

  
aaa@TEST:test> alter session set optimizer_use_sql_plan_baselines=false;
  

  
Session altered.
  创建测试表T2,并创建索引
aaa@TEST:test> create table t2 as select * from dba_objects;  

  
Table created.
  

  
aaa@TEST:test> create index idx_t2 on t2(object_id);
  

  
Index created.
  对T2收集统计信息
aaa@TEST:test> exec dbms_stats.gather_table_stats(ownname => 'AAA',tabname => 'T2',estimate_percent => 100,cascade => true);  

  
PL/SQL procedure successfully completed.
  执行SQL
aaa@TEST:test> 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
  

  
6 rows selected.
  执行计划如下
-----------------------------------------------------------------------------------------------------------------------------------  
SQL_ID  8vtdn0kgytfxr, child number 0
  
-------------------------------------
  
select object_id,object_name from t2 where object_id between 103 and 108
  

  
Plan hash value: 2008370210
  

  
--------------------------------------------------------------------------------------
  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
  
--------------------------------------------------------------------------------------
  
|   0 | SELECT STATEMENT            |        |       |       |     3 (100)|          |
  
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     6 |   180 |     3   (0)| 00:00:01 |
  
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     6 |       |     2   (0)| 00:00:01
  因为该SQL只执行过一次,所以Oracle不会自动捕获其SQL PLAN Baseline,验证:
aaa@TEST:test> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';  

  
no rows selected
  再执行一次,执行计划没有变化,因为SQL已经重复执行,Oracle会自动捕获其SQL PLAN Baseline
SQL> 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
  这里修改索引IDX_T2的聚簇因子修改为2400万,改变目标SQL的执行计划为全表。
SQL> exec dbms_stats.set_index_stats(ownname=>'AAA',indname=>'IDX_T2',clstfct=>24000000,no_invalidate => false);  
PL/SQL procedure successfully completed
  重新执行SQL,执行计划如下,为全表
select object_id,object_name from t2 where object_id between 103 and 108  

  
Plan hash value: 1513984157
  

  
--------------------------------------------------------------------------
  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  
--------------------------------------------------------------------------
  
|   0 | SELECT STATEMENT  |      |       |       |   290 (100)|          |
  
|*  1 |  TABLE ACCESS FULL| T2   |     6 |   180 |   290   (1)| 00:00:04 |
  
--------------------------------------------------------------------------
  因为目标SQL已经重复执行且又产生了一个执行计划,所以Oracle会自动捕获并创建这个新的执行计划所对应的SQL PLAN Baseline,查询视图
SQL> 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     NO       select object_id,object_name from t2 where object_id between 103 and 108
  然后关闭当前session自动捕获SQL PLAN Baseline,并启用SPM,就是恢复11g默认
aaa@TEST:test> alter session set optimizer_capture_sql_plan_baselines=false;  

  
Session altered.
  

  
aaa@TEST:test> alter session set optimizer_use_sql_plan_baselines=true;
  

  
Session altered.
  现在索引IDX_T2的聚簇因子还是2400万
aaa@TEST:test> select index_name,clustering_factor from user_indexes;  

  
INDEX_NAME                     CLUSTERING_FACTOR
  
------------------------------ -----------------
  
IDX_T2                                  24000000
  再次执行目标SQL,其执行计划
PLAN_TABLE_OUTPUT  
-----------------------------------------------------------------------------------------------------------------------------------
  
SQL_ID  8vtdn0kgytfxr, child number 2
  
-------------------------------------
  
select object_id,object_name from t2 where object_id between 103 and 108
  

  
Plan hash value: 2008370210
  

  
--------------------------------------------------------------------------------------
  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
  
--------------------------------------------------------------------------------------
  
|   0 | SELECT STATEMENT            |        |       |       |  2069 (100)|          |
  
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     6 |   180 |  2069   (0)| 00:00:25 |
  
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     6 |       |     2   (0)| 00:00:01 |
  
--------------------------------------------------------------------------------------
  
Note
  
-----
  
   - SQL plan baseline SQL_PLAN_asnmb3t5yfk4024c6dbb6 used for this statement
  上述可以看出,现在目标SQL的执行计划已经从对表的全表扫描,变成了索引范围扫描。并且NOTE下面有“SQL plan baseline SQL_PLAN_asnmb3t5yfk4024c6dbb6 used for this statement”,这表明在启用SPM情况下, 即使目标SQL产生了新执行计划,Oracle依然只会使用enabled和accepted均为YES的SQL PLAN Baseline对应的执行计划。
  如果想启用新的执行计划,对于不同版本操作不一样。
  11gR1,只需要将目标SQL锁采用的名为SQL_PLAN_asnmb3t5yfk4024c6dbb6的SQL PLAN Baseline的accepted值变为NO即可。
SQL> var temp number;  
SQL> exec :temp:=dbms_spm.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_ac526b1e4be74880',plan_name => 'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name => 'accepted',attribute_value => 'NO');
  在11gR2中,执行上述代码或报错
SQL> var temp number;  
SQL> 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;
  
ORA-38136: 指定的属性名 ACCEPTED 无效
  
ORA-06512: 在 "SYS.DBMS_SPM", line 2469
  
ORA-06512: 在 line 1
  
temp
  
---------
  在11gR2中,使用dbms_spm.evolve_sql_plan_baseline和dbms_spm.alter_sql_plan_baseline达到启用新执行计划的目的。
  先用dbms_spm.evolve_sql_plan_baseline将新执行计划的accepted置为YES
SQL> var temp varchar2(4000);  
SQL> 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
  
temp
  
---------
  

  
-------------------------------------------------------------------------------
  
                        Evolve SQL Plan Baseline Report
  
-------------------------------------------------------------------------------
  

  
Inputs:
  
-------
  
  SQL_HANDLE = SYS_SQL_ac526b1e4be74880
  
  PLAN_NAME  = SQL_PLAN_asnmb3t5yfk40b860bcf2
  
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  
  VERIFY     = NO
  
  COMMIT     = YES
  

  
Plan: SQL_PLAN_asnmb3t5yfk40b860bcf2
  
------------------------------------
  
  It is already an accepted plan.
  

  
-------------------------------------------------------------------------------
  
                                 Report Summary
  
-------------------------------------------------------------------------------
  
There were no SQL plan baselines that required processing.
  

  
SQL> 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 ob
  再使用dbms_spm.alter_sql_plan_baseline把PLAN_NAME为SQL_PLAN_asnmb3t5yfk4024c6dbb6的SQL PLAN Baseline的enabled置为NO。
SQL> var temp number;  
SQL> 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
  
temp
  
---------
  
1
  

  
SQL> 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
PLAN_TABLE_OUTPUT  
-----------------------------------------------------------------------------------------------------------------------------------
  
SQL_ID  8vtdn0kgytfxr, child number 0
  
-------------------------------------
  
select object_id,object_name from t2 where object_id between 103 and 108
  

  
Plan hash value: 1513984157
  

  
--------------------------------------------------------------------------
  
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  
--------------------------------------------------------------------------
  
|   0 | SELECT STATEMENT  |      |       |       |   290 (100)|          |
  
|*  1 |  TABLE ACCESS FULL| T2   |     6 |   180 |   290   (1)| 00:00:04 |
  
--------------------------------------------------------------------------
  
Note
  
-----
  
   - SQL plan baseline SQL_PLAN_asnmb3t5yfk40b860bcf2 used for this statement
  使用了新的执行计划,注意NOTE中SQL_PLAN_asnmb3t5yfk40b860bcf2即SQL PLAN Baseline中enabled和accepted均为YES的SQL PLAN Baseline对应的执行执行计划名。
  从测试结果看出,我们可以在目标SQL的多个执行计划之间切换,所以SPM既能稳定执行计划,又保留了继续使用新的执行计划的机会。
  接下来介绍手工生成SQL PLAN Baseline。手工生成很简单,核心是调用dbms_spm.load_plans_from_cursor_cache。
  针对目标SQL使用dbms_spm.load_plans_from_cursor_cache手工生成其初始执行计划所对应的SQL PLAN Baseline。
PLAN_TABLE_OUTPUT  
-----------------------------------------------------------------------------------------------------------------------------------
  
SQL_ID  85htp4tya3uwm, 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  |      |       |       |   290 (100)|          |
  
|*  1 |  TABLE ACCESS FULL| T2   |     1 |    30 |   290   (1)| 00:00:04 |
  上述SQL的SQL_ID为85htp4tya3uwm,plan_hash_value为1513984157,现在没有开启自动捕获SQL PLAN Baseline。视图dba_sql_plan_baselines应该没有对应信息。
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_name%';  
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
  
------------------------------ ------------------------------ -------------- ------- -------- -----------------------------------------------------------
  使用目标SQL的SQL_ID和PLAN_HASH_VALUE来生成所对应的SQL PLAN Baseline。
SQL> var temp number;  
SQL> exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'85htp4tya3uwm',plan_hash_value=>1513984157);
  
PL/SQL procedure successfully completed
  
temp
  
---------
  
1
  
SQL> 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)*/ object_name%';
  
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
  
------------------------------ ------------------------------ -------------- ------- -------- --------------------------------------------------------------------------------
  
SYS_SQL_75b06ae056223f5f       SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD    YES     YES      select /*+ no_index(t2 idx_t2)*/ object_name,object_id from t2 where object_id=4
  已经生成了SQL_HANDLE为SYS_SQL_75b06ae056223f5f和PLAN_NAME为SQL_PLAN_7bc3aw1b24guzb860bcf2的SQL PLAN Baseline。
  改写原目标SQL,加入强制走索引hint
PLAN_TABLE_OUTPUT  
-----------------------------------------------------------------------------------------------------------------------------------
  
SQL_ID  0argb4cn0sybz, child number 0
  
-------------------------------------
  
select /*+ index(t2 idx_t2)*/ object_name,object_id from t2 where
  
object_id=4
  

  
Plan hash value: 2008370210
  

  
--------------------------------------------------------------------------------------
  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
  
--------------------------------------------------------------------------------------
  
|   0 | SELECT STATEMENT            |        |       |       |   332 (100)|          |
  
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    30 |   332   (0)| 00:00:04 |
  
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |
  改写后走索引,SQL_ID=0argb4cn0sybz,Plan hash value: 2008370210。
  再加上原目标SQL的SQL_HANDLE来生成新的SQL PLAN Baseline。
SQL> exec :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0argb4cn0sybz',plan_hash_value=>2008370210,sql_handle=>'SYS_SQL_75b06ae056223f5f');  
PL/SQL procedure successfully completed
  
temp
  
---------
  
1
  
SQL> 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)*/ object_name%';
  
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
  
------------------------------ ------------------------------ -------------- ------- -------- --------------------------------------------------------------------------------
  
SYS_SQL_75b06ae056223f5f       SQL_PLAN_7bc3aw1b24guz24c6dbb6 MANUAL-LOAD    YES     YES      select /*+ no_index(t2 idx_t2)*/ object_name,object_id from t2 where object_id=4
  
SYS_SQL_75b06ae056223f5f       SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD    YES     YES      select /*+ no_index(t2 idx_t2)*/ object_name,object_id from t2 where object_id=4
  上述可以看出原目标SQL生成了新的PLAN,其PLAN_NAME为SQL_PLAN_7bc3aw1b24guz24c6dbb6。注意新生成的SQL PLAN Baseline的enabled和accepted均为YES,这是个自动捕获不同的地方。
  现在DROP掉原目标SQL走全表扫描所对应的SQL PLAN Baseline。
SQL> exec :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle =>'SYS_SQL_75b06ae056223f5f',plan_name => 'SQL_PLAN_7bc3aw1b24guzb860bcf2');  
PL/SQL procedure successfully completed
  
temp
  
---------
  
1
  
SQL>  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)*/ object_name%';
  
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
  
------------------------------ ------------------------------ -------------- ------- -------- --------------------------------------------------------------------------------
  
SYS_SQL_75b06ae056223f5f       SQL_PLAN_7bc3aw1b24guz24c6dbb6 MANUAL-LOAD    YES     YES      select /*+ no_index(t2 idx_t2)*/ object_name,object_id from t2 where object_id=4
  再次执行原SQL,可以看到走了索引
PLAN_TABLE_OUTPUT  
-----------------------------------------------------------------------------------------------------------------------------------
  
SQL_ID  85htp4tya3uwm, child number 2
  
-------------------------------------
  
select /*+ no_index(t2 idx_t2)*/ object_name,object_id from t2 where
  
object_id=4
  

  
Plan hash value: 2008370210
  

  
--------------------------------------------------------------------------------------
  
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
  
--------------------------------------------------------------------------------------
  
|   0 | SELECT STATEMENT            |        |       |       |   332 (100)|          |
  
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    30 |   332   (0)| 00:00:04 |
  
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |
  
--------------------------------------------------------------------------------------
  
Note
  
-----
  
   - SQL plan baseline SQL_PLAN_7bc3aw1b24guz24c6dbb6 used for this statement
  最后注意NOTE下面,使用了SQL PLAN Baeline。



运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-624525-1-1.html 上篇帖子: SQL Server 维护计划实现数据库备份 下篇帖子: SQL优化----百万数据查询优化
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表