在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; 3User created. 5SYS@test> grant dba to lhr; 7Grant succeeded. 9SYS@test> grant create any outline,alter any outline,DROP ANY OUTLINE to lhr; 11Grant succeeded. 13SYS@test> grant all on OL$HINTS to lhr; 15Grant succeeded. 17SYS@test> conn lhr/lhr 18Connected. 19LHR@test> select * from v$version; 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 29LHR@test> create table TB_LHR_20160518 as select * from dba_tables; 31Table created. 33LHR@test> create index idx_TB_LHR_20160518 on TB_LHR_20160518(TABLE_NAME); 35Index created. 37LHR@test> SET AUTOTRACE ON; 38LHR@test> select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'; 40no rows selected 43Execution Plan 44---------------------------------------------------------- 45Plan hash value: 2186742855 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--------------------------------------------------------------------------------------------------- 55Predicate Information (identified by operation id): 56--------------------------------------------------- 2 - access("TABLE_NAME"='TB_LHR_20160518') 60Note 61----- - dynamic sampling used for this statement (level=2) 65Statistics 66---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed 79LHR@test> select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'; 81no rows selected 84Execution Plan 85---------------------------------------------------------- 86Plan hash value: 1750418716 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------------------------------------------------------------------------------------- 95Predicate Information (identified by operation id): 96--------------------------------------------------- 1 - filter("TABLE_NAME"='TB_LHR_20160518') 100Note 101----- - dynamic sampling used for this statement (level=2) 105Statistics 106---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed 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'; 122Outline created. 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'; 126Outline created. 128LHR@test> select name,USED,sql_text from dba_outlines where name like '%TB_LHR_20160518%'; 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 135LHR@test> select name,HINT from dba_outline_hints where JOIN_POS=1 and name like '%TB_LHR_20160518%'; 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") 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'); rows updated. 146LHR@test> commit; 148Commit complete. 150LHR@test> select name,USED,sql_text from dba_outlines where name like '%TB_LHR_20160518%'; 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' 157LHR@test> SELECT NAME,HINT FROM DBA_OUTLINE_HINTS WHERE JOIN_POS=1 AND NAME LIKE '%TB_LHR_20160518%'; 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") 164LHR@test> SET AUTOTRACE ON; 165LHR@test> alter system set use_stored_outlines=true; 167System altered. 169LHR@test> select owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'; 171no rows selected 174Execution Plan 175---------------------------------------------------------- 176Plan hash value: 1750418716 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------------------------------------------------------------------------------------- 185Predicate Information (identified by operation id): 186--------------------------------------------------- 1 - filter("TABLE_NAME"='TB_LHR_20160518') 190Note 191----- - outline "TB_LHR_20160518_2" used for this statement 195Statistics 196---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed 209LHR@test> select /*+full(TB_LHR_20160518)*/ owner from TB_LHR_20160518 where table_name='TB_LHR_20160518'; 211no rows selected 214Execution Plan 215---------------------------------------------------------- 216Plan hash value: 2186742855 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--------------------------------------------------------------------------------------------------- 226Predicate Information (identified by operation id): 227--------------------------------------------------- 2 - access("TABLE_NAME"='TB_LHR_20160518') 231Note 232----- - outline "TB_LHR_20160518_1" used for this statement 236Statistics 237---------------------------------------------------------- recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client sorts (memory) sorts (disk) rows processed
本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。
详细内容可以添加麦老师微信或QQ私聊。
● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用
● 作者博客地址:http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
● QQ:646634621 QQ群:618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处,还望各位朋友批评指正,共同进步
DBA宝典
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,学习最实用的数据库技术。
本文分享自微信公众号 - DB宝(lhrdba)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。