不可視索引の裏でオプティマイザはどう動いているか？

オプティマイザの動作を確認する

前回の投稿では、不可視索引の実践的な使い方の提言を行った。

今回は、オプティマイザが不可視索引を使用する際に、内部でどのようなことが起きているのかを調べてみる。

コストベース・オプティマイザの挙動を調べるには10053トレースを取得する。
（10053トレースにはハード・パース時の実行計画算出過程が出力されるため、以下の検証はインスタンス再起動直後に実施した。）

トレースファイルの確認と識別文字列の設定

これはトレースを取得する際のTipsだが、識別文字列を設定しておくとトレースファイルをトレース出力ディレクトリで探すのが非常に楽になる。

ここでは「CBO」という識別文字列を設定する。

SQL> select VALUE from V$DIAG_INFO where NAME = 'Default Trace File';

VALUE
--------------------------------------------------------------
/opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_28863.trc

SQL> alter session set tracefile_identifier = 'CBO';

Session altered.

SQL> select VALUE from V$DIAG_INFO where NAME = 'Default Trace File';

VALUE
------------------------------------------------------------------
/opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_25629_CBO.trc

10053トレースの開始

以下のコマンドによりセッション単位で10053トレースの取得を開始する。

SQL> alter session set events '10053 trace name context forever';

Session altered.

デフォルト実行（不可視索引は使われない）

SQL> set autot on
SQL> select
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME
  3  ,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE
  4  from
  5   EMPLOYEES
  6  where HIRE_DATE <= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    27 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    27 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HIRE_DATE"<=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

10053トレース抜粋

  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
....................................................
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = false
flashback_data_archive_internal_cursor = 0
....................................................
***************************************
BASE STATISTICAL INFORMATION
***********************
....................................................
  Index: EMP_HIRE_DATE_IX  Col#: 6
    LVLS: 0  #LB: 1  #DK: 98  LB/K: 1.00  DB/K: 1.00  CLUF: 13.00
    UNUSABLE
....................................................
***************************************
SINGLE TABLE ACCESS PATH 
....................................................
  Best:: AccessPath: TableScan
         Cost: 3.00  Degree: 1  Resp: 3.00  Card: 1.09  Bytes: 0

optimizer_use_invisible_indexesパラメータの値はデフォルトの「false」なので、不可視索引EMP_HIRE_DATE_IXは「UNUSABLE」つまり使用されない状態であることがわかる。

オプティマイザが最終的に選択したアクセスパスはCost=3となるフル・テーブル・スキャンである。

ヒント句で不可視索引を指定

SQL> select /*+ USE_INVISIBLE_INDEXES INDEX(EMPLOYEES EMP_HIRE_DATE_IX)  */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME
  3  ,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE
  4  from
  5   EMPLOYEES
  6  where HIRE_DATE <= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00

Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    27 |       2 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     1 |    27 |       2 (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_HIRE_DATE_IX |     1 |       |       1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("HIRE_DATE"<=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

10053トレース抜粋

  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
....................................................
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = false
flashback_data_archive_internal_cursor = 0
....................................................
***************************************
BASE STATISTICAL INFORMATION
***********************
....................................................
  Index: EMP_HIRE_DATE_IX  Col#: 6
    LVLS: 0  #LB: 1  #DK: 98  LB/K: 1.00  DB/K: 1.00  CLUF: 13.00
    User hint to use this index
....................................................
***************************************
 SINGLE TABLE ACCESS PATH 
  Best:: AccessPath: IndexRange
  Index: EMP_HIRE_DATE_IX
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.09  Bytes: 0

「User hint to use this index」という表示から、不可視索引EMP_HIRE_DATE_IXがヒント句で有効になっていることがわかる。

このインデックスを使用してCost=2となるアクセスパスが選択された。

USE_INVISIBLE_INDEXESパラメータをTRUEに変更

SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_use_invisible_indexes      boolean     FALSE
SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_use_invisible_indexes      boolean     TRUE

SQL> select
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME
  3  ,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE
  4  from
  5   EMPLOYEES
  6  where HIRE_DATE <= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00

Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_HIRE_DATE_IX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("HIRE_DATE"<=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

10053トレース抜粋

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
_pga_max_size                       = 471840 KB
optimizer_use_invisible_indexes     = true
Bug Fix Control Environment
....................................................
  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
....................................................
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = true
flashback_data_archive_internal_cursor = 0
....................................................
***************************************
BASE STATISTICAL INFORMATION
***********************
....................................................
  Index: EMP_HIRE_DATE_IX  Col#: 6
    LVLS: 0  #LB: 1  #DK: 98  LB/K: 1.00  DB/K: 1.00  CLUF: 13.00
....................................................
***************************************
SINGLE TABLE ACCESS PATH 
....................................................
  Best:: AccessPath: IndexRange
  Index: EMP_HIRE_DATE_IX
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.09  Bytes: 0
....................................................

USE_INVISIBLE_INDEXESパラメータをALTER SESSIONコマンドにより「true」に変更している。これによりインデックスEMP_HIRE_DATE_IXは実行計画算出に使用されるようになる。

２番目の例と同様にCost=2のアクセスパスが選択された。

10053トレースの終了

10053トレースを終了させるためには以下のコマンドを実行させる。

SQL> alter session set events '10053 trace name context off';

Session altered.

今日はここまで。