您当前的位置: 首页 >  面试

【DB笔试面试604】在Oracle中,存储概要(Stored Outline)的作用是什么?

发布时间:2019-07-29 01:00:00 ,浏览量:0

640?wx_fmt=gif

♣题目 部分

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

640?wx_fmt=gif ---------------优质麦课------------

640?wx_fmt=png

 详细内容可以添加麦老师微信或QQ私聊。

640?wx_fmt=gif

About Me:小麦苗

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 作者博客地址:http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

● QQ:646634621  QQ群:618766405

● 提供OCP、OCM和高可用部分最实用的技能培训

● 题目解答若有不当之处,还望各位朋友批评指正,共同进步

640?wx_fmt=gifDBA宝典

长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,学习最实用的数据库技术。

640?wx_fmt=gif

640?wx_fmt=gif

640?wx_fmt=png 喜欢就点击“好看”吧

关注
打赏
1688896170
查看更多评论

暂无认证

  • 0浏览

    0关注

    107766博文

    0收益

  • 0浏览

    0点赞

    0打赏

    0留言

私信
关注
热门博文
立即登录/注册

微信扫码登录

0.0561s