SQL Plan Management

任何数据库应用程序的性能都严重依赖于它的查询执行。当没有任何用户介入时Oracle优化器是完全能够评估最优执行计划的,SQL语句的执行计划因为各种原因，比如：重新收集优化器统计信息，改变优化器参数或方案/元数据定义而可能会出现改变。但是不能保证执行计划的改变总是会提高性能，因此会导致有些管理员会锁定SQL的执行计划(Stored Outline)或锁定统计信息。然而，这样做会导致不能使用新版优化器为了提供高SQL性能所提供的一些新特性或新的访问路径。

Oracle的每一个新版本中都包含了一些新特性用来提高查询的性能。然而某些应用（或部分应用）是静态的并且比起在特定情况下提高性能来说可预测的性能是更可取的。Oracle11G提供了SPM来完全透时的控制执行计划的演变。使用SPM优化器自动管理执行计划并且确保只有已经或被验证的执行计划才会被使用。当找到SQL语句的新执行计划时，但直到它被数据库进行验证或其性能比当前执行计划高才会被使用。

SQL Plan Management是一种预防机制，记录与评估SQL语句的执行计划。这种机制可以创建一个SQL执行计划基线，它是由一组已知性能良好的执行计划组成。不管系统发生何种改变,SQL执行计划基线被用来维护相关SQL的性能，提供了执行计划的稳定性。

SPM确保SQL的性能不会因为执行计划的改变而降低。为了保证这一点，只有SQL执行计划ACCEPTED为YES才会被优化器所使用。

2.SQL执行计划基线选择确保只有ACCEPTED为YES的执行计划才会被使用，使用SQL执行计划基线并在plan history中跟踪所有新的执行计划。plan history由ACCEPTED为NO与ACCEPTED为YES的执行计划组成。ACCEPTED为NO的执行计划可能是没有被验证或者被拒绝(验证了但性能比当前使用的执行计划要差)

3.SQL执行计划基线演进对plan history中的所有没有被验证的执行计划进行评估让其成为接受的或拒绝的执行计划

自动捕获执行计划自动捕获执行计划是由参数optimizer_capture_sql_plan_baselines=true来开启的(缺省值为false)当自动捕获执行计划被启用时，SPM档案库将会自动存储任何重复执行的SQL语句的执行计划。为了识别重复执行的SQL语句，优化器将会在每个SQL语句第一次被编译时进行标识，如果这个SQL语句再次被执行或编译，那么就会将这个SQL语句标记为重复执行的SQL，将会为这个SQL语句创建SQL plan history，它将包含生成的执行计划，比如:SQL文本，存储概要，绑当定变量与编译环境等信息。当前基于成本的执行计划将会作为SQL语句的第一个SQL执行计划基线并且这个执行计划的ACCEPTED会标记为YES，只有ACCEPTED为YES的执行计划才会被使用，如果将来对于这个SQL语句产生了新的执行计划，这个执行计划将会增加到plan history中并且将会被标记为验证过的。只有当它的性能比当前所使用的执行计划更好时才会将ACCEPTED标记为YES。

SQL> BEGIN   2  DBMS_SQLTUNE.CREATE_SQLSET(   3  sqlset_name => 'my_sql_tuning_set',   4  description => 'TEST');   5  END;   6  /   PL/SQL procedure successfully completed  SQL> DECLARE   2    l_cursor  DBMS_SQLTUNE.sqlset_cursor;   3  BEGIN   4    OPEN l_cursor FOR   5      SELECT VALUE(p)   6      FROM   TABLE (DBMS_SQLTUNE.select_cursor_cache (   7                      NULL, -- basic_filter   8                      NULL, -- object_filter   9                      NULL, -- ranking_measure1  10                      NULL, -- ranking_measure2  11                      NULL, -- ranking_measure3  12                      NULL, -- result_percentage  13                      2,    -- result_limit                          'all') --attribute_list      14                    ) p;  15    16    DBMS_SQLTUNE.load_sqlset (  17      sqlset_name     => 'my_sql_tuning_set',  18      populate_cursor => l_cursor);  19  END;  20  /   PL/SQL procedure successfully completed

或者从awr中加载

SQL> DECLARE   2    l_cursor  DBMS_SQLTUNE.sqlset_cursor;   3  BEGIN   4    OPEN l_cursor FOR   5      SELECT VALUE(p)   6      FROM   TABLE (DBMS_SQLTUNE.select_workload_repository (   7                      29027,  -- begin_snap   8                      29028,  -- end_snap   9                      NULL, -- basic_filter  10                      NULL, -- object_filter  11                      NULL, -- ranking_measure1  12                      NULL, -- ranking_measure2  13                      NULL, -- ranking_measure3  14                      NULL, -- result_percentage  15                      2,   -- result_limit  16                      'all'--attribute_list  17                      )  18                    ) p;  19    20    DBMS_SQLTUNE.load_sqlset (  21      sqlset_name     => 'my_sql_tuning_set',  22      populate_cursor => l_cursor);  23  END;  24  /   PL/SQL procedure successfully completed

SQL> select * from dba_sqlset where name='my_sql_tuning_set';           ID NAME                           OWNER                          DESCRIPTION                                                                      CREATED     LAST_MODIFIED STATEMENT_COUNT ---------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------- ------------- ---------------         15 my_sql_tuning_set              INSUR_CHANGDE                  TEST                                                                             2016/5/26 1 2016/5/26 12:               2   SQL> select * from dba_sqlset_statements where sqlset_name='my_sql_tuning_set';   SQLSET_NAME                    SQLSET_OWNER                    SQLSET_ID SQL_ID        FORCE_MATCHING_SIGNATURE SQL_TEXT                                                                         PARSING_SCHEMA_NAME            PARSING_SCHEMA_ID PLAN_HASH_VALUE BIND_DATA                                                                        BINDS_CAPTURED MODULE                                           ACTION                           ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED    FETCHES EXECUTIONS END_OF_FETCH_COUNT OPTIMIZER_COST OPTIMIZER_ENV                                                                      PRIORITY COMMAND_TYPE FIRST_LOAD_TIME     STAT_PERIOD ACTIVE_STAT_PERIOD OTHER                                                                            PLAN_TIMESTAMP    SQL_SEQ ------------------------------ ------------------------------ ---------- ------------- ------------------------ -------------------------------------------------------------------------------- ------------------------------ ----------------- --------------- -------------------------------------------------------------------------------- -------------- ------------------------------------------------ -------------------------------- ------------ ---------- ----------- ---------- ------------- -------------- ---------- ---------- ------------------ -------------- -------------------------------------------------------------------------------- ---------- ------------ ------------------- ----------- ------------------ -------------------------------------------------------------------------------- -------------- ---------- my_sql_tuning_set              INSUR_CHANGDE                          15 2823agph489xc        1.702412129134E19 select id,name,password from gl_czy where name ='系统管理'                       ZW4001                                       248      2543410975                                                                                                 R9_AppSrv.EXE                                                                           124908      32105         324         48             0             21         42         21                 21              3 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544                       3                                                                                                                                     2016/2/16 10:5        717   my_sql_tuning_set              INSUR_CHANGDE                          15 1hfffsrmgqhwp      1.11129101236943E19 select nvl(catalog_center,center_id) as  catalog_center from bs_hospital_collate INSUR_CHANGDE                                211      2429242715                                                                                                 JDBC Thin Client                                                                        141372     141372       11475          0             0           3825       3825       3825               3825              1 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544                       3                                                                                                                                     2016/5/23 14:2        718

SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => ‘TABLE_SQLSET’,schema_name   => ‘JY’);  PL/SQL procedure successfully completed  SQL> exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name =>   ‘my_sql_tuning_set’,staging_table_name => ‘TABLE_SQLSET’,staging_schema_owner =>’JY  ’);  PL/SQL procedure successfully completed

导出存储STS

[IBMP740-1:oracle:/yb_oradata/RLZYbak]$expdp jy/jy directory=dump_RLZY dumpfile=stgtab_sqlset.dmp logfile=stgtab_sqlset.log tables=TABLE_SQLSET                                                           < Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 26 May, 2016 16:57:34  Copyright (c) 2003, 2007, Oracle.  All rights reserved.  Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01":  insur_changde/******** directory=dump_RLZY dumpfile=stgtab_sqlset.dmp logfile=stgtab_sqlset.log tables=TABLE_SQLSET  Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 576 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX . . exported "INSUR_CHANGDE"."TABLE_SQLSET"              16.91 KB       2 rows . . exported "INSUR_CHANGDE"."TABLE_SQLSET_CPLANS"       24.10 KB       5 rows . . exported "INSUR_CHANGDE"."TABLE_SQLSET_CBINDS"       9.507 KB       0 rows Master table "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for INSUR_CHANGDE.SYS_EXPORT_TABLE_01 is:   /bak/dpdump/stgtab_sqlset.dmp Job "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01" successfully completed at 16:59:25

导入STS

[oracle@jyrac1 ~]$ impdp jy/jy directory=dump_dir dumpfile=stgtab_sqlset.dmp remap_schema=insur_changde:jy remap_tablespace=hygeia:users  Import: Release 11.2.0.4.0 - Production on Thu May 26 17:53:08 2016  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "JY"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "JY"."SYS_IMPORT_FULL_01":  jy/******** directory=dump_dir dumpfile=stgtab_sqlset.dmp remap_schema=insur_changde:jy remap_tablespace=hygeia:users  Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "JY"."TABLE_SQLSET"                         16.91 KB       2 rows . . imported "JY"."TABLE_SQLSET_CPLANS"                  24.10 KB       5 rows . . imported "JY"."TABLE_SQLSET_CBINDS"                  9.507 KB       0 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Job "JY"."SYS_IMPORT_FULL_01" successfully completed at Thu May 26 17:59:33 2016 elapsed 0 00:06:05

unpack STS

SQL> begin   2    DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name          => 'my_sql_tuning_set',   3                                      sqlset_owner         => 'JY',   4                                      replace              => true,   5                                      staging_table_name   => 'TABLE_SQLSET',   6                                      staging_schema_owner => 'JY');   7  END;   8  / PL/SQL procedure successfully completed

从STS加载SQL执行计划基线

SQL> declare   2  ret number;   3  begin   4  ret := dbms_spm.load_plans_from_sqlset(sqlset_name => 'my_sql_tuning_set',sqlset_owner => 'INSUR_CHANGDE');   5  end;   6  /      PL/SQL procedure successfully completed  SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where parsing_schema_name in('ZW4001','INSUR_CHANGDE');   SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- --------   -------------------------------------------------------------------------------- SQL_c4a3ce0d598be4e4           SQL_PLAN_c98yf1pcsrt74ebcd9fdf MANUAL-LOAD    YES     YES      select id,name,password from gl_czy where name ='系统管理'   SQL_b9bc48036c46e780           SQL_PLAN_bmg280dq4dtw0e85be7cc MANUAL-LOAD    YES     YES      select nvl(catalog_center,center_id) as  catalog_center from   bs_hospital_collate

2.使用Cursor Cache中的执行计划可以从cursor cache中直接加载SQL语句的执行计划到SQL Management Base中。可以通过module名，方案或SQL_ID来进过滤，可以用来标记想要捕获的SQL语句或一组SQL语句。

SQL> set autotrace traceonly SQL> select * from t1 where c1>4076;  924 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013  -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   957 |  3828 |     5   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| T1   |   957 |  3828 |     5   (0)| 00:00:01 | --------------------------------------------------------------------------    SQL> select sql_text,sql_id,plan_hash_value from v$sqlarea where sql_text like '%c1>4076%';   SQL_TEXT                                                                         SQL_ID        PLAN_HASH_VALUE -------------------------------------------------------------------------------- ------------- --------------- select * from t1 where c1>4076                                                   0006gg4zsmmcg      3617692013   SQL> var n number SQL> begin   2  :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0006gg4zsmmcg', plan_hash_value=>'3617692013', fixed =>'NO', enabled=>'YES');   3  end;   4  /  PL/SQL procedure successfully completed.  SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';   SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- --------   -------------------------------------------------------------------------------- SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076

3.从staging table中unpack SQL执行计划基线在原系统中创建staing表

SQL> set autotrace traceonly  SQL> select * from t1 where c1>4076;  924 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 1369807930  --------------------------------------------------------------------------- | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT |        |   924 |  3696 |     4   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| IDX_T1 |   924 |  3696 |     4   (0)| 00:00:01 | ---------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - access("C1">4076)  Note -----    - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement   SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY');   PL/SQL procedure successfully completed

将SQL执行计划基线从SQL Management Base中加载到staing table中

SQL> declare   2  x number;   3  begin   4   x:=dbms_spm.pack_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY',creator =>'JY');   5  end;   6  /    PL/SQL procedure successfully completed  SQL> declare   2  cursor c is select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where parsing_schema_name in('JY');   3  x number;   4  begin   5   for i in c loop   6    x:=dbms_spm.drop_sql_plan_baseline(sql_handle => ''||i.sql_handle,plan_name => ''||i.plan_name);   7   end loop;   8  end;   9  /   PL/SQL procedure successfully completed  SQL> select * from t1 where c1>4076;  924 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 1369807930  --------------------------------------------------------------------------- | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT |        |   924 |  3696 |     4   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| IDX_T1 |   924 |  3696 |     4   (0)| 00:00:01 | ---------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - access("C1">4076)

导出staing table并在目标数据库中导入，这里省略了相关操作。在目标数据库将SQL执行计划基线从staging table中加载到SQL Management Base中

SQL> declare   2  x number;   3  begin   4   x:=dbms_spm.unpack_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY');   5  end;   6  /   PL/SQL procedure successfully completed   SQL> select * from t1 where c1>4076;  924 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 1369807930  --------------------------------------------------------------------------- | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT |        |   924 |  3696 |     4   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| IDX_T1 |   924 |  3696 |     4   (0)| 00:00:01 | ---------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - access("C1">4076)  Note -----    - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement

SQL执行计划基线选择每次SQL被编译时，优化器首先使用传统的基于成本的方法来创建一个最优执行计划。如果参数optimizer_use_plan_baselines设置为true(缺省值)，那么在基于成本的执行计划被执行之前，优化器将尝试在SQL语句的SQL执行计划基线中找到一个匹配的执行计划，这是作为内存操作来执行的，如果找到了匹配的执行计划将使用该执行计划。否则，将生成一个新的执行计划并增加到plan history中，在执行计划被授受之前必须被验证。优化器将从该SQL所关联的所有执行计划中选择一个成本最低的来执行。然而，如果系统的改变造成所有已经接受的执行计划不能重现，那么优化器将会使用新的执行计划。

SQL执行计划基线也可能影响优化器对执行计划的选择。SQL执行计划基线可以被标记为固定(fixed),Fixed的SQL执行计划基线指示优化器会优先选择。如果优化器正在评估SQL执行计划基线的成本并且有一个执行计划是fixed，并且fixed的执行计划可以重现，那么优化器只会评估fixed执行计划的成本。如果fixed的执行计划不可以重现，那么优化器将会评估其它的SQL执行计划基线并从其中选择一个成本最低的执行计划。评估一个执行计划的成本远不及硬解析那样昂贵。优化器不会考虑所有可能的访问方法，只会考虑特定的访问方法。

如果选择选项1,它将触发新的执行计划被评估来比较是否它的性能要比被选择的执行计划好。如果是，那么新的执行计划会被增加到SQL执行计划基线并且ACCEPTED设置为YES。否则将新执行计划的ACCEPTED设置为NO并增加到plan history中，但它的last_verified属性将更新为当前时间戳，并且会返回一个格式化的文本报告，它包含了新执行计划与原执行计划的性能统计数据。

如果选择选项2,新执行计划将会增加到SQL执行计划基线中，而不对性能进行验证并将ACCEPTED设置为YES，同样也会生成报告。

如果选择选项3,将会验证新执行计划的性能是否比已选择的执行计划好，但是就算性能更好也会不自动将新执行计划的ACCEPTED设置为YES，在评估之后只会生成报告。

2.optimizer_use_sql_plan_baselines:控制优化器是否使用SQL执行计划基线。当启用时，优化器在编译SQL语句时会查找SQL执行计划基线中的执行计划。如果找到，优化器将会从SQL执行计划基线中选择一个成本最低的执行计划。缺省值是true。

SQL Management Base空间消耗的管理statment log,plan histories与SQL执行计划基线都被存储在SQL Management Base中。SQL Management Base是数据库数据字典的一部分存储在SYSAUX表空间中。缺省情况下，SQL Management Base的空间限制不会超过SYSAUX表空间的10%。但是也可以使用dbms_spm.configure来进行修改让其空间限制处于SYSAUX表空间的1%到50%之间。每周后台进程都会检测SQL Management Base所占用的空间大小，并且当使用空间超过设置的限制，进程就会向alert.log记录一条警告信息。也有一个每周调度清除作业来管理SQL Management Base所占用的空间。在维护窗口内这个任务会自动运行并且任何超过53周没有被使用过的执行计划将会被清除，因此确保只有SQL语句一旦运行就能保证一年之内相关执行计划是可以使用的。可以使用dbms_spm.configure来将没使用的执行计划保留周期修改为5到523周这个范围之内。

因为SQL Management Base使用SYSAUX表侬间来存储执行计划，如果SYSAUX表空间不能使用，那么SPM也将不能使用。

通过DBA视图监控SPMdbs_sql_plan_baselines视图显示了关于当前为特定SQL语句所创建的SQL执行计划基线。

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text,fixed,autopurge from dba_sql_plan_baselines where parsing_schema_name in('JY') and   sql_handle='SQL_67b129b37635284b';   SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT                                                                           FIXED AUTOPURGE ------------------------------ ------------------------------ -------------- ------- --------   -------------------------------------------------------------------------------- ----- --------- SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     YES      select * from t1 where c1>4076                                                     NO    YES SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    NO      YES      select * from t1 where c1>4076                                                     NO    YES

为了查看任何SQL执行计划基线的详细执行计划可以执行dbms_xplan.display_sql_plan_baseline

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_67b129b37635284b',plan_name => 'SQL_PLAN_6gc99qdv3aa2bdbd90e8e'));   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_67b129b37635284b SQL text: select * from t1 where c1>4076 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_6gc99qdv3aa2bdbd90e8e         Plan id: 3688435342 Enabled: NO      Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   924 |  3696 |     5   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| T1   |   924 |  3696 |     5   (0)| 00:00:01 | --------------------------------------------------------------------------   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("C1">4076)   24 rows selected   SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_67b129b37635284b',plan_name => 'SQL_PLAN_6gc99qdv3aa2bbeaed07c'));   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_67b129b37635284b SQL text: select * from t1 where c1>4076 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_6gc99qdv3aa2bbeaed07c         Plan id: 3199127676 Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 1369807930 --------------------------------------------------------------------------- | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT |        |   924 |  3696 |     4   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| IDX_T1 |   924 |  3696 |     4   (0)| 00:00:01 | ---------------------------------------------------------------------------   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - access("C1">4076)   24 rows selected

还可以通过使用v$sql视图来查看SQL语句是否使用了SQL执行计划基线。如果SQL语句正使用SQL执行计划基线，那么dba_sql_plan_baselines中的plan_name就会与v$sql中的sql_plan_baseline有相同的值。

select sql_handle, plan_name,origin, enabled, accepted,a.sql_text,fixed,autopurge from dba_sql_plan_baselines a,v$sql b  where a.parsing_schema_name in('JY') and  a.plan_name=b.sql_plan_baseline and a.signature=b.exact_matching_signature and a.sql_text like '%select * from t1 where c1>4076%';

2.系统/数据发生变化当系统/数据发生变化时可能会影响某些SQL的执行计划，潜在造成性能衰减。使用SQL执行计划能够最小化性能衰减并且稳定SQL性能。

3.应用程序升级开发新的应用程序意味着引入新的SQL语句。在标准测试环境中对于新SQL语句应用程序可能会使用合适的执行计划。如果你的系统配置不同于测试环境，SQL执行计划基线可以被用来产生更好的性能。

如果在cursor缓存中有性能良好的执行计划，那么可以将其加载到SPM中，因此你可以使用SQL执行计划基数来维护SQL的性能。

SQL> set autotrace traceonly SQL> select * from t1 where c1>4076;  924 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013  -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   957 |  3828 |     5   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| T1   |   957 |  3828 |     5   (0)| 00:00:01 | --------------------------------------------------------------------------    SQL> select sql_text,sql_id,plan_hash_value from v$sqlarea where sql_text like '%c1>4076%';   SQL_TEXT                                                                         SQL_ID        PLAN_HASH_VALUE -------------------------------------------------------------------------------- ------------- --------------- select * from t1 where c1>4076                                                   0006gg4zsmmcg      3617692013   SQL> var n number SQL> begin   2  :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0006gg4zsmmcg', plan_hash_value=>'3617692013', fixed =>'NO', enabled=>'YES');   3  end;   4  /  PL/SQL procedure successfully completed.  SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';   SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- --------   -------------------------------------------------------------------------------- SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076    SQL> select * from t1 where c1>4076;  924 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013  -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   957 |  3828 |     5   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| T1   |   957 |  3828 |     5   (0)| 00:00:01 | --------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - filter("C1">4076)  Note -----    - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement   Statistics ----------------------------------------------------------          26  recursive calls          17  db block gets          89  consistent gets           0  physical reads        3060  redo size       13042  bytes sent via SQL*Net to client        1091  bytes received via SQL*Net from client          63  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)         924  rows processed

可以看到SQL plan baseline “SQL_PLAN_6gc99qdv3aa2bdbd90e8e” used for this statement信息，说明对于该SQL语句以后的执行都将会使用所创建的SQL执行计划基线。

在启用SPM后，对于这些有了SQL执行计划基线的SQL_ID，数据库将不会再收集新的执行计划(即使在SPM被禁用的情况下也不会再收集新的执行计划)。

SPM缺省值就是启用，自动捕获SQL执行计划基线是禁用的

SQL> show parameter sql_plan_baselines  NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean     FALSE optimizer_use_sql_plan_baselines     boolean     TRUE

启用自动捕获SQL执行计划基线

SQL> alter system set optimizer_capture_sql_plan_baselines=true scope=both sid='*';  System altered.   SQL> create index idx_t1 on t1(c1);  Index created.  SQL> exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'t1',estimate_percent=>100,method_opt=>'for all columns size repeat',cascade=>true);  PL/SQL procedure successfully completed.   SQL> select * from t1 where c1>4076;  924 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013  -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   957 |  3828 |     5   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| T1   |   957 |  3828 |     5   (0)| 00:00:01 | --------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - filter("C1">4076)  Note -----    - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement    SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';   SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- --------   -------------------------------------------------------------------------------- SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076

可以看到优化器还是使用原来手动创建的SQL执行计划基线，并没有生成新的执行计划，还是执行的全表扫描，并没有使用我们创建的索引。

禁用SPM,启用自动捕获SQL执行计划基线

SQL> alter system set optimizer_use_sql_plan_baselines=false scope=both sid='*';  System altered.  SQL> select * from t1 where c1>4076;  924 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 1369807930  --------------------------------------------------------------------------- | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT |        |   957 |  3828 |     4   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| IDX_T1 |   957 |  3828 |     4   (0)| 00:00:01 | ---------------------------------------------------------------------------

可以看到执行计划使用了对索引idx_t1的范围扫描，而不是之前的全表扫描了。

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';   SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- --------   -------------------------------------------------------------------------------- SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     NO       select * from t1 where c1>4076 SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076

可以看到ORIGN为AUTO-CAPTURE的SQL执行计划基线就是自动捕获的，ENABLED为YES，但是ACCEPTED为NO，这说明虽然创建了新的SQL执行计划基线但是优化器不能使用。下面启用SPM，看优化器是否使用新创建的SQL执行计划基线。

SQL> alter system set optimizer_use_sql_plan_baselines=true scope=both sid='*';  System altered.  SQL> show parameter sql_plan_baselines  NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean     TRUE optimizer_use_sql_plan_baselines     boolean     TRUE  SQL> select * from t1 where c1>4076;  924 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013  -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   957 |  3828 |     5   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| T1   |   957 |  3828 |     5   (0)| 00:00:01 | --------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - filter("C1">4076)  Note -----    - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement

从SQL plan baseline “SQL_PLAN_6gc99qdv3aa2bdbd90e8e” used for this statement信息可以看到优化器还是使用我们手动创建的SQL执行计划基线，对表t1执行全表扫描。想要使用执行索引扫描的SQL执行计划基线就要将其对应的ACCEPTED设置为YES并将使用全表扫描的SQL执行计划基线的ACCEPTED设置为NO。但是11gr1与11gr2在修改SQL执行计划基线的ACCEPTED属性的方法是不同的。另外，一旦在11gr2中将SQL执行计划基线的ACCEPTED设置为YES，就不能再将其设置为NO。为了不使用某个SQL执行计划基线，只能将该SQL执行计划基线的ENABLED设置为NO。

在11gr1中修改SQL执行计划基线的ACCEPTED属性值的方法如下:

SQL> DECLARE   2 x number;   3  BEGIN   4  x := dbms_spm.alter_sql_plan_baseline('SQL_67b129b37635284b','SQL_PLAN_6gc99qdv3aa2bbeaed07c',attribute_name=> 'ACCEPTED',attribute_value=> 'YES');   5  END;   6  /    PL/SQL procedure successfully completed.

如果在11gr2中使用这种方法，将会得到错误信息:

SQL> DECLARE   2 x number;   3  BEGIN   4  x := dbms_spm.alter_sql_plan_baseline('SQL_67b129b37635284b','SQL_PLAN_6gc99qdv3aa2bbeaed07c',attribute_name=> 'ACCEPTED',attribute_value=> 'YES');   5  END;   6  /   ERROR at line 1: ORA-38136: invalid attribute name ACCEPTED specified ORA-06512: at "SYS.DBMS_SPM", line 2532 ORA-06512: at line 4

那么如何在11gr2中修改SQL执行计划基线的ACCEPTED值，可以使用dbms_spm.evolve_sql_plan_baseline与dbms_spm.alter_sql_plan_baseline。

SQL> SET SERVEROUTPUT ON SQL> SET LONG 10000 SQL> DECLARE   2      x clob;   3  BEGIN   4      x := dbms_spm.evolve_sql_plan_baseline('SQL_67b129b37635284b', 'SQL_PLAN_6gc99qdv3aa2bbeaed07c',verify=>'NO' ,commit=>'YES');   5      DBMS_OUTPUT.PUT_LINE(x);   6  END;   7  / -------------------------------------------------------------------------------                         Evolve SQL Plan Baseline Report -------------------------------------------------------------------------------  Inputs: -------   SQL_HANDLE = SQL_67b129b37635284b   PLAN_NAME  = SQL_PLAN_6gc99qdv3aa2bbeaed07c   TIME_LIMIT = DBMS_SPM.AUTO_LIMIT   VERIFY     = NO   COMMIT     = YES  Plan: SQL_PLAN_6gc99qdv3aa2bbeaed07c ------------------------------------   Plan was changed to an accepted plan.  -------------------------------------------------------------------------------                                  Report Summary ------------------------------------------------------------------------------- Number of plans verified: 0 Number of plans accepted: 1   PL/SQL procedure successfully completed.

这里的verify参数在修改ACCEPTED为YES之前是否需要执行相应的执行计划并与当前可以使用的执行计划比较性能。如果ACCEPTED为NO的执行计划的性能有所提高，那么将执行计划基线的ACCEPTED设置为YES。当这个参数设置为”YES”时，如果执行计划的性能有所提高就将ACCEPTED修改为YES。当这个参数设置为”NO”时，直接将ACCEPTED修改为YES。

参数commit指定是否将ACCEPTED值从NO修改为YES。当设置为YES时，执行计划基线会将ACCEPTED设置为YES并生成一个报告。当设置为NO时，一个报告会生成但不会改变ACCEPTED值。

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';   SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- --------   -------------------------------------------------------------------------------- SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     YES      select * from t1 where c1>4076 SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076    SQL> DECLARE   2      x number;   3  BEGIN   4      x := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_67b129b37635284b',plan_name =>'SQL_PLAN_6gc99qdv3aa2bdbd90e8e',attribute_name   =>'ENABLED',attribute_value =>'NO');   5  END;   6  /  PL/SQL procedure successfully completed.  SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';   SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- --------   -------------------------------------------------------------------------------- SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     YES      select * from t1 where c1>4076 SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    NO      YES      select * from t1 where c1>4076

现在手动创建的使用全表扫描的SQL执行计划基线的ENABLED为NO，新生成的使用索引范围扫描的SQL执行计划基线的ENABLED与ACCEPTED值为YES，说明优化器可以使用。

SQL> select * from t1 where c1>4076;  924 rows selected.   Execution Plan ---------------------------------------------------------- Plan hash value: 1369807930  --------------------------------------------------------------------------- | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT |        |   957 |  3828 |     4   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| IDX_T1 |   957 |  3828 |     4   (0)| 00:00:01 | ---------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - access("C1">4076)  Note -----    - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement

可以看到优化器确实使用了使用索引范围扫描的SQL执行计划基线。