Oracle高级优化——侵入存储提纲（用PRIVATEOUTLINE）
如果某语句当前的执行计划并不令人满意，而且这条语句又不能加hint（因为在应用中不能编辑）。那么可以用这种方法，使执行计划稳定在一个与当前执行计划不同的执行计划上。
SYS@ prod> conn hr/hr
Connected.
为所要调整的语句用当前的执行计划（并不是想要的）创建一个outline。
HR@ prod> create outline pub_otln for category c1 on
2 select last_name from employees1 where employee_id = 100 ;
Outline created.
创建一个与上面相同的私有OUTLINE。
HR@ prod> create private outline otln1 from pub_otln ;
Outline created.
通过向原来的语句中加入hints得到想要的执行计划，如下，使执行计划使用复合索引test_idx2。
HR@ prod> set autotrace on
HR@ prod> select /*+ index(employees1 test_idx2) */ last_name from employees1 where employee_id = 100 ;
LAST_NAME
-------------------------
King
Execution Plan
----------------------------------------------------------
Plan hash value: 2093088777
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX2 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
HR@ prod> set autotrace off
为这条功能与原语句相同，但是加了hints的语句创建私有outline。
HR@ prod> create private outline otln2 on
2 select /*+ index(employees1 test_idx2) */ last_name from employees1 where employee_id = 100 ;
Outline created.
修改ol$hints表（临时表），使两个outline的ol_name字段对调。
HR@ prod> col ol_name for a30
HR@ prod> col hint_text for a100
HR@ prod> set linesize 150
HR@ prod> select ol_name , hint_text from ol$hints ;
OL_NAME HINT_TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
OTLN1 OUTLINE_LEAF(@"SEL$1")
OTLN1 IGNORE_OPTIM_EMBEDDED_HINTS
OTLN1 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
OTLN1 DB_VERSION('11.2.0.1')
OTLN1 ALL_ROWS
OTLN1 INDEX_RS_ASC(@"SEL$1" "EMPLOYEES1"@"SEL$1" ("EMPLOYEES1"."EMPLOYEE_ID"))
OTLN2 INDEX_RS_ASC(@"SEL$1" "EMPLOYEES1"@"SEL$1" ("EMPLOYEES1"."EMPLOYEE_ID" "EMPLOYEES1"."FIRST_NAME"))
OTLN2 OUTLINE_LEAF(@"SEL$1")
OTLN2 ALL_ROWS
OTLN2 DB_VERSION('11.2.0.1')
OTLN2 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
OTLN2 IGNORE_OPTIM_EMBEDDED_HINTS
12 rows selected.
HR@ prod> update ol$hints set ol_name = 'T' where ol_name = 'OTLN1' ;
6 rows updated.
HR@ prod> update ol$hints set ol_name = 'OTLN1' where ol_name = 'OTLN2' ;
6 rows updated.
HR@ prod> update ol$hints set ol_name = 'OTLN2' where ol_name = 'T' ;
6 rows updated.
结果如下：
HR@ prod> select ol_name , hint_text from ol$hints ;
OL_NAME HINT_TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
OTLN2 OUTLINE_LEAF(@"SEL$1")
OTLN2 IGNORE_OPTIM_EMBEDDED_HINTS
OTLN2 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
OTLN2 DB_VERSION('11.2.0.1')
OTLN2 ALL_ROWS
OTLN2 INDEX_RS_ASC(@"SEL$1" "EMPLOYEES1"@"SEL$1" ("EMPLOYEES1"."EMPLOYEE_ID"))
OTLN1 INDEX_RS_ASC(@"SEL$1" "EMPLOYEES1"@"SEL$1" ("EMPLOYEES1"."EMPLOYEE_ID" "EMPLOYEES1"."FIRST_NAME"))
OTLN1 OUTLINE_LEAF(@"SEL$1")
OTLN1 ALL_ROWS
OTLN1 DB_VERSION('11.2.0.1')
OTLN1 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
OTLN1 IGNORE_OPTIM_EMBEDDED_HINTS
12 rows selected.
修改ol$表，使hintcount字段对调。
HR@ prod> select ol_name , hintcount from ol$ ;
OL_NAME HINTCOUNT
------------------------------ ----------
OTLN1 6
OTLN2 6
这里两个相同，不用更改了。
启用私有outline，查看效果是否达到了。
HR@ prod> alter session set use_private_outlines = true ;
Session altered.
HR@ prod> set autotrace on
HR@ prod> select last_name from employees1 where employee_id = 100 ;
LAST_NAME
-------------------------
King
Execution Plan
----------------------------------------------------------
Plan hash value: 2093088777
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX2 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- outline "OTLN1" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可知，原语句已经使用了加了hints后的执行计划。
将结果固化（私有outline是临时的，放在临时表中，重连会话后就没有了）
用正确的private outline替换掉原来的outline（为了不至于同一语句出现两个outline）
HR@ prod> create or replace outline pub_otln from private otln1 for category c1 ;
Outline created.
HR@ prod> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dbsvr1 dbhome_1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 8 08:58:49 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
重连，在会话启用outline，查看结果是否有效。
SYS@ prod> conn hr/hr
Connected.
HR@ prod> alter session set use_stored_outlines=c1 ;
Session altered.
HR@ prod> set autotrace on
HR@ prod> select last_name from employees1 where employee_id = 100 ;
LAST_NAME
-------------------------
King
Execution Plan
----------------------------------------------------------
Plan hash value: 2093088777
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX2 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- outline "PUB_OTLN" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2rows processed
目标达成。
