♣题目 部分
在Oracle中，存储概要（Stored Outline）的作用是什么？
♣答案部分
OUTLINE的原理是将调好的执行计划（一系列的Hint）保存起来，然后使用该效率高的执行计划替换之前效率低下的执行计划，从而使得当系统每次执行该SQL时，都会使用已存储的执行计划来执行。所以，可以在不改变已有系统SQL的情况下达到改变其执行计划的目的。OUTLINE方式是通过存储Hint的方式来达到执行计划的稳定与改变。当发现低效SQL之后，可以使用Hint优化它，对于SQL代码可以修改的情况，直接修改SQL代码加上Hint即可。
Oracle在Outline的表中保存了SQL的Hint，当执行SQL时，Oracle会使用Outline中的Hint来为SQL生成执行计划。
Ø使用OutLine的步骤：
（1）生成新SQL和老SQL的2个Outline
（2）交换两个SQL的提示信息
（3）ON LOGON触发器设定session的CATEGORY（自定义类别）
SQL命令行为：SQL>alter session set use_stored_outlines=special;
Ouline使用演示：
1SYS@test> create user lhr identified by lhr; 2 3User created. 4 5SYS@test> grant dba to lhr; 6 7Grant succeeded. 8 9SYS@test> grant create any outline,alter any outline,DROP ANY OUTLINE to lhr; 10 11Grant succeeded. 12 13SYS@test> grant all on OL$HINTS to lhr; 14 15Grant succeeded. 16 17SYS@test> conn lhr/lhr 18Connected. 19LHR@test> select * from v$version; 20 21BANNER 22-------------------------------------------------------------------------------- 23Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 24PL/SQL Release 11.2.0.4.0 - Production 25CORE 11.2.0.4.0 Production 26TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production 27NLSRTL Version 11.2.0.4.0 - Production 28 29LHR@test> create table TB_LHR_20160518 as select * from dba_tables; 30 31Table created. 32 33LHR@test> create index idx_TB_LHR_20160518 on TB_LHR_20160518(TABLE_NAME); 34 35Index created. 36 37LHR@test> SET AUTOTRACE ON; 38LHR@test> select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'; 39 40no rows selected 41 42 43Execution Plan 44---------------------------------------------------------- 45Plan hash value: 2186742855 46 47--------------------------------------------------------------------------------------------------- 48| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 49--------------------------------------------------------------------------------------------------- 50| 0 | SELECT STATEMENT | | 1 | 34 | 1 (0)| 00:00:01 | 51| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518 | 1 | 34 | 1 (0)| 00:00:01 | 52|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518 | 1 | | 1 (0)| 00:00:01 | 53--------------------------------------------------------------------------------------------------- 54 55Predicate Information (identified by operation id): 56--------------------------------------------------- 57 58 2 - access("TABLE_NAME"='TB_LHR_20160518') 59 60Note 61----- 62 - dynamic sampling used for this statement (level=2) 63 64 65Statistics 66---------------------------------------------------------- 67 11 recursive calls 68 0 db block gets 69 72 consistent gets 70 8 physical reads 71 0 redo size 72 333 bytes sent via SQL*Net to client 73 508 bytes received via SQL*Net from client 74 1 SQL*Net roundtrips to/from client 75 0 sorts (memory) 76 0 sorts (disk) 77 0 rows processed 78 79LHR@test> select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'; 80 81no rows selected 82 83 84Execution Plan 85---------------------------------------------------------- 86Plan hash value: 1750418716 87 88------------------------------------------------------------------------------------- 89| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 90------------------------------------------------------------------------------------- 91| 0 | SELECT STATEMENT | | 1 | 34 | 31 (0)| 00:00:01 | 92|* 1 | TABLE ACCESS FULL| TB_LHR_20160518 | 1 | 34 | 31 (0)| 00:00:01 | 93------------------------------------------------------------------------------------- 94 95Predicate Information (identified by operation id): 96--------------------------------------------------- 97 98 1 - filter("TABLE_NAME"='TB_LHR_20160518') 99100Note101-----102 - dynamic sampling used for this statement (level=2)103104105Statistics106----------------------------------------------------------107 7 recursive calls108 0 db block gets109 170 consistent gets110 0 physical reads111 0 redo size112 333 bytes sent via SQL*Net to client113 508 bytes received via SQL*Net from client114 1 SQL*Net roundtrips to/from client115 0 sorts (memory)116 0 sorts (disk)117 0 rows processed118119LHR@test> set autotrace off;120LHR@test> create or replace outline TB_LHR_20160518_1 on select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';121122Outline created.123124LHR@test> create or replace outline TB_LHR_20160518_2 on select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';125126Outline created.127128LHR@test> select name,USED,sql_text from dba_outlines where name like '%TB_LHR_20160518%';129130NAME USED SQL_TEXT131------------------------------ ------ --------------------------------------------------------------------------------132TB_LHR_20160518_1 UNUSED select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'133TB_LHR_20160518_2 UNUSED select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='T134135LHR@test> select name,HINT from dba_outline_hints where JOIN_POS=1 and name like '%TB_LHR_20160518%';136137NAME HINT138------------------------------ --------------------------------------------------------------------------------139TB_LHR_20160518_1 INDEX_RS_ASC(@"SEL$1" "TB_LHR_20160518"@"SEL$1" ("TB_LHR_20160518"."TABLE_NAME")140TB_LHR_20160518_2 FULL(@"SEL$1" "TB_LHR_20160518"@"SEL$1")141142LHR@test> UPDATE OUTLN.OL$ SET OL_NAME=DECODE(OL_NAME,'TB_LHR_20160518_2','TB_LHR_20160518_1','TB_LHR_20160518_1','TB_LHR_20160518_2') WHERE OL_NAME IN ('TB_LHR_20160518_1','TB_LHR_20160518_2');1431442 rows updated.145146LHR@test> commit;147148Commit complete.149150LHR@test> select name,USED,sql_text from dba_outlines where name like '%TB_LHR_20160518%';151152NAME USED SQL_TEXT153------------------------------ ------ --------------------------------------------------------------------------------154TB_LHR_20160518_1 UNUSED select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='T155TB_LHR_20160518_2 UNUSED select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'156157LHR@test> SELECT NAME,HINT FROM DBA_OUTLINE_HINTS WHERE JOIN_POS=1 AND NAME LIKE '%TB_LHR_20160518%';158159NAME HINT160------------------------------ --------------------------------------------------------------------------------161TB_LHR_20160518_1 INDEX_RS_ASC(@"SEL$1" "TB_LHR_20160518"@"SEL$1" ("TB_LHR_20160518"."TABLE_NAME")162TB_LHR_20160518_2 FULL(@"SEL$1" "TB_LHR_20160518"@"SEL$1")163164LHR@test> SET AUTOTRACE ON;165LHR@test> alter system set use_stored_outlines=true;166167System altered.168169LHR@test> select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';170171no rows selected172173174Execution Plan175----------------------------------------------------------176Plan hash value: 1750418716177178-------------------------------------------------------------------------------------179| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |180-------------------------------------------------------------------------------------181| 0 | SELECT STATEMENT | | 89 | 3026 | 31 (0)| 00:00:01 |182|* 1 | TABLE ACCESS FULL| TB_LHR_20160518 | 89 | 3026 | 31 (0)| 00:00:01 |183-------------------------------------------------------------------------------------184185Predicate Information (identified by operation id):186---------------------------------------------------187188 1 - filter("TABLE_NAME"='TB_LHR_20160518')189190Note191-----192 - outline "TB_LHR_20160518_2" used for this statement193194195Statistics196----------------------------------------------------------197 34 recursive calls198 147 db block gets199 125 consistent gets200 0 physical reads201 624 redo size202 333 bytes sent via SQL*Net to client203 508 bytes received via SQL*Net from client204 1 SQL*Net roundtrips to/from client205 2 sorts (memory)206 0 sorts (disk)207 0 rows processed208209LHR@test> select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';210211no rows selected212213214Execution Plan215----------------------------------------------------------216Plan hash value: 2186742855217218---------------------------------------------------------------------------------------------------219| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |220---------------------------------------------------------------------------------------------------221| 0 | SELECT STATEMENT | | 89 | 3026 | 6 (0)| 00:00:01 |222| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518 | 89 | 3026 | 6 (0)| 00:00:01 |223|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518 | 36 | | 1 (0)| 00:00:01 |224---------------------------------------------------------------------------------------------------225226Predicate Information (identified by operation id):227---------------------------------------------------228229 2 - access("TABLE_NAME"='TB_LHR_20160518')230231Note232-----233 - outline "TB_LHR_20160518_1" used for this statement234235236Statistics237----------------------------------------------------------238 34 recursive calls239 147 db block gets240 24 consistent gets241 0 physical reads242 584 redo size243 333 bytes sent via SQL*Net to client244 508 bytes received via SQL*Net from client245 1 SQL*Net roundtrips to/from client246 2 sorts (memory)247 0 sorts (disk)248 0 rows processedcreate user lhr identified by lhr;
2
3User created.
4
5SYS@test> grant dba to lhr;
6
7Grant succeeded.
8
9SYS@test> grant create any outline,alter any outline,DROP ANY OUTLINE to lhr;
10
11Grant succeeded.
12
13SYS@test> grant all on OL$HINTS to lhr;
14
15Grant succeeded.
16
17SYS@test> conn lhr/lhr
18Connected.
19LHR@test> select * from v$version;
20
21BANNER
22--------------------------------------------------------------------------------
23Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
24PL/SQL Release 11.2.0.4.0 - Production
25CORE 11.2.0.4.0 Production
26TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
27NLSRTL Version 11.2.0.4.0 - Production
28
29LHR@test> create table TB_LHR_20160518 as select * from dba_tables;
30
31Table created.
32
33LHR@test> create index idx_TB_LHR_20160518 on TB_LHR_20160518(TABLE_NAME);
34
35Index created.
36
37LHR@test> SET AUTOTRACE ON;
38LHR@test> select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
39
40no rows selected
41
42
43Execution Plan
44----------------------------------------------------------
45Plan hash value: 2186742855
46
47---------------------------------------------------------------------------------------------------
48| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
49---------------------------------------------------------------------------------------------------
50| 0 | SELECT STATEMENT | | 1 | 34 | 1 (0)| 00:00:01 |
51| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518 | 1 | 34 | 1 (0)| 00:00:01 |
52|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518 | 1 | | 1 (0)| 00:00:01 |
53---------------------------------------------------------------------------------------------------
54
55Predicate Information (identified by operation id):
56---------------------------------------------------
57
58 2 - access("TABLE_NAME"='TB_LHR_20160518')
59
60Note
61-----
62 - dynamic sampling used for this statement (level=2)
63
64
65Statistics
66----------------------------------------------------------
67 11 recursive calls
68 0 db block gets
69 72 consistent gets
70 8 physical reads
71 0 redo size
72 333 bytes sent via SQL*Net to client
73 508 bytes received via SQL*Net from client
74 1 SQL*Net roundtrips to/from client
75 0 sorts (memory)
76 0 sorts (disk)
77 0 rows processed
78
79LHR@test> select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
80
81no rows selected
82
83
84Execution Plan
85----------------------------------------------------------
86Plan hash value: 1750418716
87
88-------------------------------------------------------------------------------------
89| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
90-------------------------------------------------------------------------------------
91| 0 | SELECT STATEMENT | | 1 | 34 | 31 (0)| 00:00:01 |
92|* 1 | TABLE ACCESS FULL| TB_LHR_20160518 | 1 | 34 | 31 (0)| 00:00:01 |
93-------------------------------------------------------------------------------------
94
95Predicate Information (identified by operation id):
96---------------------------------------------------
97
98 1 - filter("TABLE_NAME"='TB_LHR_20160518')
99
100Note
101-----
102 - dynamic sampling used for this statement (level=2)
103
104
105Statistics
106----------------------------------------------------------
107 7 recursive calls
108 0 db block gets
109 170 consistent gets
110 0 physical reads
111 0 redo size
112 333 bytes sent via SQL*Net to client
113 508 bytes received via SQL*Net from client
114 1 SQL*Net roundtrips to/from client
115 0 sorts (memory)
116 0 sorts (disk)
117 0 rows processed
118
119LHR@test> set autotrace off;
120LHR@test> create or replace outline TB_LHR_20160518_1 on select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
121
122Outline created.
123
124LHR@test> create or replace outline TB_LHR_20160518_2 on select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
125
126Outline created.
127
128LHR@test> select name,USED,sql_text from dba_outlines where name like '%TB_LHR_20160518%';
129
130NAME USED SQL_TEXT
131------------------------------ ------ --------------------------------------------------------------------------------
132TB_LHR_20160518_1 UNUSED select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'
133TB_LHR_20160518_2 UNUSED select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='T
134
135LHR@test> select name,HINT from dba_outline_hints where JOIN_POS=1 and name like '%TB_LHR_20160518%';
136
137NAME HINT
138------------------------------ --------------------------------------------------------------------------------
139TB_LHR_20160518_1 INDEX_RS_ASC(@"SEL$1" "TB_LHR_20160518"@"SEL$1" ("TB_LHR_20160518"."TABLE_NAME")
140TB_LHR_20160518_2 FULL(@"SEL$1" "TB_LHR_20160518"@"SEL$1")
141
142LHR@test> UPDATE OUTLN.OL$ SET OL_NAME=DECODE(OL_NAME,'TB_LHR_20160518_2','TB_LHR_20160518_1','TB_LHR_20160518_1','TB_LHR_20160518_2') WHERE OL_NAME IN ('TB_LHR_20160518_1','TB_LHR_20160518_2');
143
1442 rows updated.
145
146LHR@test> commit;
147
148Commit complete.
149
150LHR@test> select name,USED,sql_text from dba_outlines where name like '%TB_LHR_20160518%';
151
152NAME USED SQL_TEXT
153------------------------------ ------ --------------------------------------------------------------------------------
154TB_LHR_20160518_1 UNUSED select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='T
155TB_LHR_20160518_2 UNUSED select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'
156
157LHR@test> SELECT NAME,HINT FROM DBA_OUTLINE_HINTS WHERE JOIN_POS=1 AND NAME LIKE '%TB_LHR_20160518%';
158
159NAME HINT
160------------------------------ --------------------------------------------------------------------------------
161TB_LHR_20160518_1 INDEX_RS_ASC(@"SEL$1" "TB_LHR_20160518"@"SEL$1" ("TB_LHR_20160518"."TABLE_NAME")
162TB_LHR_20160518_2 FULL(@"SEL$1" "TB_LHR_20160518"@"SEL$1")
163
164LHR@test> SET AUTOTRACE ON;
165LHR@test> alter system set use_stored_outlines=true;
166
167System altered.
168
169LHR@test> select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
170
171no rows selected
172
173
174Execution Plan
175----------------------------------------------------------
176Plan hash value: 1750418716
177
178-------------------------------------------------------------------------------------
179| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
180-------------------------------------------------------------------------------------
181| 0 | SELECT STATEMENT | | 89 | 3026 | 31 (0)| 00:00:01 |
182|* 1 | TABLE ACCESS FULL| TB_LHR_20160518 | 89 | 3026 | 31 (0)| 00:00:01 |
183-------------------------------------------------------------------------------------
184
185Predicate Information (identified by operation id):
186---------------------------------------------------
187
188 1 - filter("TABLE_NAME"='TB_LHR_20160518')
189
190Note
191-----
192 - outline "TB_LHR_20160518_2" used for this statement
193
194
195Statistics
196----------------------------------------------------------
197 34 recursive calls
198 147 db block gets
199 125 consistent gets
200 0 physical reads
201 624 redo size
202 333 bytes sent via SQL*Net to client
203 508 bytes received via SQL*Net from client
204 1 SQL*Net roundtrips to/from client
205 2 sorts (memory)
206 0 sorts (disk)
207 0 rows processed
208
209LHR@test> select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
210
211no rows selected
212
213
214Execution Plan
215----------------------------------------------------------
216Plan hash value: 2186742855
217
218---------------------------------------------------------------------------------------------------
219| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
220---------------------------------------------------------------------------------------------------
221| 0 | SELECT STATEMENT | | 89 | 3026 | 6 (0)| 00:00:01 |
222| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518 | 89 | 3026 | 6 (0)| 00:00:01 |
223|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518 | 36 | | 1 (0)| 00:00:01 |
224---------------------------------------------------------------------------------------------------
225
226Predicate Information (identified by operation id):
227---------------------------------------------------
228
229 2 - access("TABLE_NAME"='TB_LHR_20160518')
230
231Note
232-----
233 - outline "TB_LHR_20160518_1" used for this statement
234
235
236Statistics
237----------------------------------------------------------
238 34 recursive calls
239 147 db block gets
240 24 consistent gets
241 0 physical reads
242 584 redo size
243 333 bytes sent via SQL*Net to client
244 508 bytes received via SQL*Net from client
245 1 SQL*Net roundtrips to/from client
246 2 sorts (memory)
247 0 sorts (disk)
248 0 rows processed
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
喜欢就点击“好看”吧