【DB笔试面试604】在Oracle中，存储概要（Stored Outline）的作用是什么？

• 2019 年 10 月 10 日
• 筆記

题目部分

在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使用演示：

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程序员面试笔试宝典》，作者：李华荣。