關於OUTLINE的使用方法請參見文章《【OUTLINE】使用Oracle Outline技術暫時鎖定SQL的執行計劃》（http://space.itpub.net/519536/viewspace-673010）。

我們這裡測試環境不具備OUTLINE中記錄的執行計劃時的執行效果。

1.環境準備
1）建立使用者secooler，並授予適當許可權，注意，使用者需要具有create any outline許可權
sys@ora10g> create tablespace tbs_secooler_d datafile '/oracle/ora10gR2/oradata/ora10g/tbs_secooler_d_01.dbf' size 10m autoextend on;

Tablespace created.

sys@ora10g> create user secooler identified by secooler default tablespace TBS_SECOOLER_D;

User created.

sys@ora10g> grant connect,resource to secooler;

Grant succeeded.

sys@ora10g> grant create any outline to secooler;

Grant succeeded.

2）在secooler使用者中建立表T
sys@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> create table t as select * from all_objects;

Table created.

secooler@ora10g> select count(*) from t;

COUNT(*)
----------
      4370

3）在T表中建立索引
secooler@ora10g> create index i_t on t(object_id);

Index created.

4）此時的執行計劃
secooler@ora10g> 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

2.建立OUTLINE
1）解鎖OUTLN使用者
sys@ora10g> conn / as sysdba
Connected.
sys@ora10g> alter user outln identified by outln account unlock;

User altered.

2）建立一個outline，取名叫做t_outln1，指定它的category名字為CATEGORY_T
outln@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> create outline t_outln1 for category CATEGORY_T on select * from t where OBJECT_ID=258;

Outline created.

此時建立了一個訪問表T時走索引的OUTLINE。

3.使用OUTLINE
1）調整當前session使用CATEGORY_T這個OUTLINE
secooler@ora10g> alter session set use_stored_outlines=CATEGORY_T;

Session altered.

（2）檢視此時SQL的執行計劃
secooler@ora10g> set autotrace traceonly explain;
secooler@ora10g> select * from t where OBJECT_ID=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915

------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    50 | 6400 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T    |    50 | 6400 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T |    20 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=258)

Note
-----
   - outline "T_OUTLN1" used for this statement

從執行計劃上可以看到此時該SQL使用到了索引。

4.將T表索引刪除測試使用OUTLINE效果
secooler@ora10g> drop index i_t;

Index dropped.

secooler@ora10g> alter session set use_stored_outlines=CATEGORY_T;

Session altered.

secooler@ora10g> set autotrace traceonly explain;
secooler@ora10g> select * from t where OBJECT_ID=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |    50 | 6400 |    15   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |    50 | 6400 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=258)

Note
-----
   - outline "T_OUTLN1" used for this statement

結論已經揭曉：
當表及索引資訊不滿足OUTLINE記錄的執行計劃時，Oracle並不會報錯，而是走其他執行計劃以便能夠返回正確執行結果。

5.小結
OUTLINE並非“強制”按照記錄的執行計劃行事，當滿足條件時會按照OUTLINE中的執行計劃執行，當環境不滿足時，也不會報錯，SQL將選擇其他的執行計劃。

Good luck.

secooler
11.07.03

-- The End --

【OUTLINE】環境不滿足OUTLINE記錄的執行計劃時會選擇其他執行計劃

相關文章