原文链接 个人博客 http://www.killdb.com/?p=182
我们知道outlines 特性在oracle 8i就引入了，不过我用的很少，今天同事问到了，
我也就再回顾温习一下，如下是做的简单测试。
首先先来看2个跟outline相关的参数：
create_stored_outlines ---控制是否自动创建outline
use_stored_outlines ---控制是否启用outline
使用outline的方式有很多种，列出如下几种方式：
1. 针对sql语句或sqlid SQL> conn roger/roger Connected. SQL> alter session set create_stored_outlines = true;
Session altered.
SQL> select * from v$version where rownum <2;
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
SQL> SQL> create outline test_id for CATEGORY test_outlines on 2 select owner,object_id from test_ht where object_id=:p;
Outline created.
SQL> select name,category,sql_text from user_outlines where category=upper('test_outlines');
NAME CATEGORY SQL_TEXT --------- ----------------- ------------------------------------------------------- TEST_ID TEST_OUTLINES select owner,object_id from test_ht where object_id=:p
SQL> select * from user_outline_hints where name=upper('test_id');
NAME NODE STAGE JOIN_POS HINT ------- ---- ------ -------- ---------------------------------------------------------------- TEST_ID 1 1 1 INDEX_RS_ASC(@"SEL$1" "TEST_HT"@"SEL$1" ("TEST_HT"."OBJECT_ID")) TEST_ID 1 1 0 OUTLINE_LEAF(@"SEL$1") TEST_ID 1 1 0 ALL_ROWS TEST_ID 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.4') TEST_ID 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
SQL> select name,category,used from user_outlines where category=upper('test_outlines');
NAME CATEGORY USED --------------- ------------------------------ ------ TEST_ID TEST_OUTLINES UNUSED
SQL> --该outline未使用
SQL> var p number; SQL> exec :p :=1000;
PL/SQL procedure successfully completed.
SQL> select name,category,used from user_outlines where category=upper('test_outlines');
NAME CATEGORY USED --------------- ------------------------------ ------ TEST_ID TEST_OUTLINES UNUSED
SQL>
---设置参数use_stored_outlines
SQL> var p number; SQL> exec :p :=10000;
PL/SQL procedure successfully completed.
SQL> select owner,object_id from test_ht where object_id=:p;
OWNER OBJECT_ID ------------------------------ ---------- WMSYS 10000
SQL> select name,category,used from user_outlines where category=upper('test_outlines');
NAME CATEGORY USED --------------- ------------------------------ ------ TEST_ID TEST_OUTLINES UNUSED
SQL> alter session set use_stored_outlines=TEST_OUTLINES 2 ;
Session altered.
SQL> var p number; SQL> exec :p :=20000;
PL/SQL procedure successfully completed.
SQL> select owner,object_id from test_ht where object_id=:p;
OWNER OBJECT_ID ------------------------------ ---------- SYS 20000
SQL>
SQL> select name,category,used from user_outlines where category=upper('test_outlines');
NAME CATEGORY USED --------------- ------------------------------ ------ TEST_ID TEST_OUTLINES USED
SQL> SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,object_id%';
SQL_ID SQL_TEXT ------------- --------------------------------------------------------------------------- 3v6z2kwca0ttm select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,ob ject_id%'
45s1gxyr1y5k3 select owner,object_id from test_ht where object_id=:p 6cc34dzmkg686 create table test_ht as select owner,object_id,object_name from dba_objects
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR('&sql_id',null));
Enter value for sql_id: 45s1gxyr1y5k3
old 1: select * from table(dbms_xplan.DISPLAY_CURSOR('&sql_id',null))
new 1: select * from table(dbms_xplan.DISPLAY_CURSOR('45s1gxyr1y5k3',null))
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- SQL_ID 45s1gxyr1y5k3, child number 0 ------------------------------------- select owner,object_id from test_ht where object_id=:p
Plan hash value: 793292976
--------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_HT | 1 | 9 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- |* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("OBJECT_ID"=:P)
SQL_ID 45s1gxyr1y5k3, child number 1 ------------------------------------- select owner,object_id from test_ht where object_id=:p
PLAN_TABLE_OUTPUT -----------------------------------------------------------------------------------------------
Plan hash value: 793292976
--------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_HT | 1 | 9 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("OBJECT_ID"=:P)
Note ----- - outline "TEST_ID" used for this statement
42 rows selected.
SQL>
----对于存在的cursor创建outline
SQL> select owner,object_id,object_name from test_ht where object_id=3000;
OWNER OBJECT_ID ------------------------------ ---------- OBJECT_NAME ------------------------------------------------------------------ SYS 3000 EXU8SYNU
SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,object_id%';
SQL_ID SQL_TEXT ------------- --------------------------------------------------------------------------- 3v6z2kwca0ttm select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,ob ject_id%'
8gz444rhg594f select owner,object_id,object_name from test_ht where object_id=3000 SQL> select hash_value, child_number, sql_text from v$sql where sql_text like '%select owner,object_id%';
HASH_VALUE CHILD_NUMBER SQL_TEXT ---------- ------------ --------------------------------------------------------------------------- 413165363 0 select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,ob ject_id%'
47485093 0 select hash_value, child_number, sql_text from v$sql where sql_text like '% select owner,object_id%'
3773998222 0 select owner,object_id,object_name from test_ht where object_id=3000
SQL> exec dbms_outln.create_outline(3773998222,0);
PL/SQL procedure successfully completed.
SQL> SQL> select owner,object_id,object_name from test_ht where object_id=3000;
OWNER OBJECT_ID OBJECT_NAME ------------------------------ ---------- -------------------- SYS 3000 EXU8SYNU
SQL> select hash_value, child_number, sql_text from v$sql where sql_text like '%select owner,object_id%';
HASH_VALUE CHILD_NUMBER SQL_TEXT ---------- ------------ --------------------------------------------------------------------------- 413165363 0 select sql_id,sql_text from v$sqlarea where sql_text like '%select owner,ob ject_id%'
47485093 0 select hash_value, child_number, sql_text from v$sql where sql_text like '% select owner,object_id%'
3773998222 0 select owner,object_id,object_name from test_ht where object_id=3000
SQL> select * from table(dbms_xplan.display_cursor('8gz444rhg594f'));
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- SQL_ID 8gz444rhg594f, child number 0 ------------------------------------- select owner,object_id,object_name from test_ht where object_id=3000
Plan hash value: 793292976
--------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| TEST_HT | 1 | 33 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------- |* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("OBJECT_ID"=3000)
Note ----- - outline "SYS_OUTLINE_11071210544304523" used for this statement
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------
23 rows selected.
SQL> select name,CATEGORY,USED,SQL_TEXT from user_outlines 2 where name='SYS_OUTLINE_11071210544304523';
NAME CATEGORY USED SQL_TEXT ------------------------------- --------------- ------ --------------------------------------------------------------------------- SYS_OUTLINE_11071210544304523 DEFAULT USED select owner,object_id,object_name from test_ht where object_id=3000
SQL>
这里需要说明一下的是alter session set create_stored_outlines = true;这是由于bug5454975的缘故（10204已经修复） 虽然我这里是10204，不过我还是设置了一下，列出来说明，以提醒大家。详见metalink ID 445126.1
最后再补充一下，如果不用outline，那么可以将其删除，可以通过如下的几种方式：
execute DBMS_OUTLN.drop_by_cat('TEST_OUTLINES');
execute DBMS_OUTLN.CLEAR_USED('TEST_OUTLINES');
execute DBMS_OUTLN.drop_unused; ---这是删除所有状态为unused的outline，要慎重。
SQL> show parameter outline
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _outline_bitmap_tree boolean TRUE _plan_outline_data boolean TRUE create_stored_outlines string SQL> 关于outlines的使用，有几个需要注意的地方：
1. 参数cursor_sharing设置为force时，outlines将无效； 2. literial sql的共享程度不高的情况下，使用outline会生产很多个执行计划，可能会有一些问题； 3. 一般情况我们在使用outline的时候，也是发现某个sql的执行计划不稳定的时候，由于执行计划是基于 统计信息的，那么由于生产系统中统计信息可能是在不断的变化，那么使用outline固定的执行计划不见得 一定就是最好的，这一点需要考虑。 4. 由于outlines信息的存放在用户outln下，那么该用户就显得尤为重要，维护的时候需要注意，不能随便 给drop了。 5. outline创建以后，不是说就一层不变了，可以进行编辑，至于什么时候编辑，怎么编辑，大家可以参考 metalink 文档730062.1 How to Edit a Stored Outline to Use the Plan from Another Stored Outline。
love wife love life —Roger的Oracle/MySQL数据恢复博客
Phone:18180207355 提供专业Oracle/MySQL数据恢复、性能优化、迁移升级、紧急救援等服务
