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

小麦苗DBA宝典

发布于 2019-09-29 16:04:26

9700

发布于 2019-09-29 16:04:26

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

题目部分

在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