Oracle 11g中SPM指的是什么

本篇文章为大家展示了Oracle 11g中SPM指的是什么，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

前言
   我们知道,SQL语句的性能在很大程度上依赖于SQL语句的执行计划,如果SQL语句的执行计划发生变化,那么SQL语句的性能有可能发生很大的变化。影响SQL语句执行计划的因素很多,常见的有:

   ▪优化器版本的变化

   ▪统计信息的变化

   ▪优化器相关的各种参数的变化

   ▪对象定义的修改

   ▪添加、删除相关索引

   ▪修改了系统的设置

   ▪绑定变量的"窥视"功能

   ▪绑定变量的定义发生变化（比如类型或长度有变化）

   ▪启用了outline或SQL Profile等设置

   11g前我们的处理方式

   在Oracle 11g前，我们可以借助存储大纲(Stored Outline)和SQL Profile来帮助我们固定某个SQL语句的执行计划，防止由于执行计划的改变从而导致SQL性能的衰退。不过这些技术需要DBA人为的处理，比如存储大纲需要DBA手工创建，而对10g中提供的SQL Profile来说，则需要DBA手工应用才可以生效。

   11g中，Oracle 提供了SPM

   SPM就是Sql Plan Management,是Oracle自我管理（或者说自动性）发展的新功能。通过这个特性，Oracle自动去判断某个SQL的新的执行计划是否更加合理（成本更低），只有在新的执行计划比原来的执行计划更好的情况下，它才会被使用，从而有效的保护了执行计划的稳定性，进而保证了SQL语句的执行效率。

关于SPM的工作原理,本文不做详细的介绍,有兴趣的同志可以参阅相关的资料(网上类似的文章也不少)

执行计划管理实例测试

测试一:自动捕获的场景

      Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
      Connected as study

      SQL> create table test as select * from dba_objects;
      Table created

      SQL> exec DBMS_STATS.gather_table_stats(USER, 'TEST', cascade=>TRUE);
      PL/SQL procedure successfully completed

      SQL> select object_name from test where object_id=100;
      OBJECT_NAME
      —————-
      FIXED_OBJ$

      SQL> select object_name from test where object_id=100;
      OBJECT_NAME
      —————-
      FIXED_OBJ$

      尽管上面的查询语句执行了2次,但这个时候去查询dba_sql_plan_baseline的时候会发现,找不到我们需要的记录,因为这个时候optimizer_capture_sql_plan_baselines设置为false.禁止了自动捕获的功能.下面我将该参数设置为true,继续测试看看:

      SQL> alter session set optimizer_capture_sql_plan_baselines=true;
      Session altered

      SQL> select object_name from test where object_id=100;

      OBJECT_NAME
      ——————–
      FIXED_OBJ$

      SQL> select object_name from test where object_id=100;

      OBJECT_NAME
      ——————–
      FIXED_OBJ$

      SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;

      SIGNATURE SQL_HANDLE                   PLAN_NAME                      ORIGIN         ENABLED ACCEPTED AUTOPURGE
      ———- ————————— —————————— ————– ——- ——– ———
      4.32736134 SYS_SQL_3c0de038050ab8f1    SYS_SQL_PLAN_050ab8f197bbe3d0  AUTO-CAPTURE   YES     YES      YES

      这个时候,我们再去查询dba_sql_plan_baseline的时候,就会发现该语句在plan history中记录了一个执行计划.

      几个关键字段的说明

      sql_handle：表示SQL语句的句柄

      plan_name： 表示该SQL语句执行计划的名字

      origin：    表示该执行计划是如何进入plan history的。AUTO-CAPTURE：优化器自动加入；MANUAL：DBA手工加入

      enabled：   表示是否被启用了。YES：启用 NO：禁用。如果某个执行计划为禁用，则优化器根本就不会考虑使用该执行计划

      accepted：  表示是否接受，也就是是否进入了plan baseline里，YES表示接受，NO表示不接受

      autopurge： 表示是否为定期自动删除,YES表示是，NO表示否。

      fixed：     当plan baseline中有多个执行计划时，表示优化器仅考虑这些被设置为FIXED的计划，从中选择一个最优的。

      继续测试的旅程，新加一个index，再执行上面的SQL语句：

      SQL> create index inx_test_object_id on test(object_id);
      Index created

      SQL> exec DBMS_STATS.gather_table_stats(USER, 'TEST', cascade=>TRUE);
      PL/SQL procedure successfully completed

      SQL> select object_name from test where object_id=100;

      OBJECT_NAME
      —————–
      FIXED_OBJ$

      SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;

      SIGNATURE  SQL_HANDLE                 PLAN_NAME                      ORIGIN        ENABLED ACCEPTED AUTOPURGE
      ———- ————————– —————————— ————- ——- ——– ———
      4.32736134 SYS_SQL_3c0de038050ab8f1   SYS_SQL_PLAN_050ab8f127b7cc01  AUTO-CAPTURE  YES     NO       YES
      4.32736134 SYS_SQL_3c0de038050ab8f1   SYS_SQL_PLAN_050ab8f197bbe3d0  AUTO-CAPTURE  YES     YES      YES

      SQL>

      可以看到，dba_sql_plan_baselines视图里多了一个执行计划（上面灰色的），不过该执行计划的accepted为NO，表示还没有进入到plan baseline里，而是进入了plan history中。

      如果想让该计划进入到plan baseline中，我们可以借助dbms_spm来完成：

       Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
       Connected as study
       
       SQL> 
       SQL> set serveroutput on long 100000
       SQL> declare
         2     report clob;
         3  begin
         4     report := dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_3c0de038050ab8f1',
         5                                                 plan_name => 'SYS_SQL_PLAN_050ab8f127b7cc01');
         6     dbms_output.put_line(report);
         7  end;
         8  /
       
       
       -------------------------------------------------------------------------------
                               Evolve SQL Plan Baseline Report
       -------------------------------------------------------------------------------
       
       Inputs:
       -------
         SQL_HANDLE = SYS_SQL_3c0de038050ab8f1
         PLAN_NAME  = SYS_SQL_PLAN_050ab8f127b7cc01
         TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
         VERIFY     = YES
         COMMIT     = YES
       
       Plan: SYS_SQL_PLAN_050ab8f127b7cc01
       -----------------------------------
         Plan was verified: Time used .062 seconds.
         Passed performance criterion: Compound improvement ratio >= 337.17.
         Plan was changed to an accepted plan.
       
                             Baseline Plan      Test Plan     Improv. Ratio
                             -------------      ---------     -------------
         Execution Status:        COMPLETE       COMPLETE
         Rows Processed:                 1              1
         Elapsed Time(ms):              13              0
         CPU Time(ms):                  15              0
         Buffer Gets:                 1010              3            336.67
         Disk Reads:                     0              0
         Direct Writes:                  0              0
         Fetches:                        0              0
         Executions:                     1              1
       
       -------------------------------------------------------------------------------
                                        Report Summary
       -------------------------------------------------------------------------------
       Number of SQL plan baselines verified: 1.
       Number of SQL plan baselines evolved: 1.
       
       
       PL/SQL procedure successfully completed
       
       SQL>

      再看一下dba_sql_plan_baselines

      SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;

      SIGNATURE  SQL_HANDLE                 PLAN_NAME                      ORIGIN        ENABLED ACCEPTED AUTOPURGE
      ———- ————————– —————————— ————- ——- ——– ———
      4.32736134 SYS_SQL_3c0de038050ab8f1   SYS_SQL_PLAN_050ab8f197bbe3d0  AUTO-CAPTURE  YES     YES      YES
      4.32736134 SYS_SQL_3c0de038050ab8f1   SYS_SQL_PLAN_050ab8f127b7cc01  AUTO-CAPTURE  YES     YES     YES
      SQL>

      咱们分别看一下这两个执行计划的详细信息：

SQL> select * from table(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_050ab8f127b7cc01'));
     
      PLAN_TABLE_OUTPUT
      ——————————————————————————–
      SQL handle: SYS_SQL_3c0de038050ab8f1
      SQL text: select object_name from test where object_id=100
      ——————————————————————————–
      Plan name: SYS_SQL_PLAN_050ab8f127b7cc01
      Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
      ——————————————————————————–
      Plan hash value: 2422726699
      ——————————————————————————–
      | Id  | Operation                   | Name               | Rows  | Bytes | Cost
      ——————————————————————————–
      |   0 | SELECT STATEMENT            |                    |     1 |    30 |     2
      |   1 |  TABLE ACCESS BY INDEX ROWID| TEST               |     1 |    30 |     2
      |*  2 |   INDEX RANGE SCAN          | INX_TEST_OBJECT_ID |     1 |       |     1
      ——————————————————————————–
     
      PLAN_TABLE_OUTPUT
      ——————————————————————————–
      Predicate Information (identified by operation id):
      —————————————————
         2 – access("OBJECT_ID"=100)
     
      25 rows selected
     
      SQL>
     
      SQL> select * from table(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_050ab8f197bbe3d0'));
     
      PLAN_TABLE_OUTPUT
      ——————————————————————————–
      ——————————————————————————–
      SQL handle: SYS_SQL_3c0de038050ab8f1
      SQL text: select object_name from test where object_id=100
      ——————————————————————————–
      ——————————————————————————–
      Plan name: SYS_SQL_PLAN_050ab8f197bbe3d0
      Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
      ——————————————————————————–
      Plan hash value: 1357081020
      ————————————————————————–
      | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
      ————————————————————————–
      |   0 | SELECT STATEMENT  |      |     1 |    30 |   282   (1)| 00:00:04 |
      |*  1 |  TABLE ACCESS FULL| TEST |     1 |    30 |   282   (1)| 00:00:04 |
      ————————————————————————–
     
      PLAN_TABLE_OUTPUT
      ——————————————————————————–
      Predicate Information (identified by operation id):
      —————————————————
         1 – filter("OBJECT_ID"=100)
     
      24 rows selected
     
      SQL>

在看看这个SQL执行时到底选择了那个baseline:

SQL> conn study/study
      已连接。
      SQL> set autotrace trace
      SQL> select object_name from test where object_id=100;      
     
      执行计划
      ———————————————————-
      Plan hash value: 2422726699
     
      ————————————————————————————————–
      | Id  | Operation                   | Name               | Rows  | Bytes | Cost(%CPU)| Time     |
      ————————————————————————————————–
      |   0 | SELECT STATEMENT            |                    |     1 |    30 |     2   (0)| 00:00:01 |
      |   1 |  TABLE ACCESS BY INDEX ROWID| TEST               |     1 |    30 |     2   (0)| 00:00:01 |
      |*  2 |   INDEX RANGE SCAN          | INX_TEST_OBJECT_ID |     1 |       |     1   (0)| 00:00:01 |
      ————————————————————————————————–
     
      Predicate Information (identified by operation id):
      —————————————————
     
         2 – access("OBJECT_ID"=100)
     
      Note
      —–
         – SQL plan baseline "SYS_SQL_PLAN_050ab8f127b7cc01" used for this statement
     
      SQL>　

下面再熟悉一下手工删除plan baseline里的执行计划，同样可以借助dbms_spm来完成。下面的DEMO把上面baseline里走full table scan的执行计划清除：

SQL> var ret number
      SQL> exec :ret:=dbms_spm.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_3c0de038050ab8f1',
                                                      plan_name => 'SYS_SQL_PLAN_050ab8f197bbe3d0');

      PL/SQL procedure successfully completed
      ret
      ———
      1

      SQL>
      SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;

      SIGNATURE  SQL_HANDLE               PLAN_NAME                      ORIGIN         ENABLED ACCEPTED AUTOPURGE
      ———- ———————— —————————— ————– ——- ——– ———
      4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01  AUTO-CAPTURE   YES     YES      YES

      SQL>

测试二:手动捕获的场景      

       Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
       Connected as study
       
       SQL> select sql_text,sql_id from v$sql where sql_text like '%select object_name from test where%';
       
       SQL_TEXT                                              SQL_ID
       ----------------------------------------------------- -------------
       select object_name from test where object_id=100      7j7jc706upva2
       
       
       SQL> 
       SQL> set serveroutput on
       SQL> declare
         2    l_plans_loaded  PLS_INTEGER;
         3  begin
         4    l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
         5      sql_id => '7j7jc706upva2');
         6  
         7    DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
         8  END;
         9  /
       
       Plans Loaded: 1
       
       PL/SQL procedure successfully completed
       
       SQL> 
       SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge
         2  from dba_sql_plan_baselines;
       
        SIGNATURE SQL_HANDLE                PLAN_NAME                      ORIGIN      ENABLED ACCEPTED AUTOPURGE
       ---------- ------------------------- ------------------------------ ----------- ------- -------- ---------
       4.32736134 SYS_SQL_3c0de038050ab8f1  SYS_SQL_PLAN_050ab8f127b7cc01  MANUAL-LOAD YES     YES      YES
       
       SQL>

上述内容就是Oracle 11g中SPM指的是什么，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。