题目部分
在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使用演示：
代码语言：javascript复制SYS@test> create user lhr identified by lhr;
User created.
SYS@test> grant dba to lhr;
Grant succeeded.
SYS@test> grant create any outline,alter any outline,DROP ANY OUTLINE to lhr;
Grant succeeded.
SYS@test> grant all on OL$HINTS to lhr;
Grant succeeded.
SYS@test> conn lhr/lhr
Connected.
LHR@test> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
LHR@test> create table TB_LHR_20160518 as select * from dba_tables;
Table created.
LHR@test> create index idx_TB_LHR_20160518 on TB_LHR_20160518(TABLE_NAME);
Index created.
LHR@test> SET AUTOTRACE ON;
LHR@test> select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2186742855
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518 | 1 | 34 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='TB_LHR_20160518')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
72 consistent gets
8 physical reads
0 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
LHR@test> select /* full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1750418716
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 31 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_LHR_20160518 | 1 | 34 | 31 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"='TB_LHR_20160518')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
170 consistent gets
0 physical reads
0 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
LHR@test> set autotrace off;
LHR@test> create or replace outline TB_LHR_20160518_1 on select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
Outline created.
LHR@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';
Outline created.
LHR@test> select name,USED,sql_text from dba_outlines where name like '%TB_LHR_20160518%';
NAME USED SQL_TEXT
------------------------------ ------ --------------------------------------------------------------------------------
TB_LHR_20160518_1 UNUSED select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'
TB_LHR_20160518_2 UNUSED select /* full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='T
LHR@test> select name,HINT from dba_outline_hints where JOIN_POS=1 and name like '%TB_LHR_20160518%';
NAME HINT
------------------------------ --------------------------------------------------------------------------------
TB_LHR_20160518_1 INDEX_RS_ASC(@"SEL$1" "TB_LHR_20160518"@"SEL$1" ("TB_LHR_20160518"."TABLE_NAME")
TB_LHR_20160518_2 FULL(@"SEL$1" "TB_LHR_20160518"@"SEL$1")
LHR@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');
2 rows updated.
LHR@test> commit;
Commit complete.
LHR@test> select name,USED,sql_text from dba_outlines where name like '%TB_LHR_20160518%';
NAME USED SQL_TEXT
------------------------------ ------ --------------------------------------------------------------------------------
TB_LHR_20160518_1 UNUSED select /* full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='T
TB_LHR_20160518_2 UNUSED select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'
LHR@test> SELECT NAME,HINT FROM DBA_OUTLINE_HINTS WHERE JOIN_POS=1 AND NAME LIKE '%TB_LHR_20160518%';
NAME HINT
------------------------------ --------------------------------------------------------------------------------
TB_LHR_20160518_1 INDEX_RS_ASC(@"SEL$1" "TB_LHR_20160518"@"SEL$1" ("TB_LHR_20160518"."TABLE_NAME")
TB_LHR_20160518_2 FULL(@"SEL$1" "TB_LHR_20160518"@"SEL$1")
LHR@test> SET AUTOTRACE ON;
LHR@test> alter system set use_stored_outlines=true;
System altered.
LHR@test> select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1750418716
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 89 | 3026 | 31 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_LHR_20160518 | 89 | 3026 | 31 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME"='TB_LHR_20160518')
Note
-----
- outline "TB_LHR_20160518_2" used for this statement
Statistics
----------------------------------------------------------
34 recursive calls
147 db block gets
125 consistent gets
0 physical reads
624 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
LHR@test> select /* full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2186742855
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 89 | 3026 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_LHR_20160518 | 89 | 3026 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_LHR_20160518 | 36 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='TB_LHR_20160518')
Note
-----
- outline "TB_LHR_20160518_1" used for this statement
Statistics
----------------------------------------------------------
34 recursive calls
147 db block gets
24 consistent gets
0 physical reads
584 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
