1.初始化环境 1）创建用户secooler，并授予适当权限，注意，用户需要具有create any outline权限 SYS@PROD> create user secooler identified by secooler;
User created.
SYS@PROD> grant connect,resource to secooler;
Grant succeeded.
SYS@PROD> grant create any outline,alter any outline to secooler;
Grant succeeded.
2）在secooler用户中创建表T SYS@PROD> conn secooler/secooler Connected. SECOOLER@PROD> create table t as select * from all_objects;
Table created.
SECOOLER@PROD> select count(*) from t;
COUNT(*) ---------- 4448
2.创建outline 1）解锁outln用户 SECOOLER@PROD> conn / as sysdba Connected. SYS@PROD> alter user outln identified by outln account unlock;
User altered.
2）创建一个outline，取名叫做t_outln1，指定它的category名字为CATEGORY_T SYS@PROD> conn secooler/secooler Connected. SECOOLER@PROD> create outline t_outln1 for category CATEGORY_T on select * from t where OBJECT_ID=258;
Outline created.
3）此时outln用户下的三张表OL$、OL$HINTS和OL$NODES中便会记录与此次操作的相关信息。执行计划会记录在OL$HINTS中。 SECOOLER@PROD> conn outln/outln Connected. OUTLN@PROD> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;
4）比对一下对应的执行计划 OUTLN@PROD> conn secooler/secooler Connected. SECOOLER@PROD> set autotrace traceonly explain; SECOOLER@PROD> select * from t where OBJECT_ID=258;
Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note ----- - dynamic sampling used for this statement
这里记录了一个全表扫描的执行计划。
5）关于创建的outline基本信息也可以通过dba_outlines视图进行查询 SECOOLER@PROD> conn / as sysdba Connected. SYS@PROD> select NAME,OWNER,CATEGORY,SQL_TEXT from dba_outlines;
NAME OWNER ------------------------------ ------------------------------ CATEGORY ------------------------------ SQL_TEXT ------------------------------------------------------------- T_OUTLN1 SECOOLER CATEGORY_T select * from t where OBJECT_ID=258
3.使用outline 1）为了对比，我们创建索引，改变SQL语句的执行计划 （1）在T表的X字段创建索引 SYS@PROD> conn secooler/secooler Connected. SECOOLER@PROD> create index i_t on t(object_id);
Index created.
（2）查看此时SQL的执行计划 SECOOLER@PROD> set lines 200 SECOOLER@PROD> set autotrace traceonly explain; SECOOLER@PROD> select * from t where OBJECT_ID=258;
Execution Plan ---------------------------------------------------------- Plan hash value: 2928007915
------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("OBJECT_ID"=258)
Note ----- - dynamic sampling used for this statement
从执行计划上可以看到此时该SQL使用到了索引，没有进行全表扫面。
2）强制SQL使用ontline中记录的执行计划 （1）设置会话使用category为CATEGORY_T的outline SECOOLER@PROD> alter session set use_stored_outlines=CATEGORY_T;
Session altered.
（2）再次查看SQL语句的执行计划 SECOOLER@PROD> select * from t where OBJECT_ID=258;
Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("OBJECT_ID"=258)
Note ----- - outline "T_OUTLN1" used for this statement
SECOOLER@PROD> alter outline t_outln1 disable;
Outline altered.
SECOOLER@PROD> select * from t where OBJECT_ID=258;
Execution Plan ---------------------------------------------------------- Plan hash value: 2928007915
------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("OBJECT_ID"=258)
Note ----- - dynamic sampling used for this statement
此时ontline T_OUTLN1已经被停用。
4.清除outline 我们在10g环境下可以使用dbms_outln.drop_by_cat完成清空具体category的目的。 SECOOLER@PROD> conn outln/outln Connected. OUTLN@PROD> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;
OUTLN@PROD> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;
no rows selected
5.关于USE_STORED_OUTLINES参数的说明 USE_STORED_OUTLINES参数不像一般的参数可以在参数文件中进行设定，但我们可以使用常规的方法对其进行修改。 ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE | category; ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE | category;
