月別アーカイブ: 2016年6月

インデックス領域を含まない全体バックアップ（RMAN）

マニュアルを読んでいて気がついた

先日、12cの概要マニュアルを何気なく読んでいたら以下の文章が目に入ってきた。

Oracle® Database概要
12cリリース1 (12.1)
B71299-07

索引記憶域
「索引セグメントの表領域は、所有者のデフォルト表領域またはCREATE INDEX文で明示的に指定された表領域です。管理を容易にするために、索引をその表とは別の表領域に格納できます。たとえば、索引のみを含む表領域は再構築できるため、これらの表領域をバックアップしないよう指定することによって、バックアップに必要な時間と記憶域を削減できます。」

バックアップ領域の問題

以前関わっていた某超巨大システムでは、バックアップ領域は常に問題を抱えていた。

時間の経過と共にデータ量が増加し、バックアップ時間が長くなるだけでなく、バックアップ先のディスク容量が逼迫して新たにストレージを追加するという問題が深刻になっていた。

データ量が多いシステムほどバックアップに関する悩みは大きいが、上に紹介したマニュアルの記述はまさに目からウロコであった。仮にインデックスが占めるサイズが全体の3割であれば、バックアップ容量と時間を一気に3割削減できることになる。これは大きい。

万一ストレージが全損してバックアップからリカバリすることになった場合、インデックスを再作成する手間と時間がかかるが、日常のバックアップ容量と時間を削減することのメリットの方が大きい。

テーブルとインデックスの表領域を分ける意味

昔は、テーブルとインデックスのI/O競合を避けるために、両者を格納する表領域を分けるということはごく当たり前に行われていた。

しかし、ASMが一般的になりSAMEアーキテクチャによって理論的にはI/Oホットスポットが発生しなくなってからは、管理が容易という理由でデータとインデックスを１つの大きな表領域で管理することが普通になった。

テーブル用とインデック用の表領域をサイジングを間違って空き容量にアンバランスを発生させたりということに頭を使うよりも、全部を１つにしてしまえば単純である。

ところが、バックアップ対象を分けるという意味では、テーブルとインデックス用の表領域を別個に設けるという考え方が新しく成り立つ。

インデックス用表領域を含まない全体バックアップ

インデックスをインデックス用表領域に移動する

それでは実際にインデックス表領域を全体バックアップに含まない方法を確認してみよう。

まず、インデックスをインデックス用表領域に移動させる。これは個々のインデックスに対して必要であるが、一度行ってしまえばよい。

SQL> alter index SCOTT.PK_EMP rebuild tablespace USER_INDEX;

Index altered.

RMANの起動とデフォルト設定の確認

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 金 6月 3 06:11:07 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1439336626)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

除外表領域の指定

特定の表領域をバックアップ対象から外すためには「CONFIGURE EXCLUDE FOR TABLESPACE」コマンドによって設定する。

これによりハイライト行に示すように除外表領域が指定される。

RMAN> configure exclude for tablespace USER_INDEX;

Tablespace USER_INDEX will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
..........................................................
CONFIGURE EXCLUDE FOR TABLESPACE 'USER_INDEX';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

バックアップの実行

除外設定を行った後は、通常と同様に「BACKUP DATABASE」コマンドで全体バックアップを取得する。

RMAN> backup database;

Starting backup at 16-06-03
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
file 6 is excluded from whole database backup
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/app/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/app/oradata/orcl/system01.dbf
input datafile file number=00003 name=/opt/oracle/app/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/opt/oracle/app/oradata/orcl/example01.dbf
input datafile file number=00004 name=/opt/oracle/app/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T061125_co187xbq_.bkp tag=TAG20160603T061125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T061125_co189s2r_.bkp tag=TAG20160603T061125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-06-03

「LIST BACKUP」コマンドでバックアップの内容を確認すると、USER_INDEX表領域が含まれていないことがわかる。

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.36M      DISK        00:00:01     16-06-03
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T060715
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T060715_co18053x_.bkp
  SPFILE Included: Modification time: 16-06-02
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2328889      Ckp time: 16-06-03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.24G      DISK        00:00:49     16-06-03
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T061125_co187xbq_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time Name
  ---- -- ---- ---------- -------- ----
  1       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/system01.dbf
  2       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/sysaux01.dbf
  3       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/undotbs01.dbf
  4       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/users01.dbf
  5       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.36M      DISK        00:00:04     16-06-03
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T061125_co189s2r_.bkp
  SPFILE Included: Modification time: 16-06-03
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2329721      Ckp time: 16-06-03

除外設定を無効にする

NOEXCLUDE句を指定すると除外設定を無効にしてバックアップを取得することができる。

バックアップのログにUSER_INDEX表領域が含まれている。（12行目）

RMAN> backup database noexclude;

Starting backup at 16-06-03
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/app/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/app/oradata/orcl/system01.dbf
input datafile file number=00003 name=/opt/oracle/app/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/opt/oracle/app/oradata/orcl/example01.dbf
input datafile file number=00006 name=/opt/oracle/app/oradata/orcl/user_index01.dbf    --バックアップ対象となっている
input datafile file number=00004 name=/opt/oracle/app/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T065225_co1bnslm_.bkp tag=TAG20160603T065225 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T065225_co1boxno_.bkp tag=TAG20160603T065225 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-06-03

LIST BACKUPでバックアップの内容を確認すると、やはりUSER_INDEX表領域がバックアップに含まれていることがわかる。

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.36M      DISK        00:00:01     16-06-03
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T060715
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T060715_co18053x_.bkp
  SPFILE Included: Modification time: 16-06-02
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2328889      Ckp time: 16-06-03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.24G      DISK        00:00:49     16-06-03
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T061125_co187xbq_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time Name
  ---- -- ---- ---------- -------- ----
  1       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/system01.dbf
  2       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/sysaux01.dbf
  3       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/undotbs01.dbf
  4       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/users01.dbf
  5       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.36M      DISK        00:00:04     16-06-03
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T061125_co189s2r_.bkp
  SPFILE Included: Modification time: 16-06-03
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2329721      Ckp time: 16-06-03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    1.24G      DISK        00:00:27     16-06-03
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T065225
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T065225_co1bnslm_.bkp
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time Name
  ---- -- ---- ---------- -------- ----
  1       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/system01.dbf
  2       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/sysaux01.dbf
  3       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/undotbs01.dbf
  4       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/users01.dbf
  5       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/example01.dbf
  6       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/user_index01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    9.36M      DISK        00:00:01     16-06-03
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T065225
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T065225_co1boxno_.bkp
  SPFILE Included: Modification time: 16-06-03
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2332008      Ckp time: 16-06-03

今日はここまで。

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

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

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

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

コストベース・オプティマイザの挙動を調べるには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.

今日はここまで。

不可視索引はUSE_INVISIBLE_INDEXESヒントと共に使おう

不可視索引とは

不可視索引とはオプティマイザから「見えない」という意味で不可視である。（12c概要 索引の使用可能性と可視性 参照）

オプティマイザに影響を与えないので、通常の索引（可視索引）作成で既存の実行計画を不用意に変えてしまうリスクを回避することができる、11g以降で実装されている機能である。

不可視索引の用途

本番運用が始まってから、「この列にインデックスが必要だ。」とか「このインデックスはどうも使われていないようなので削除しよう。」という定義変更のニーズが発生することは多々ある。

上記マニュアルには

  1. 索引を削除する前に削除をテストする場合
  2. アプリケーション全体に影響を与えることなく一時的に索引を使用する場合

という２つの使い方の例が示されているが、2.の「一時的」とはそのインデックスの有効性を確認するテストなので、（恒常的な）運用に乗せるためには

SQL> ALTER INDEX <インデックス名> VISIBLE;

として、不可視→可視とする必要があると、マニュアルや多くのブログ記事等には書いてある。

というようなことを先日あるプロジェクトの人に話したところ「可視化した時点でアプリケーション全体に影響を与える可能性があるので望ましくない。SQL単位で可視化を制御する方法はないのか？」と質問された。

確かにもっともな意見である。普段いろいろ教える機会が多いのだが、実際に使う側の目線で本質的な問いを投げかけられるとハッとさせられる。

SQL単位でとなると答えはヒント句による制御しかない。

不可視索引関連のヒント

結論から先に言うと、不可視索引関連のヒントは

  • USE_INVISIBLE_INDEXES（NO_USE_INVISIBLE_INDEXES）

であるが、残念ながらSQLリファレンス・マニュアルにはこのヒントに関する記述がない。

（以前、Oracleバージョンによるヒント句の変遷 という記事を書いたのでそちらを参照してもらいたい。）

ヒント句を検証してみた

不可視索引の作成

検証を行うためにEMP表のHIRE_DATE列に不可視索引を作成する。

SQL> create index EMP_HIRE_DATE_IX on EMPLOYEES (HIRE_DATE) invisible;

Index created.

SQL> select TABLE_NAME,INDEX_NAME,VISIBILITY from user_indexes
  2  where VISIBILITY != 'VISIBLE'
  3  order by TABLE_NAME,INDEX_NAME;

TABLE_NAME                     INDEX_NAME                     VISIBILIT
------------------------------ ------------------------------ ---------
EMPLOYEES                      EMP_HIRE_DATE_IX               INVISIBLE

基本動作（不可視索引はそのままでは使われない）

不可視索引はデフォルトではオプティマイザに使用されないので、条件検索は全件検索からのフィルタ処理となる。（Id=1）

SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  2  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'))

OPTIMIZER_USE_INVISIBLE_INDEXESパラメータをセッション単位で変更

不可視索引を使用するためにはALTER SESSIONコマンドにより、OPTIMIZER_USE_INVISIBLE_INDEXESパラメータをセッション単位で変更する。
（ALTER SYSTEMコマンドによりインスタンスで使用可能に変更することもできるが、不可視索引として作成する意味がないので現実的ではない。）

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

この状態で先ほどの問合せを実行すると、使用されなかったインデックスEMP_HIRE_DATE_IXが使用されるようになったことがわかる。

SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES  
  2  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'))

ヒント句を試してみる

ヒント句を試す前に、セッションを再接続することでクリアし、OPTIMIZER_USE_INVISIBLE_INDEXESパラメータがデフォルトの「FALSE」に戻っていることを確認する。

SQL> conn hr/hr
Connected.
SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

INDEXヒントのみ

まず、INDEXヒントにより不可視索引を指定した場合どのような挙動になるのかを確認する。

SQL> select /*+ INDEX (employees emp_hire_date_ix) */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  3  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'))

オプティマイザがこのインデックスを使用するようになっていないため、ヒント句でインデックス名を指定したとしても無視されることがわかる。

USE_INVISIBLE_INDEXESヒントのみ

次に、USE_INVISIBLE_INDEXESヒントをヒント句で指定してみる。

SQL> select /*+ USE_INVISIBLE_INDEXES */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  3  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'))

今度は、インデックスを使用するようになった。

INDEXヒント＋USE_INVISIBLE_INDEXESヒント

基本的にUSE_INVISIBLE_INDEXESヒントを指定するだけでよいのだが、もし複数の不可視索引が定義されていたりする場合は、どのインデックスを使用するべきかをINDEXヒントで明確に指定することができる。 （この部分は事実と異なる。NO_INDEXヒントにより使いたくない索引も明示的に指定する必要がある。 USE_INVISIBLE_INDEXESヒントについて（続編） 参照）

SQL> select /*+ USE_INVISIBLE_INDEXES INDEX(EMPLOYEES EMP_HIRE_DATE_IX)  */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  3  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'))

SQL文の可読性を向上させるために、使用したいインデックスを明示的に指定した方がよいかもしれない。

前々回、前回とWHERE句とインデックスの関係を調査する

という２つの記事を書いたが、新しく作成するインデックスが及ぼす影響を網羅性を担保しつつ調査することは、インデックスの数が多いほど大変な作業である。

ヒント句で有効にした不可視索引はアプリケーション全体に影響を与えることなく性能を改善することができる。不可視索引を使うには原則としてヒント句を共に使うべきであるというのが私の提言である。

今日はここまで。