sql计划基线简单介绍
上一篇介绍了关于oracle的stored outline有关内容，通过测试我们发觉stored outline有很多不稳定的地方，在oracle 11g后stored outline已经被sql计划基线所取代，虽然oracle 11g也支持stored outline，但是官方并不建议继续使用stored outline，而是使用更为
上一篇介绍了关于oracle的stored outline有关内容，通过测试我们发觉stored outline有很多不稳定的地方，在oracle 11g后stored outline已经被sql计划基线所取代，虽然oracle 11g也支持stored outline，但是官方并不建议继续使用stored outline，而是使用更为灵活的sql plan baseline，下面小鱼也来对sql plan baseline做一些简单的说明和介绍。
Sql计划基线也是一个与sql语句相关联的对象，也是用来固化sql语句的执行计划的，而它的工作原理大体是：
简单来说就是先根据统计信息优化器评估最优的执行计划，然后对sql语句整理（忽略大小写 空格等）生成一个签名，基于此签名查询数据字典，只要发现可接受（信任的）并且又有相同签名的sql计划基线可用，因为根据sql文本生成的签名是个hash value，还需要确认优化的sql和sql计划基线对应的sql语句是否一致，如果sql语句一致，则使用sql计划基线的执行计划，而如果有多个sql计划基线，优化器会选择代价最小的那个去执行。
Oracle 官档有个通俗易懂的介绍：
Each time the database compiles a SQL statement, the optimizer does the following:
1. Uses a cost-based search method to build a best-cost plan
2. Tries to find a matching plan in the SQL plan baseline
3. Does either of the following depending on whether a match is found:
If found, then the optimizer proceeds using the matched plan
If not found, then the optimizer evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost
捕获sql计划基线：
和存储游标一样捕获sql计划基线有大体下面三种办法：自动捕获、从库缓存加载、从sql调优集加载,下面来简单介绍上述三种捕获方式
自动捕获；
当设置初始化动态参数optimizer_capture_sql_plan_baseline为true，优化器将自动创建sql计划基线，这个参数可以在session和system级别设置。
当自动捕获开启后，优化器会为每条重复执行过的sql语句存储新的sql计划基线，这个过程大体是:sql第一次执行时，仅存储sql的签名到日志中，然后第二次执行时，如果不存在与此语句相对应的sql计划基线，就新建一个并存储起来，如果与sql语句相对应的sql计划基线已经存在，优化器会对比当前执行计划和基于sql计划基线的执行计划，如果不匹配那么这个新的执行计划将被存储为新的sql计划基线。然后优化器会在sql计划基线辅助下产生执行计划。
当一个新的sql计划基线被存储时：
1 如果这是此sql语句相关的第一个sql计划基线，此sql计划基线被存储为accepted状态，优化器将能够使用
2 如果此sql语句已存在对应的sql计划基线，新的sql计划基线将被存储为不可接受状态，优化器将不能够使用它。
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
SQL> select object_name from t where object_id=1000;
OBJECT_NAME
--------------------------------------------------------------------------------
SYS_LOB0000000997C00031$$
SQL> select object_name from t where object_id=1000;
OBJECT_NAME
--------------------------------------------------------------------------------
SYS_LOB0000000997C00031$$
SQL> alter session set optimizer_capture_sql_plan_baselines=false;
SQL> select sql_handle,plan_name,sql_text,parsing_schema_name,enabled,accepted from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT PARSING_SCHEMA_NAME ENA ACC
-------------------------------------------------------------------------------- ------------------------------ --- ---
SQL_d546892829d1fd62 SQL_PLAN_dajn950nx3zb294ecae5c
select object_name from t where object_id=1000 XIAOYU YES YES
这里发现重复执行的sql已经自动捕捉到了sql计划基线中，由于这是这个sql的第一个计划基线，所以accepted被标记为yes。
而我们再次执行上面这个sql，这里发现note里面已经显示使用了sql计划基线
SQL> select object_name from t where object_id=1000;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000000997C00031$$
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0bjnptjy6ctk2, child number 0
-------------------------------------
select object_name from t where object_id=1000
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 356 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 29 | 356 (1)| 00:00:05 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1000)
Note
-----
- SQL plan baseline SQL_PLAN_dajn950nx3zb294ecae5c used for this statement
22 rows selected.
而如果在object_id上面创建索引，再次执行发现此时query还是会走sql计划基线中存储的执行计划。
SQL> create index ind_objecit_id on t(object_id);
Index created.
SQL> select object_name from t where object_id=1000;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000000997C00031$$
SQL> select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0bjnptjy6ctk2, child number 1
-------------------------------------
select object_name from t where object_id=1000
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 356 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 29 | 356 (1)| 00:00:05 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1000)
Note
-----
- SQL plan baseline SQL_PLAN_dajn950nx3zb294ecae5c used for this statement
22 rows selected.
这个也验证了我们上面总结的第一点，自动捕捉的第一个sql计划基线是能够使用的。
接下来我们在创建索引后继续自动捕捉上面的query的计划基线：
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
SQL> select object_name from t where object_id=1000;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000000997C00031$$
SQL> select object_name from t where object_id=1000;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000000997C00031$$
SQL> alter session set optimizer_capture_sql_plan_baselines=false;
SQL> select sql_handle,plan_name,sql_text,parsing_schema_name,enabled,accepted from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT PARSING_SCHEMA_NAME ENA ACC
-------------------------------------------------------------------------------- ------------------------------ --- ---
SQL_d546892829d1fd62 SQL_PLAN_dajn950nx3zb294ecae5c
select object_name from t where object_id=1000 XIAOYU YES YES
SQL_d546892829d1fd62 SQL_PLAN_dajn950nx3zb29806dc4d
select object_name from t where object_id=1000 XIAOYU YES NO
此时我们发现sql_handle都是一致的，但是plan_name确不相同，看出新产生的这个sql plan baseline的accepted是no，此时这个sql计划基线是不可以使用的，这个也验证了我们上面总结的自动捕获下同样的sql产生的第二个sql计划基线默认是不可以使用的。
从库缓存加载：
基于库缓存中的游标，可使用dbms_spm中的函数load_plans_from_cursor_cache来将sql计划基线手动导入到数据字典中，用这些函数加载的sql计划基线被都标记为accepted状态，优化器可以立即使用。
比如如下查看v$sql发现有如下的query语句
SQL> select sql_id,SQL_TEXT,CHILD_NUMBER from v$sql where sql_text like 'select /*+full(t)*/ object_name from t where object_id=10%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
CHILD_NUMBER
------------
6q4zr5kyjpnvj
select /*+full(t)*/ object_name from t where object_id=10
0
下面通过dbms_spm.load_PLANS_FROM_CURSOR_CACHE从库缓存中加入到sql计划基线中
SQL> variable cnt number;
SQL> exec :cnt:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'6q4zr5kyjpnvj');
PL/SQL procedure successfully completed.
SQL> select sql_handle,plan_name,sql_text,parsing_schema_name,enabled,accepted from dba_sql_plan_baselines where sql_text like '%/*+full(t)*/%';
SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT PARSING_SCHEMA_NAME ENA ACC
-------------------------------------------------------------------------------- ------------------------------ --- ---
SQL_b034699b2917840e SQL_PLAN_b0d39mcnjg10f94ecae5c
select /*+full(t)*/ object_name from t where object_id=10 XIAOYU YES YES
通过dbms_spm.load_plans_from_cursor_cache加载库缓存到sql计划基线后可以马上使用
SQL> set autotrace traceonly exp
SQL> select /*+full(t)*/ object_name from t where object_id=10 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 356 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 1 | 29 | 356 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Note
-----
- SQL plan baseline "SQL_PLAN_b0d39mcnjg10f94ecae5c" used for this statement
dbms_spm.load_plans_from_cursor_cache的function比较多，可以参考官方文档，这里由于篇幅不再列出
下面我们思考一个问题，比如有一些开发人员喜欢在程序中写上hint,比如index hint、full的hint，nested loop或者hash join等，而这些hint往往随着数据库数据的变化不再高效，深圳会变得特别慢，而我们又不方便去修改代码，此时我们能否用sql计划基线去处理这个问题的，其实是可以的，下面小鱼简单列举一个例子以供参考：
SQL> update t set object_type='TABLE' where rownum
SQL> commit;
SQL> create index ind_type on t(object_type);
SQL> execute dbms_stats.gather_table_stats(ownname=>'XIAOYU',tabname=>'T');
SQL> set autotrace traceonly exp
SQL> select /*+index(t ind_type)*/* from t where object_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 4230327298
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57230 | 5365K| 1736 (1)| 00:00:21 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 57230 | 5365K| 1736 (1)| 00:00:21 |
|* 2 | INDEX RANGE SCAN | IND_TYPE | 57230 | | 142 (1)| 00:00:02 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
SQL> select sql_id,sql_text,child_number,plan_hash_value from v$sql where sql_text like 'select /*+index(t ind_type)*/* from t where object_type=''TABLE''%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
CHILD_NUMBER PLAN_HASH_VALUE
------------ ---------------
acxaza81hv1a6
select /*+index(t ind_type)*/* from t where object_type='TABLE'
0 4230327298
此时我们把这个执行计划加入sql计划基线
SQL> variable cnt number;
SQL>exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_Id=>'acxaza81hv1a6',plan_hash_value=>4230327298);
PL/SQL procedure successfully completed.
SQL> select sql_handle,sql_text,accepted,plan_name from dba_sql_plan_baselines where sql_text like 'select /*+index(t ind_type)*/* from t where object_type=''TABLE''%';
SQL_HANDLE SQL_TEXT ACC
------------------------------ -------------------------------------------------------------------------------- ---
PLAN_NAME
------------------------------
SQL_3457d203d679c1ef select /*+index(t ind_type)*/* from t where object_type='TAB YES
LE'
SQL_PLAN_38pyk0gb7mhggfc10600c
接下来我们按照正确的sql写法执行上述同样逻辑的语句
SQL> set autotrace traceonly exp
SQL> select * from t where object_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57230 | 5365K| 356 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 57230 | 5365K| 356 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
查看正确sql写法的sql_id、plan_hash_value等信息
SQL> set autotrace off
SQL> select sql_id,plan_hash_value,child_number,sql_text from v$sql where sql_text like 'select * from t where object_type=''TABLE''%';
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER
------------- --------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
781ph1vsf599k 1601196873 0
select * from t where object_type='TABLE'
再次用dbms_spm.load_plans_from_cursor_cache也加载正确的库缓存的执行计划到sql计划基线中
SQL> exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_Id=>'781ph1vsf599k',plan_hash_value=>1601196873,sql_handle=>'SQL_3457d203d679c1ef');
PL/SQL procedure successfully completed.
SQL> select sql_handle,sql_text,accepted,plan_name from dba_sql_plan_baselines where sql_text like 'select /*+index(t ind_type)*/* from t where object_type=''TABLE''%';
SQL_HANDLE SQL_TEXT ACC
------------------------------ -------------------------------------------------------------------------------- ---
PLAN_NAME
------------------------------
SQL_3457d203d679c1ef select /*+index(t ind_type)*/* from t where object_type='TAB YES
LE'
SQL_PLAN_38pyk0gb7mhgg94ecae5c
SQL_3457d203d679c1ef select /*+index(t ind_type)*/* from t where object_type='TAB YES
LE'
SQL_PLAN_38pyk0gb7mhggfc10600c
此时我们看系统同一个sql_handle中出现了两个可选择的计划基线，然后我们删除先前的那个计划基线，
SQL>exec :cnt:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_3457d203d679c1ef',plan_name=>'SQL_PLAN_38pyk0gb7mhggfc10600c');
PL/SQL procedure successfully completed.
SQL> select /*+index(t ind_type)*/* from t where object_type='TABLE';
58272 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57230 | 5365K| 356 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 57230 | 5365K| 356 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
Note
-----
- SQL plan baseline "SQL_PLAN_38pyk0gb7mhgg94ecae5c" used for this statement
这里出现了即使加上了hint index优化器还是会选择基线中的执行计划也就是全表扫描。
上述的测试表明了dbms_spm.load_plans_from_cursor_cache可以直接从库缓存拉执行计划到sql计划基线中，也就是我们能够让一个hint index拉一个全表扫描的执行计划到计划基线，然后删除不高效的计划基线，当然也可以留着让优化器自行选择高效的计划基线。
从sql调优集或者awr报告加载：
Dbms_spm中有个load_plans_from_sqlset来从sql调优集中加载到sql计划基线，只需要指定sql调优集拥有者和名称就可以实现加载，用load_plan_from_sqlset加载的sql计划基线都被标记为可接受状态，优化器能够立即使用
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1');
END;
/
如何展示sql计划基线：
在之前说到如何查看执行计划时，有个重要的包dbm_xplan，其中有display_sql_plan_baseline就是用来展示sql计划基线的，当然也可以查看dba_sql_plan_baselines等视图来查看sql计划基线。
FUNCTION DISPLAY_SQL_PLAN_BASELINE RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_3457d203d679c1ef'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_3457d203d679c1ef
SQL text: select /*+index(t ind_type)*/* from t where object_type='TABLE'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_38pyk0gb7mhgg94ecae5c Plan id: 2498539100
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57230 | 5365K| 356 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T | 57230 | 5365K| 356 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE')
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_38pyk0gb7mhggfc10600c Plan id: 4228931596
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 4230327298
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57230 | 5365K| 1736 (1)| 00:00:21 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 57230 | 5365K| 1736 (1)| 00:00:21 |
|* 2 | INDEX RANGE SCAN | IND_TYPE | 57230 | | 142 (1)| 00:00:02 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
44 rows selected.
Sql计划基线演化：
当优化器认识到有一个于通过sql计划基线产生的执行计划不同的更高效的执行计划存在时，就会自动创建一个新的不可接受状态的sql计划基线，即使此时优化器最终不会使用它。而当要优化器考虑这个新生成的不可接受状态的sql计划基线，此时必须演化，演化过程中如果观察新的不可接受的sql计划基线确实效率更高，oracle将会去修改这个计划基线为可接受状态，下面来简单演示下：
SQL> drop index IND_OBJECIT_ID;
Index dropped.
SQL> select object_type from t where object_id=10000;
OBJECT_TYPE
-------------------
TABLE
SQL> select sql_id,plan_hash_value from v$sql where sql_text like 'select object_type from t where object_id=10000';
SQL_ID PLAN_HASH_VALUE
------------- ---------------
9r9xhspdhg56d 1601196873
SQL> exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'9r9xhspdhg56d');
PL/SQL procedure successfully completed.
SQL> select sql_handle,sql_text,accepted from dba_sql_plan_baselines where sql_text like 'select object_type from t where object_id=10000';
SQL_HANDLE SQL_TEXT ACC
------------------------------ -------------------------------------------------------------------------------- ---
SQL_2102307e5161e529 select object_type from t where object_id=10000 YES
SQL> create index ind_id on t(object_id);
Index created.
SQL> select object_type from t where object_id=10000;
OBJECT_TYPE
-------------------
TABLE
SQL> select sql_handle,sql_text,accepted,plan_name from dba_sql_plan_baselines where sql_text like 'select object_type from t where object_id=10000';
SQL_HANDLE SQL_TEXT ACC PLAN_NAME
------------------------------ -------------------------------------------------------------------------------- --- ------------------------------
SQL_2102307e5161e529 select object_type from t where object_id=10000 YES SQL_PLAN_220jhgt8q3t9994ecae5c
SQL_2102307e5161e529 select object_type from t where object_id=10000 NO SQL_PLAN_220jhgt8q3t99f36215ea
这里由于新创建了索引，优化器再次评估这个sql发现执行计划相比sql计划基线的效率更高，此时会新生成一个不可接受的sql计划基线。
SQL> select dbms_spm.evolve_sql_plan_baseline(
2 sql_handle=>'SQL_2102307e5161e529',
3 plan_name=>null,
4 verify=>'yes',
5 commit=>'yes') from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_2102307E5161E529',PLAN_NAME=>
--------------------------------------------------------------------------------
-----------------------------------------------------------
--------------------
Evolve SQL Plan Baseline Report
-------------------------------------------
------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_2102307e5161e529
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = yes
COMMIT = yes
关于verify和commit参数：
Verify如果为yes（默认值）将执行sql语句演化，如果设置为false将不需要演化
Commit如果为yes（默认值）数据字典将按照演化的结果进行修改，如果设置为no，参数verify设置为yes，只进行演化但是不会修改数据字典。
Plan: SQL_PLAN_220jhgt8q3t99f36215ea
------------------------------------
Plan was verified: Time used .13 seconds.
Plan passed performance criterion: 423.4
7 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan
Test Plan Stats Ratio
------------- --
------- -----------
Execution Status: COMPLETE COMPLE
TE
Rows Processed: 1 1
Elapsed Time(ms): 11.106 .102 108.88
CPU Time(ms): 11.109 .111 100.08
Buffer Gets: 1274 3 424.67
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
Report Summary
-----------------------------------------
--------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
SQL> select sql_handle,sql_text,accepted,plan_name from dba_sql_plan_baselines where sql_text like 'select object_type from t where object_id=10000';
SQL_HANDLE SQL_TEXT ACC PLAN_NAME
------------------------------ -------------------------------------------------------------------------------- --- ------------------------------
SQL_2102307e5161e529 select object_type from t where object_id=10000 YES SQL_PLAN_220jhgt8q3t9994ecae5c
SQL_2102307e5161e529 select object_type from t where object_id=10000 YES SQL_PLAN_220jhgt8q3t99f36215ea
经过演化后sql计划基线已经成为accepted状态，再次执行上面的sql，oracle会选择成本最小的sql计划基线来执行sql语句。
SQL> set autotrace traceonly exp
SQL> select object_type from t where object_id=10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 4043158466
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=10000)
2
Note
-----
- SQL plan baseline "SQL_PLAN_220jhgt8q3t99f36215ea" used for this statement
激活sql计划基线：当optimizer_use_sql_plan_baselines参数为true时为启用sql计划基线，这个参数默认就是true。
SQL> show parameter optimizer_use_sql_plan_baseline
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_sql_plan_baselines boolean TRUE
更改sql计划基线：
通过dbms_spm中的过程alter_sql_plan_baseline可以修改sql计划基线创建时指定的一些属性。
SQL> desc dbms_spm;
FUNCTION ALTER_SQL_PLAN_BASELINE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
ATTRIBUTE_NAME VARCHAR2 IN
ATTRIBUTE_VALUE VARCHAR2 IN
其中attribute_name是属性名称，然后attribute_value是属性值，参数attribute_name可以接受以下几个值：
Enabled-这个属性可以设置为yes或者no，设置为yes时，此sql计划基线才能被优化器所使用
Fixed-这个属性设置为yes时，sql计划基线将不能被演化
Autopurge-可以设置为yes或者no，如果设置为yes，如果sql计划基线在保留期内没有被使用过，就会自动移除。
Plan_name-此属性被用来更改sql计划明
Description-此属性被用来给sql计划基线加上一些描述
激动sql计划基线
初始化参数optimizer_use_sql_plan_baselines设置为true时，优化器才会考虑使用sql计划基线，参数optimizer_use_sql_plan_baselines参数默认就是true。
还有在库之前迁移sql计划基线，这个由于较复杂，小鱼个人觉得一般情况下这个特性使用也很少，这里就不再列出了，关于sql基线跟存储提纲一样是为了固化执行计划，在oracle 11g中sql计划基线基本已经完全取代了存储提纲，掌握sql计划基线的使用将对我们固化执行计划，生成更优的执行计划非常有帮助。
原文地址：sql计划基线简单介绍, 感谢原作者分享。