【转载声明】本文为转载文章，原作者「李华荣」，版权归原作者所有。　原文链接：https://mp.weixin.qq.com/s/BkotYp2mQ9kLEhET5g1bNg

公众号：杨建荣的学习笔记 · 作者：李华荣 · 发布：2016-06-09 23:46:33 · 原文链接

1  outline

1.1  outline基础知识

在实际项目中，通常在开发环境下一些SQL 执行没有任何问题，而到了生产环境或生产环境的数据量发生较大的变量时，其SQL 的执行效率会异常的慢。此时如果更改SQL ，则可能需要重新修改源程序以及重新编译程序。如果觉得修改源程序的成本比较大，则可以使用OUTLINE在不改变原应用程序的情况下更改特定SQL 的执行计划。

OUTLINE的原理是将调好的SQL 的执行计划(一系列的HINT)存贮起来，然后该执行计划所对应的SQL 用目前系统那个效率低下的SQL 来替代之。从而使得系统每次执行该SQL 时，都会使用已存贮的执行计划来执行。因此可以在不改变已有系统SQL 的情况下达到改变其执行计划的目的。

OUTLINE方式也是通过存贮HINT的方式来达到执行计划的稳定与改变。

当发现低效SQL之后，可以使用hint优化他，对于SQL代码可以修改的情况，直接修改SQL代码加上hint即可，但是对于SQL代码不可修改的情况，Oracle提供了outLine功能来为SQL修改hint，以致执行计划变更！

OutLine机制：

Outline保存了SQL的hint在outline的表中。当执行SQL时，Oracle会使用outline中的hint来为SQL生成执行计划。

?使用  OutLine的步骤：

（1）生成新SQL和老SQL的2个Outline

（2）交换两个SQL的提示信息

（3） ON LOGON触发器设定session的CATEGORY（自定义类别）

SQL命令行为：SQL> alter session setuse_stored_outlines=special;

1.2  ouline使用演示

测试过程如下：

SYS@test> create user lhr identifiedby lhr;

User created.

SYS@test> grant dba to lhr;

Grant succeeded.

SYS@test> grant create anyoutline,alter any outline,DROP ANY OUTLINE to lhr;

Grant succeeded.

SYS@test> grant all on OL$HINTS tolhr;

Grant succeeded.

SYS@test> conn lhr/lhr

Connected.

LHR@test> create tableTB_LHR_20160518 as select * from dba_tables;

Table created.

LHR@test> create indexidx_TB_LHR_20160518 on TB_LHR_20160518(TABLE_NAME);

Index created.

LHR@test> SET AUTOTRACE ON;

LHR@test> select owner fromTB_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 byoperation 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 toclient

       508  bytes received via SQL*Netfrom 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 byoperation 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 toclient

       508  bytes received via SQL*Netfrom 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 outlineTB_LHR_20160518_1 on select owner from TB_LHR_20160518 wheretable_name=’TB_LHR_20160518′;

Outline created.

LHR@test> create or replace outlineTB_LHR_20160518_2 on select /*+full(TB_LHR_20160518)*/ owner fromTB_LHR_20160518 where table_name=’TB_LHR_20160518′;

Outline created.

LHR@test> select name,USED,sql_textfrom dba_outlines where name like ‘%TB_LHR_20160518%’;

NAME                           USED   SQL_TEXT

—————————— ——

TB_LHR_20160518_1              UNUSED select owner from TB_LHR_20160518where 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 fromdba_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$ SETOL_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_textfrom 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 fromTB_LHR_20160518 where table_name=’TB_LHR_20160518′

LHR@test> select name,HINT fromdba_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 setuse_stored_outlines=true;

System altered.

LHR@test> select owner fromTB_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 byoperation 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 toclient

       508  bytes received via SQL*Netfrom 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 wheretable_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 byoperation 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 toclient

       508  bytes received via SQL*Netfrom client

          1 SQL*Net roundtrips to/from client

          2 sorts (memory)

          0 sorts (disk)

          0 rows processed