【OUTLINE】使用Oracle Outline技術暫時鎖定SQL的執行計劃

  Oracle的Outline技術可以在特殊情況下保證執行計劃的穩定性。在極端情況下可以使用此項技術實作暫時鎖定執行計劃的目的。

  主要使用場景如下：

  ①短時間内無法完成SQL的優化任務，此時可以使用outline暫時鎖定SQL執行計劃；

  ②在CBO優化模式下，當統計資訊出現問題時，會導緻執行計劃出現異常變化，此時可以使用outline暫時調整SQL執行計劃；

  ③由于資料庫的bug導緻SQL的執行計劃出現異常，使用outline鎖定執行計劃。

  記錄一下關于outline的使用方法，供參考。

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;

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

SYS@PROD> alter user outln identified by outln account unlock;

User altered.

2）建立一個outline，取名叫做t_outln1，指定它的category名字為CATEGORY_T

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

OUTLN@PROD> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;

HINT_TEXT

--------------------------------------------------------------------------------

FULL(@"SEL$1" "T"@"SEL$1")

OUTLINE_LEAF(@"SEL$1")

ALL_ROWS

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

4）比對一下對應的執行計劃

OUTLN@PROD> conn secooler/secooler

SECOOLER@PROD> set autotrace traceonly explain;

SECOOLER@PROD> select * from t where OBJECT_ID=258;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT  |      |     1 |   128 |    19   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |   128 |    19   (0)| 00:00:01 |

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID"=258)

Note

-----

   - dynamic sampling used for this statement

這裡記錄了一個全表掃描的執行計劃。

5）關于建立的outline基本資訊也可以通過dba_outlines視圖進行查詢

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字段建立索引

SECOOLER@PROD> create index i_t on t(object_id);

Index created.

（2）檢視此時SQL的執行計劃

SECOOLER@PROD> set lines 200

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 |

   2 - access("OBJECT_ID"=258)

從執行計劃上可以看到此時該SQL使用到了索引，沒有進行全表掃面。

2）強制SQL使用ontline中記錄的執行計劃

（1）設定會話使用category為CATEGORY_T的outline

SECOOLER@PROD> alter session set use_stored_outlines=CATEGORY_T;

Session altered.

（2）再次檢視SQL語句的執行計劃

|   0 | SELECT STATEMENT  |      |    51 |  6528 |    19   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |    51 |  6528 |    19   (0)| 00:00:01 |

   - outline "T_OUTLN1" used for this statement

從最後的注釋上我們已經可以看到，此時SQL語句執行過程中使用的是outln中記錄的執行計劃。SQL在擷取資料的時候走的是全表掃描。

3）消除ontline對SQL語句的影響

（1）第一種方法是調整use_stored_outlines參數為false

SECOOLER@PROD> alter session set use_stored_outlines=false;

（2）第二種方法是停用具體的outline

這種方法可以在use_stored_outlines參數起作用的前提下停用具體的outline。

SECOOLER@PROD> alter outline t_outln1 disable;

Outline altered.

此時ontline T_OUTLN1已經被停用。

4.清除outline

我們在10g環境下可以使用dbms_outln.drop_by_cat完成清空具體category的目的。

------------------------------------------------------

OUTLN@PROD> exec dbms_outln.drop_by_cat('CATEGORY_T');

PL/SQL procedure successfully completed.

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;

6.小結

  通過文中的描述，大家應該對Oracle Outline技術有一個比較詳細的了解。在CBO優化模式下，很有可能遇到執行計劃不穩定及不準确的情況。在這種場景下我們可以考慮使用這項技術暫時規避執行計劃變化帶來的性能問題。

Good luck.

secooler

10.09.08

-- The End --