从oracle 10g开始，oracle推出了存储提纲用来固定sql语句的执行计划，在oracle 11g后存储提纲被sql计划基线（sql plan baseline）取而代之，sql plan baseline也和存储提纲一样被用来提供稳定的执行计划，简单来讲也是固化sql语句执行计划的，而同样固化执行计划的还有hint、sql profile、存储提纲等，下面小鱼简单来对存储提纲和sql计划基线做一定的介绍。
oracle stored outline（存储提纲）和sql计划基线最主要的作用就是用来保持固定sql语句的执行计划，让sql语句的执行计划在数据库环境变更（统计信息、优化器参数、表结果变更等）后还能保持原来的执行计划。
比如我们升降级数据库会由于优化器版本的不同造成执行计划差异、统计信息不准确导致执行计划可能出现波动、优化器的bug等造成执行计划不合理等，我们都可以采取oracle的存储提纲和sql计划基线来固化sql的执行计划。
下面来看看oracle的stored outline是如何工作的：
存储提纲是一个和sql语句相关的对象，存储在数据字典里的，优化器正是根据存储提纲关联的执行计划来固化sql语句执行计划。
首先对sql语句进行标准化，移除空白并转换非字段值的部分为大写，为标准化的sql语句计算一个签名。注意该签名是一个hash value，然后当发现相同签名的存储提纲时，就会去检查当前执行的sql语句和存储提纲记录的sql语句是否相同，如果相同则使用存储提纲内的执行计划。
关于创建存储提纲有两种方法，分别是自动创建和手动创建，自动创建需要结合初始化参数create_stored_outlines，手动创建可以用create outline或者dbms_outln.create_outline来创建。
这里小鱼主要来演示下手动创建存储提纲。
sql> create table t_out01 as select * from dba_objects;
table created.
sql> create or replace outline outline01 for category outline on select object_name from t_out01 where object_id=1099;
outline created.
sql> select name,category,used,enabled,sql_text from user_outlines;
name category used enabled
------------------------------ ------------------------------ ------ --------
sql_text
--------------------------------------------------------------------------------
outline01 outline unused enabled
select object_name from t_out01 where object_id=1099
sql> select hint from user_outline_hints where name='outline01';
hint
--------------------------------------------------------------------------------
full(@sel$1 t_out01@sel$1)
outline_leaf(@sel$1)
all_rows
opt_param('_push_join_union_view' 'false')
opt_param('_push_join_predicate' 'false')
optimizer_features_enable('10.2.0.4')
ignore_optim_embedded_hints
7 rows selected.
sql> alter system set use_stored_outlines=outline;
system altered.
sql> select object_name from t_out01 where object_id=1099;
object_name
--------------------------------------------------------------------------------------------------------------------------------
v$parameter
sql> set linesize 140
sql> select * from table(dbms_xplan.display_cursor(null,null));
plan_table_output
--------------------------------------------------------------------------------------------------------------------------------------------
sql_id a5fdfsf61dk3f, child number 0
-------------------------------------
select object_name from t_out01 where object_id=1099
plan hash value: 43222384
-----------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
-----------------------------------------------------------------------------
| 0 | select statement | | | | 234 (100)| |
|* 1 | table access full| t_out01 | 1 | 26 | 234 (1)| 00:00:03 |
plan_table_output
--------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(object_id=1099)
18 rows selected.
sql> create index ind_obj_id on t_out01(object_id);
index created.
sql> select object_name from t_out01 where object_id=1099;
object_name
--------------------------------------------------------------------------------------------------------------------------------
v$parameter
execution plan
----------------------------------------------------------
plan hash value: 2483087502
------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | table access by index rowid| t_out01 | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | index range scan | ind_obj_id | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access(object_id=1099)
这个数据库版本是linux的10.2.0.4.0的版本，又尝试了windows的一个10.2.0.4.0版本的数据库，结果还是如此，就是创建的outline优化器并不去使用它，刚开始小鱼觉得可能是否和bug有关的，于是换了一个11.2.0.1的库还是如此，群里的一个热情的朋友测试存储提纲也没问题，人品真的很有点差啊，不过对于这类问题我们可不能就直接扔到一边去，小鱼一直坚信能遇见就是幸运的。
对于上述测试小鱼均都是使用的sys用户测试的，我们换一个用户来试试看
sql> show user;
user is xiaoyu
sql> create table t_out01 as select * from dba_objects;
table created.
sql> create or replace outline outline1 for category cate on select object_name
from t_out01 where object_id=1009;
outline created.
sql> set autotrace on exp
sql> set linesize 140;
sql> select object_name from t_out01 where object_id=1009;
object_name
--------------------------------------------------------------------------------
------------------------------------------------
v$log
execution plan
----------------------------------------------------------
plan hash value: 43222384
-----------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
-----------------------------------------------------------------------------
| 0 | select statement | | 8 | 632 | 158 (1)| 00:00:02 |
|* 1 | table access full| t_out01 | 8 | 632 | 158 (1)| 00:00:02 |
-----------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(object_id=1009)
note
-----
- dynamic sampling used for this statement
sql> set autotrace off
sql> select name,category,used,sql_text from user_outlines;
name category used
------------------------------ ------------------------------ ------
sql_text
--------------------------------------------------------------------------------
outline1 cate unused
select object_name from t_out01 where object_id=1009
sql> alter session set use_stored_outlines=cate;
session altered.
sql> set autotrace on exp
sql> select object_name from t_out01 where object_id=1009;
object_name
--------------------------------------------------------------------------------
------------------------------------------------
v$log
execution plan
----------------------------------------------------------
plan hash value: 43222384
-----------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
-----------------------------------------------------------------------------
| 0 | select statement | | 581 | 45899 | 158 (1)| 00:00:02 |
|* 1 | table access full| t_out01 | 581 | 45899 | 158 (1)| 00:00:02 |
-----------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(object_id=1009)
note
-----
- outline outline1 used for this statement
看出这里的note表示已经使用了outline outline1，下面我们建立索引
sql> create index indid on t_out01(object_id);
index created.
sql> select object_name from t_out01 where object_id=1009;
object_name
--------------------------------------------------------------------------------
------------------------------------------------
v$log
execution plan
----------------------------------------------------------
plan hash value: 1679505401
--------------------------------------------------------------------------------
-------
| id | operation | name | rows | bytes | cost (%cpu)| tim
e |
--------------------------------------------------------------------------------
-------
| 0 | select statement | | 581 | 45899 | 4 (0)| 00:
00:01 |
| 1 | table access by index rowid| t_out01 | 581 | 45899 | 4 (0)| 00:
00:01 |
|* 2 | index range scan | indid | 232 | | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
predicate information (identified by operation id):
---------------------------------------------------
2 - access(object_id=1009)
note
-----
- outline outline1 used for this statement
但是建议索引后，执行计划好像还是变化了，从之前的全表扫描变为了索引扫描。
sql> set autotrace off
sql> select name,category,used,sql_text from user_outlines;
name category used
------------------------------ ------------------------------ ------
sql_text
--------------------------------------------------------------------------------
outline1 cate used
select object_name from t_out01 where object_id=1009
sql> select hint from dba_outline_hints where name='outline1';
hint
--------------------------------------------------------------------------------
full(@sel$1 t_out01@sel$1)
outline_leaf(@sel$1)
all_rows
optimizer_features_enable('10.2.0.4')
ignore_optim_embedded_hints
防止set autotrace的执行计划不准，我们用dbms_xplan.display_cursor看看真实的执行计划
sql> select * from table(dbms_xplan.display_cursor(null,null));
plan_table_output
--------------------------------------------------------------------------------
------------------------------------------------------------
sql_id 7k0h3uvq0xufh, child number 0
-------------------------------------
select object_name from t_out01 where object_id=1009
plan hash value: 1679505401
--------------------------------------------------------------------------------
-------
| id | operation | name | rows | bytes | cost (%cpu)| tim
e |
--------------------------------------------------------------------------------
-------
| 0 | select statement | | | | 4 (100)|
|
| 1 | table access by index rowid| t_out01 | 581 | 45899 | 4 (0)| 00:
00:01 |
|* 2 | index range scan | indid | 232 | | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
predicate information (identified by operation id):
---------------------------------------------------
2 - access(object_id=1009)
note
-----
- outline outline1 used for this statement
note是显示使用了outline outline1，但是执行计划依然还是索引扫描，而我们查看outline outline1对应的hint中还是原来的全表扫描的执行计划，对于这个问题问了一些朋友，都没有合适的答案。
首先这里我们确定了stored outline对于sys用户无效，这个还没有找到合适的资料和文档来说明，第二个是10.2.0.4版本的数据库，虽然执行计划提示使用了存储提纲，但是执行计划确没有使用stored outline的执行计划，找了半天在mos上发现了一篇关于stored outline的bug ，有兴趣的朋友可以试试打patch看看！
bug 6455659 - stored outlines do not work in multibyte db
而在oracle性能诊断艺术那本书中也提到了即使是使用了存储提纲，执行计划依然可能变化，也正是这个原因，存储提纲很少在生产环境中大批量的使用。
小鱼再找来一个11.2.0.1的数据库来进行存储提纲的测试：
sql> select * from v$version;
banner
--------------------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.1.0 - 64bit production
pl/sql release 11.2.0.1.0 - production
core 11.2.0.1.0 production
tns for linux: version 11.2.0.1.0 - production
nlsrtl version 11.2.0.1.0 - production
sql> create table t as select * from dba_objects;
table created.
sql> create or replace outline outline01 for category cate on select object_name from t where object_id=1090;
outline created.
sql> show user;
user is test
sql> set autotrace on exp
sql> set linesize 140
sql> select object_name from t where object_id=1090;
object_name
--------------------------------------------------------------------------------------------------------------------------------
sys_lob0000001087c00012$$
execution plan
----------------------------------------------------------
plan hash value: 1601196873
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | 12 | 948 | 299 (1)| 00:00:04 |
|* 1 | table access full| t | 12 | 948 | 299 (1)| 00:00:04 |
--------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(object_id=1090)
note
-----
- dynamic sampling used for this statement (level=2)
sql> create index ind_id on t(object_id);
index created.
sql> select object_name from t where object_id=1090;
object_name
--------------------------------------------------------------------------------------------------------------------------------
sys_lob0000001087c00012$$
execution plan
----------------------------------------------------------
plan hash value: 4043158466
--------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | table access by index rowid| t | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | index range scan | ind_id | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access(object_id=1090)
note
-----
- dynamic sampling used for this statement (level=2)
sql> alter system set use_stored_outlines=cate;
system altered.
sql> select object_name from t where object_id=1090;
object_name
--------------------------------------------------------------------------------------------------------------------------------
sys_lob0000001087c00012$$
execution plan
----------------------------------------------------------
plan hash value: 1601196873
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | 895 | 70705 | 300 (1)| 00:00:04 |
|* 1 | table access full| t | 895 | 70705 | 300 (1)| 00:00:04 |
--------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(object_id=1090)
note
-----
- outline outline01 used for this statement
sql> alter session set use_stored_outlines=false;
session altered.
sql> select object_name from t where object_id=1090;
object_name
--------------------------------------------------------------------------------------------------------------------------------
sys_lob0000001087c00012$$
execution plan
----------------------------------------------------------
plan hash value: 4043158466
--------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | table access by index rowid| t | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | index range scan | ind_id | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access(object_id=1090)
note
-----
- dynamic sampling used for this statement (level=2)
此时我们发现同样的测试在11.2.0.1上面则优化器使用了outline记录的hint 全表扫描，小鱼又找了几个10.2.0.4的库都是上述的情况，而这个测试更加给我们一个清晰的认识，就是一般而言我们不要在生产环境中大量使用存储提纲，即使设置了优化器也可能稀里糊涂的不去用outline的执行计划。
还有一种手动创建存储提纲的办法就是利用dbms_outln.create_outline 来创建存储提纲，dbms_outln包还有很多关于outline的procedure和function，有兴趣的同学可以倒腾下
sql> desc dbms_outln;
procedure create_outline
argument name type in/out default?
------------------------------ ----------------------- ------ --------
hash_value number in
child_number number in
category varchar2 in default
sql> drop table t;
table dropped.
sql> create table t as select * from dba_objects;
table created.
sql> select object_name,object_type from t where object_id=1099;
object_name
--------------------------------------------------------------------------------
object_type
-------------------
logmnr_session_pk
index
sql> select hash_value from v$sql where sql_text like 'select object_name,object_type from t where object_id=1099%';
hash_value
----------
300252238
sql> declare
2 begin
3 dbms_outln.create_outline(
4 hash_value=>300252238,
5 child_number=>0);
6 end;
7 /
declare
*
error at line 1:
ora-16953: type of sql statement not supported.
ora-06512: at sys.outln_pkg, line 324
ora-06512: at sys.outln_pkg, line 368
ora-06512: at line 3
这里又报出错误了，查了半天资料没有发现一个合理的说法，现在测试的版本是11.2.0.1,而在别的版本中dbms_outln.create_outline是可以创建cache cursor的stored outline的，小鱼个人推断这个又可能是个bug，测试这个运气确实有点背
上面介绍了手动收集，下面简单来讲下自动收集，自动收集outline需要一个很重要的参数就是create_stored_outlines
sql> create table t_auto01 as select * from dba_objects;
table created.
sql> alter session set create_stored_outlines=true;
session altered.
sql> select object_name from t_auto01 where object_id=1000;
object_name
--------------------------------------------------------------------------------------------------------------------------------
expact$
sql> select object_name from t_auto01 where object_id=1001;
object_name
--------------------------------------------------------------------------------------------------------------------------------
noexp$
sql> alter session set create_stored_outlines=false;
session altered.
sql> create index ind_auto_id on t_auto01(object_id);
index created.
sql> select name,category,used,sql_text from user_outlines;
name category used
------------------------------ ------------------------------ ------
sql_text
--------------------------------------------------------------------------------
sys_outline_14061917391589506 default unused
select /* opt_dyn_samp */ /*+ all_rows ignore_where_clause no_parallel(samplesub
sys_outline_14061917391589305 default unused
select object_name from t_auto01 where object_id=1000
sys_outline_14061917393895607 default unused
select object_name from t_auto01 where object_id=1001
这里设置create_stored_outlines=true后，默认这个session就开始收集sql语句并创建对应的outlines
sql> set autotrace on exp
sql> select object_name from t_auto01 where object_id=1000;
object_name
--------------------------------------------------------------------------------------------------------------------------------
expact$
execution plan
----------------------------------------------------------
plan hash value: 2314754062
-------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
-------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | table access by index rowid| t_auto01 | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | index range scan | ind_auto_id | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access(object_id=1000)
note
-----
- dynamic sampling used for this statement (level=2)
sql> alter session set use_stored_outlines=true;
session altered.
sql> select object_name from t_auto01 where object_id=1000;
object_name
--------------------------------------------------------------------------------------------------------------------------------
expact$
execution plan
----------------------------------------------------------
plan hash value: 795571617
------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
------------------------------------------------------------------------------
| 0 | select statement | | 895 | 70705 | 300 (1)| 00:00:04 |
|* 1 | table access full| t_auto01 | 895 | 70705 | 300 (1)| 00:00:04 |
------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(object_id=1000)
note
-----
- outline sys_outline_14061917391589305 used for this statement
sql> select object_name from t_auto01 where object_id=1001;
object_name
--------------------------------------------------------------------------------------------------------------------------------
noexp$
execution plan
----------------------------------------------------------
plan hash value: 795571617
------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
------------------------------------------------------------------------------
| 0 | select statement | | 895 | 70705 | 300 (1)| 00:00:04 |
|* 1 | table access full| t_auto01 | 895 | 70705 | 300 (1)| 00:00:04 |
------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(object_id=1001)
note
-----
- outline sys_outline_14061917393895607 used for this statement
sql> select object_name from t_auto01 where object_id=1002;
object_name
--------------------------------------------------------------------------------------------------------------------------------
exppkgobj$
execution plan
----------------------------------------------------------
plan hash value: 2314754062
-------------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
-------------------------------------------------------------------------------------------
| 0 | select statement | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | table access by index rowid| t_auto01 | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | index range scan | ind_auto_id | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access(object_id=1002)
note
-----
- dynamic sampling used for this statement (level=2)
这里最后一个sql语句由于sql_text不相同，所以没有配对的outline hint可供使用。
而如果是针对一类sql语句，我们不太可能每个去创建outline，可以针对该sql改成bind value然后直接对bind后的sql创建outline
sql> variable v_id number;
sql> exec :v_id:=10000;
pl/sql procedure successfully completed.
sql> create outline outline02 on select object_name,object_type from t where object_id=:v_id;
outline created.
sql> select name,category,used,sql_text from user_outlines where name='outline02';
name category used
------------------------------ ------------------------------ ------
sql_text
--------------------------------------------------------------------------------
outline02 default unused
select object_name,object_type from t where object_id=:v_id
下面创建了object_id上的索引
sql> create index ind_id on t(object_id);
index created.
sql> alter session set use_stored_outlines=false;
session altered.
sql> select object_name,object_type from t where object_id=:v_id;
object_name
--------------------------------------------------------------------------------------------------------------------------------
object_type
-------------------
ku$_eqntable_bytes_alloc_view
view
execution plan
----------------------------------------------------------
plan hash value: 4043158466
--------------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------------------
| 0 | select statement | | 855 | 76950 | 6 (0)| 00:00:01 |
| 1 | table access by index rowid| t | 855 | 76950 | 6 (0)| 00:00:01 |
|* 2 | index range scan | ind_id | 342 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access(object_id=to_number(:v_id))
note
-----
- dynamic sampling used for this statement (level=2)
当设置use_stored_outlines=true后，优化器采取的outline中的执行计划。
sql> alter session set use_stored_outlines=true;
session altered.
sql> select object_name,object_type from t where object_id=:v_id;
object_name
--------------------------------------------------------------------------------------------------------------------------------
object_type
-------------------
ku$_eqntable_bytes_alloc_view
view
execution plan
----------------------------------------------------------
plan hash value: 1601196873
--------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
--------------------------------------------------------------------------
| 0 | select statement | | 895 | 80550 | 300 (1)| 00:00:04 |
|* 1 | table access full| t | 895 | 80550 | 300 (1)| 00:00:04 |
--------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(object_id=to_number(:v_id))
note
-----
- outline outline02 used for this statement
原文地址：oracle的存储提纲简介, 感谢原作者分享。