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;
