Oracle 存储提纲（stored outline）

详细参考 Oracle性能优化求生指南

Stored outline是为了保证执行计划的稳定性，尤其是指在Oracle重新收集统计信息之后。已经逐渐被放弃，取而代之的是计划基线（PLAN BASELINE，在10g中并不完全可用）。存储提纲的使用：锁定下面这条语句的当前的执行计划（使以后执行这条语句时都用这个执行计划）HR@ prod> select last_name from employees1 where employee_id = 100 ;LAST_NAME-------------------------KingExecution Plan----------------------------------------------------------Plan hash value: 2613790398------------------------------------------------------------------------------------------| 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_IDX1  |     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创建outline，并为其指定目录。HR@ prod> create outline hr_emp1_qry for category outline1 on   2  select last_name from employees1 where employee_id = 100 ;select last_name from employees1 where employee_id = 100                      *ERROR at line 2:ORA-18005: CREATE ANY OUTLINE privilege is required for this operationHR@ prod> conn / as sysdbaConnected.SYS@ prod> grant create any outline to hr ;Grant succeeded.SYS@ prod> conn hr/hrConnected.HR@ prod> create outline hr_emp1_qry for category outline1 on   2  select last_name from employees1 where employee_id = 100 ;Outline created.Outline虽然创建了，但是并未激活，所以还不会使用。HR@ prod> select last_name from employees1 where employee_id = 100 ;LAST_NAME-------------------------KingHR@ prod> set autotrace onHR@ prod> select last_name from employees1 where employee_id = 100 ;LAST_NAME-------------------------KingExecution Plan----------------------------------------------------------Plan hash value: 2613790398------------------------------------------------------------------------------------------| 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_IDX1  |     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在会话级激活这个outline category，也可以是实例级。HR@ prod> alter session set use_stored_outlines = outline1 ;Session altered.HR@ prod> select last_name from employees1 where employee_id = 100 ;LAST_NAME-------------------------KingExecution Plan----------------------------------------------------------Plan hash value: 2613790398------------------------------------------------------------------------------------------| 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_IDX1  |     1 |       |     1   (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPLOYEE_ID"=100)Note-----   - outline "HR_EMP1_QRY" 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 processedSQL语句发生了一点大小写变化，还是会用这个OUTLINE。HR@ prod> SELECT last_name from employees1 where employee_id = 100 ;LAST_NAME-------------------------KingExecution Plan----------------------------------------------------------Plan hash value: 2613790398------------------------------------------------------------------------------------------| 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_IDX1  |     1 |       |     1   (0)| 00:00:01 |------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPLOYEE_ID"=100)Note-----   - outline "HR_EMP1_QRY" used for this statementStatistics----------------------------------------------------------          1  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