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
