利用存储纲要可以固定sql执行计划,存储纲要有一系列与sql语句相关的optimizer hints构成。当存储纲要可用是,oracle会自动根据这些hint生成对应sql语句的执行计划。存储纲要可以分为私有和公有,同时我们可以对存储纲要划分不同的类别(categories),并指定那一类存储纲要发挥作用,这无疑会方便dba的管理工作。存储纲要一旦存储,就不会随后续系统配置或者统计信息的变化而变化。(oracle 11g虽然支持存储纲要,但是oracle已经不建议使用存储纲要,而是使用SPM).
公有和私有
oracle可以创建公有的存储纲要,也可以创建私有的存储纲要。具有的语法格式如下:
创建公有存储纲要,相关信息会存放在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 TABTYPE CLUSTERID
------------------------------ ------- ----------
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 statement
SESSION 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)