Oracle高级优化——侵入存储提纲（用PRIVATE OUTLINE）

参见 Oracle性能优化求生指南。

如果某语句当前的执行计划并不令人满意，而且这条语句又不能加hint（因为在应用中不能编辑）。那么可以用这种方法，使执行计划稳定在一个与当前执行计划不同的执行计划上。SYS@ prod> conn hr/hrConnected. 为所要调整的语句用当前的执行计划（并不是想要的）创建一个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 onHR@ prod> select /*+ index(employees1 test_idx2) */ last_name from employees1 where employee_id = 100 ;LAST_NAME-------------------------KingExecution 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 processedHR@ 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 a30HR@ prod> col hint_text for a100HR@ prod> set linesize 150HR@ prod> select ol_name , hint_text from ol$hints ;OL_NAME                        HINT_TEXT------------------------------ ----------------------------------------------------------------------------------------------------OTLN1                          OUTLINE_LEAF(@"SEL$1")OTLN1                          IGNORE_OPTIM_EMBEDDED_HINTSOTLN1                          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')OTLN1                          DB_VERSION('11.2.0.1')OTLN1                          ALL_ROWSOTLN1                          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_ROWSOTLN2                          DB_VERSION('11.2.0.1')OTLN2                          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')OTLN2                          IGNORE_OPTIM_EMBEDDED_HINTS12 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_HINTSOTLN2                          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')OTLN2                          DB_VERSION('11.2.0.1')OTLN2                          ALL_ROWSOTLN2                          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_ROWSOTLN1                          DB_VERSION('11.2.0.1')OTLN1                          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')OTLN1                          IGNORE_OPTIM_EMBEDDED_HINTS12 rows selected.修改ol$表，使hintcount字段对调。HR@ prod> select ol_name , hintcount from ol$ ;OL_NAME                         HINTCOUNT------------------------------ ----------OTLN1                                   6OTLN2                                   6这里两个相同，不用更改了。启用私有outline，查看效果是否达到了。HR@ prod> alter session set use_private_outlines = true ;Session altered.HR@ prod> set autotrace onHR@ prod> select last_name from employees1 where employee_id = 100 ;LAST_NAME-------------------------KingExecution 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 statementStatistics----------------------------------------------------------          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> quitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@dbsvr1 dbhome_1]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 8 08:58:49 2014Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options重连，在会话启用outline，查看结果是否有效。SYS@ prod> conn hr/hrConnected. HR@ prod> alter session set use_stored_outlines=c1 ;Session altered.HR@ prod> set autotrace onHR@ prod> select last_name from employees1 where employee_id = 100 ;LAST_NAME-------------------------KingExecution 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 statementStatistics----------------------------------------------------------          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目标达成。