任何数据库应用程序的性能都严重依赖于它的查询执行。当没有任何用户介入时Oracle优化器是完全能够评估最优执行计划的,SQL语句的执行计划因为各种原因，比如：重新收集优化器统计信息，改变优化器参数或方案/元数据定义而可能会出现改变。但是不能保证执行计划的改变总是会提高性能，因此会导致有些管理员会锁定SQL的执行计划(Stored Outline)或锁定统计信息。然而，这样做会导致不能使用新版优化器为了提供高SQL性能所提供的一些新特性或新的访问路径。
Oracle的每一个新版本中都包含了一些新特性用来提高查询的性能。然而某些应用（或部分应用）是静态的并且比起在特定情况下提高性能来说可预测的性能是更可取的。Oracle11G提供了SPM来完全透时的控制执行计划的演变。使用SPM优化器自动管理执行计划并且确保只有已经或被验证的执行计划才会被使用。当找到SQL语句的新执行计划时，但直到它被数据库进行验证或其性能比当前执行计划高才会被使用。
SQL Plan Management是一种预防机制，记录与评估SQL语句的执行计划。这种机制可以创建一个SQL执行计划基线，它是由一组已知性能良好的执行计划组成。不管系统发生何种改变,SQL执行计划基线被用来维护相关SQL的性能，提供了执行计划的稳定性。
SPM确保SQL的性能不会因为执行计划的改变而降低。为了保证这一点，只有SQL执行计划ACCEPTED为YES才会被优化器所使用。
SPM有三个主要组件:
1.SQL执行计划基线捕获
创建SQL执行计划基线这代表对所有相关SQL语句授受这个执行计划。SQL执行计划基线被存储在SYSAUX表空间中的SQL Management BASE中的plan history中，可以通过dba_sql_plan_baselines视图进行查询。
2.SQL执行计划基线选择
确保只有ACCEPTED为YES的执行计划才会被使用，使用SQL执行计划基线并在plan history中跟踪所有新的执行计划。plan history由ACCEPTED为NO与ACCEPTED为YES的执行计划组成。ACCEPTED为NO的执行计划可能是没有被验证或者被拒绝(验证了但性能比当前使用的执行计划要差)
3.SQL执行计划基线演进
对plan history中的所有没有被验证的执行计划进行评估让其成为接受的或拒绝的执行计划
SQL执行计划基线捕获
为了让SPM起作用必须首先使用当前基于成本的执行计划来作为SQL Management Base，这将成为每个SQL语句的SQL执行计划基线。有两种不同的方法来创建SQL Management Base:
.自动捕获执行计划
.批量加载执行计划
自动捕获执行计划
自动捕获执行计划是由参数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。
批量加载执行计划
批量加载执行计划在数据库从之前的版本升级致电Oracle 11g或部署新应用程序时很有用。批量加载可以与自动加载执行计划结合使用或者代替自动加载执行计划。执行计划被批量加载时会自动接受创建的新SQL执行计划基线或者向已经存在的增加一个。SQL anagement BASE可以使用三种不同的方法来批量加载执行计划。
1.从指定的SQL Tuning Set(STS)中加载执行计划
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执行计划基线并从其中选择一个成本最低的执行计划。评估一个执行计划的成本远不及硬解析那样昂贵。优化器不会考虑所有可能的访问方法，只会考虑特定的访问方法。
SQL执行计划的演进
当优化器为SQL语句找到一个新执行计划时，这个新的执行计划会作为未被接受的执行计划被增加到plan history中，在它成为接受(ACCEPTED=YES)的执行计划之前需要被验证。可以通过使用EM或执行dbms_spm.evolve_sql_plan_baseline来演进。使用这些方法有三种选择:
1.只有新执行计划比现有的SQL执行计划基线性能更好才将ACCEPTED设置为YES
2.不进行性能验证就将ACCEPTED设置为YES
3.运行性能比较并生成一个报告但不演进新的执行计划
如果选择选项1,它将触发新的执行计划被评估来比较是否它的性能要比被选择的执行计划好。如果是，那么新的执行计划会被增加到SQL执行计划基线并且ACCEPTED设置为YES。否则将新执行计划的ACCEPTED设置为NO并增加到plan history中，但它的last_verified属性将更新为当前时间戳，并且会返回一个格式化的文本报告，它包含了新执行计划与原执行计划的性能统计数据。
如果选择选项2,新执行计划将会增加到SQL执行计划基线中，而不对性能进行验证并将ACCEPTED设置为YES，同样也会生成报告。
如果选择选项3,将会验证新执行计划的性能是否比已选择的执行计划好，但是就算性能更好也会不自动将新执行计划的ACCEPTED设置为YES，在评估之后只会生成报告。
SQL Management Base的使用与管理
有两个参数用来控制SPM
1.optimizer_capture_sql_plan_baselines:对重复执行的SQL语句是否自动捕获新的SQL执行计划基线。缺省值是false。
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视图监控SPM
dbs_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%';
SQL执行计划在以下情况可以用来提高或维护SQL性能:
1.数据库升级
数据库升级会安装新版的优化器，这通常会改变一小部分SQL的执行计划，大部分发生改变的执行计划性能没有变化或者有所提高。然而，特定的执行计划发生改变可能造成性能的衰减。使用SQL执行计划基线可以有效的最小化由数据库升级而引起的性能衰减。
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执行计划基线。