oracle的存储提纲简介-tutorial mysql-php.cn

从oracle 10g开始，oracle推出了存储提纲用来固定sql语句的执行计划，在oracle 11g后存储提纲被sql计划基线（sql plan baseline）取而代之，sql plan baseline也和存储提纲一样被用来提供稳定的执行计划，简单来讲也是固化sql语句执行计划的，而同样固化执行

从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

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

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

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;

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的存储提纲简介, 感谢原作者分享。