ストアドアウトラインによる実行計画の固定のメモ。一応 10g までが対象。(11g からは SPM)

　例題として、現在、INDEX UNIQUE SCAN を行う以下の問い合わせがあったとします。

SQL> select * from emp where empno=999;

実行計画
---------------------------------------------------
Plan hash value: *********

----------------------------------------------
| Id  | Operation                   | Name   |
----------------------------------------------
|   0 | SELECT STATEMENT            |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |
----------------------------------------------

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

   2 - access("EMPNO"=999)

　この問い合わせの実行計画を INDEX UNIQUE SCAN ではなく、TABLE ACCESS FULL となるように変更したいとします。

　手順は以下。

1. ヒントなしのアウトラインを作成します。

   -- ヒントなしのアウトラインを作成
   create or replace outline NO_HINT
   ON
   select * from emp where empno=999;
   

2. TABLE ACCESS FULLとなるようヒントを指定した時のアウトラインを作成します。

   -- ヒント指定のアウトラインを作成
   create or replace outline SET_HINT
   ON
   select /*+ full(emp) */ * from emp where empno=999;
   

3. 双方のヒントを入れ替えます。(*1)

   -- ヒントの入れ替え
   update outln.ol$hints
      set ol_name = decode(ol_name,'SET_HINT','NO_HINT','NO_HINT','SET_HINT')
    where ol_name in ('SET_HINT','NO_HINT')
   ;
   commit;
   

4. ストアドアウトラインを有効にします。(*2)

   alter system set USE_STORED_OUTLINES=TRUE;
   

　このようにすると、ヒント句なしで問い合わせを実行しても、TABLE ACCESS FULL を選択するようになります。(*3)
　（実行計画レポートの note のところに ストアドアウトラインを使用しているという表示が出ます。）

SQL> select * from emp where empno=999;

実行計画
--------------------------------------------------
Plan hash value: *********

----------------------------------
| Id  | Operation         | Name | 
----------------------------------
|   0 | SELECT STATEMENT  |      | 
|*  1 |  TABLE ACCESS FULL| EMP  |
----------------------------------

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

   1 - filter("EMPNO"=999)

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