摘要:《Stored Outline》
2009/7/28
今天要演練的題目是 Stored Outline 的實作。
因為公司以前的資料庫疏於管理，從未作過資料表的分析及統計資料的搜集。
optimize mode 目前設定為 CHOOSE。應用程式持續開發，隨著時間一久，已經無法隨意分析資料表；
因為會影響到執行計劃的產生，之前未分析過資料表，所有SQL的執行計劃都是RBO (rule base optimize)，
一但執行了分析的作業，相信大部份的SQL都會以CBO (cost base optimize) 產生執行計劃。
那是何其大的影響，不可不慎為；
若要平順的分析這些資料表，可以有以下方法。
1. set optimizer mode = RULE
以後所有的SQL都是RBO。既不影響現有應用程式作業，也可持續分析這些資料表。
2. 運用 hint (下次再寫 hint 的實作
不過要動的程式太多，而且如果真的要這樣做，那不如一次審視所有的SQL。
3. stored outline
也就是本篇要實作的項目；收集足夠的outline後，將所有的SQL的執行計劃都儲存下來，就可以放心改為CBO的模式，
這也是oracle 強烈建議大家要定期分析資料表，從某一版本的oracle 已不再對 RBO 進行任何的改進，所以這是勢必要面對的課題。
--簡介些指令如下：
GRANT CREATE ANY OUTLINE TO user
--授權 create outline 權限
GRANT DROP ANY OUTLINE TO user
--授權 drop outline 權限
CREATE OUTLINE catagory ON statement
--對單一 SQL 儲存其 outline
ALTER SESSION SET CREATE_STORED_OUTLINES = {TRUE|CATAGORY|FALSE}
--對當前session所執行的SQL儲存outline
ALTER SESSION SET USE_STORED_OUTLINES = {TRUE|CATAGORY|FALSE}
--當前session設定使用outline (先自outline讀取執行計劃，找不到再至shared pool找執行計劃)
ALTER SYSTEM SET CREATE_STORED_OUTLINES = {TRUE|CATAGORY|FALSE}
--對資料庫所有的 SQL 儲存其 outline
ALTER SYSTEM SET USE_STORED_OUTLINES = {TRUE|CATAGORY|FALSE}
--資料庫所有連線均使用 outline (or not)
--outline 查詢資料表:
-- DBA_STORED_OUTLINES
--SQL 是否有使用 outline:
-- V$SQL.OUTLINE_CATAGORY 會有所註記。
--=========================================================
SQL> SHOW USER
USER 為 "SYS"
SQL> GRANT CREATE ANY OUTLINE TO TING;
順利授權.
SQL> GRANT DROP ANY OUTLINE TO TING;
順利授權.
-----------------------------------------------------------------
SQL> CONN TING
輸入密碼:
已連線.
SQL> SHOW USER;
USER 為 "TING"
SQL> CREATE TABLE OUTLNTEST AS SELECT LEVEL AS F1 FROM DUAL CONNECT BY LEVEL<99
999;
已建立表格.
SQL> VARIABLE V1 VARCHAR2(50)
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT *FROM OUTLNTEST WHERE F1=:V1;
沒有任何資料列被選取
執行計畫
----------------------------------------------------------
Plan hash value: 1975896541
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 13000 | 40 (13)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OUTLNTEST | 1000 | 13000 | 40 (13)| 00:00:01 |
-------------------------------------------------------------------------------
SQL> CREATE OUTLINE CATA ON SELECT *FROM OUTLNTEST WHERE F1=:V1;
已建立邊框.
SQL> CREATE INDEX OUTLNTEST_I_F1 ON OUTLNTEST (F1);
已建立索引.
SQL> ALTER SESSION SET USE_STORED_OUTLINES=TRUE;
已更改階段作業.
--==========================================================
-- 設定使用 outline，所以就算建立的索引，還是full table scan
--==========================================================
SQL> SELECT *FROM OUTLNTEST WHERE F1=:V1;
沒有任何資料列被選取
執行計畫
----------------------------------------------------------
Plan hash value: 1975896541
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124 | 1612 | 36 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OUTLNTEST | 124 | 1612 | 36 (3)| 00:00:01 |
-------------------------------------------------------------------------------
SQL> VARIABLE V2 VARCHAR2(8);
SQL> SELECT *FROM OUTLNTEST WHERE F1=:V2;
沒有任何資料列被選取
執行計畫
----------------------------------------------------------
Plan hash value: 2604949381
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 13000 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| OUTLNTEST_I_F1 | 1000 | 13000 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
SQL> DROP OUTLINE CATA;
已移除邊框.
SQL> SELECT *FROM OUTLNTEST WHERE F1=:V1;
沒有任何資料列被選取
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT *FROM OUTLNTEST WHERE F1=:V1;
--==========================================================
-- 刪除 outline ，馬上使用索引
--==========================================================
沒有任何資料列被選取
執行計畫
----------------------------------------------------------
Plan hash value: 2604949381
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 13000 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| OUTLNTEST_I_F1 | 1000 | 13000 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
SQL>