ORACLE使用STORED OUTLINE固化执行计划--私有和公有

　　利用存储纲要可以固定sql执行计划，存储纲要有一系列与sql语句相关的optimizer hints构成。当存储纲要可用是，oracle会自动根据这些hint生成对应sql语句的执行计划。存储纲要可以分为私有和公有，同时我们可以对存储纲要划分不同的类别（categories），并指定那一类存储纲要发挥作用，这无疑会方便dba的管理工作。存储纲要一旦存储，就不会随后续系统配置或者统计信息的变化而变化。（oracle 11g虽然支持存储纲要，但是oracle已经不建议使用存储纲要，而是使用SPM).

公有和私有
　　oracle可以创建公有的存储纲要，也可以创建私有的存储纲要。具有的语法格式如下：
http://img.blog.csdn.net/20140103085429140?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQveWlkaWFuODE1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast
　　创建公有存储纲要，相关信息会存放在outln用中，创建私有纲要时，相关信息会存放在当前用户模式下，因此当前用户需要具有DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES的执行权限，以便创建相关对象（这不是必须的因为从10.1之后，会在system下创建临时表对象来存储私有的存储纲要）。
　　

SQL> show user
USER 为 "OUTLN"
SQL> l
1* select object_name,object_type from user_objects order by object_type
SQL> /
OBJECT_NAME       OBJECT_TYPE
------------------------------ -------------------
OL$NAME        INDEX
OL$NODE_OL_NAME        INDEX
OL$SIGNATURE       INDEX
OL$HNT_NUM       INDEX
SYS_IL0000000451C00021$$       INDEX
SYS_LOB0000000451C00021$$      LOB
ORA$GRANT_SYS_SELECT       PROCEDURE
OL$HINTS       TABLE
OL$NODES       TABLE
OL$       TABLE
已选择10行。
从这里可以看出，公共存储纲要信息主要存放在OL$,OL$HINTS,OL$NODES三张表中。

　　首先，我们来创建一个公共纲要，创建纲要需要具有CREATE ANY OUTLINE权限
　　

SQL> create public outline outline1 on select count(*) from t1 where object_id < 100;
create public outline outline1 on select count(*) from t1 where object_id < 100
*
第 1 行出现错误:
ORA-18005: 此操作需要 CREATE ANY OUTLINE 权限

SQL> create public outline outline1 on select count(*) from t1 where object_id < 100;
大纲已创建。


要是存储纲要发挥作用，需要在system或者session级别设置use_stored_outlines参数　　

SQL> set autotrace on explain
SQL> select count(*) from t1 where object_id < 100;
COUNT(*)
----------
98

执行计划
----------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |  |1 |    13 |   295   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |  |1 |    13 |       |  |
|*  2 |   TABLE ACCESS FULL| T1   |    12 |   156 |   295   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<100)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> create index i1 on t1(object_id);
索引已创建。
SQL> select count(*) from t1 where object_id < 100;
COUNT(*)
----------
98

执行计划
----------------------------------------------------------
Plan hash value: 3900446664
--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   | |     1 |    13 |      |  |
|*  2 |   INDEX RANGE SCAN| I1 |    98 |  1274 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100)
Note
-----
- dynamic sampling used for this statement (level=2)
SQL> alter session set use_stored_outlines=true;
会话已更改。
SQL> select count(*) from t1 where object_id < 100;
COUNT(*)
----------
98

执行计划
----------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |  |1 |    13 |   295   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |  |1 |    13 |       |  |
|*  2 |   TABLE ACCESS FULL| T1   |  4411 | 57343 |   295   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<100)
Note
-----
-- outline "OUTLINE1" used for this statement在system级别设置use_stored_outlines为true后，　　

SQL> show user
USER 为 "EASY2"
SQL> select count(*) from t1 where object_id < 100;
COUNT(*)
----------
98

执行计划
----------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |  |1 |    13 |   295   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |  |1 |    13 |       |  |
|*  2 |   TABLE ACCESS FULL| T1   |  4411 | 57343 |   295   (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<100)
Note
-----
- outline "OUTLINE1" used for this statement


下面创建私有的存储纲要，创建私有纲要前可以执行如下语句来在当前模式下创建相关对象　　

SQL> exec DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES;
PL/SQL 过程已成功完成。
SQL> select * from tab;
TNAME       TABTYPECLUSTERID
------------------------------ ------- ----------
OL$       TABLE
OL$HINTS       TABLE
OL$NODES       TABLE
T1       TABLE
SQL> select count(*) from OL$;
COUNT(*)
----------
0
SQL> create private outline outline2 on select count(*) from t1 where object_id < 100;
大纲已创建。
SQL> select count(*) from OL$;
COUNT(*)
----------
0
SQL> select count(*) from SYSTEM.OL$;
COUNT(*)
----------
1
SESSION 1　　

SQL> select count(*) from t1 where object_id < 100;
COUNT(*)
----------
98

执行计划
----------------------------------------------------------
Plan hash value: 3900446664
--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   | |     1 |    13 |      |  |
|*  2 |   INDEX RANGE SCAN| I1 |  4411 | 57343 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100)
Note
-----
- outline "OUTLINE2" used for this statementSESSION 2：　　

SQL> alter session set use_private_outlines=TRUE;
会话已更改。
SQL> select count(*) from t1 where object_id < 100;
COUNT(*)
----------
98

执行计划
----------------------------------------------------------
Plan hash value: 3900446664
--------------------------------------------------------------------------
| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  | |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   | |     1 |    13 |      |  |
|*  2 |   INDEX RANGE SCAN| I1 |    98 |  1274 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100)
Note
-----
- dynamic sampling used for this statement (level=2)
　　


总结：oracle的存储纲要可以分为公有和私有，公有纲要的相关信息存放在OUTLN用户中，私有公有的相关信息存放在SYSTEM下的临时表中；由于私有纲要存放在临死表中，因此只能再当前session中使用，使用私有纲要需要设置USE_PRIVATE_OUTLINES（system或者session级别，主要这不是初始化参数），同样使用公有纲要需要设置USE_STORED_OUTLINES；