*以下は、サポート契約締結中のお客様に毎月配信しているサポートレターより一部抜粋して掲載しています。

12.2より索引の使用状況の監視がデフォルトで有効になり、DBA_INDEX_USAGE ビューから使用状況を確認することができるようになりました。これにより以前のバージョンで必要であった監視対象の索引ごとの個別の設定が必要なくなり、索引にアクセスした回数などが確認できるようになっています。
DBA_INDEX_USAGE ビューによる索引の監視について実際の出力や見方をご紹介いたします。

使用状況を確認するビュー

今回使用するビューの項目は以下になります。

V$INDEX_USAGE_INFO ビュー

索引の使用状況の統計値を確認することができます。索引の使用状況統計は15分ごとにフラッシュし DBA_INDEX_USAGE に値が反映されます。このビューではフラッシュが行われた時刻や追跡されている索引の数などが把握できますが索引名などは確認できません。
特定の索引の使用状況を追跡するには、後述の DBA_INDEX_USAGE ビューを参照します。

DBA_INDEX_USAGE ビュー

各索引の使用状況を確認するビューで V$INDEX_USAGE_INFO ビューのフラッシュ後に情報が格納されます。

索引の使用状況の確認

実際に設定を行い、索引がどのように追跡と監視がされているか見ていきます。

1. ビューの確認

V$INDEX_USAGE_INFO の確認

ACTIVE_ELEM_COUNT が0のため LAST_FLUSH_TIME 以降にまだ追跡されている索引がないことを示しています。

    SQL> select INDEX_STATS_ENABLED, INDEX_STATS_COLLECTION_TYPE,
          ACTIVE_ELEM_COUNT, LAST_FLUSH_TIME from V$INDEX_USAGE_INFO;
     INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT     LAST_FLUSH_TIME
     --------------------------- -----------------     ---------------------
                               0                 0<★ 18-06-14="" 19:26:08.748="">

2. 索引を使用する SELECT 文の発行とビューの確認

SELECT 文の実行

TEST_TABLE 表に存在する TEST_INDEX1 索引と TEST_INDEX2 索引の使用状況を確認するため、以下のような SELECT 文をそれぞれ①番を4回、②番を1回実行します。

① select /*+ index(TEST_TABLE TEST_INDEX1) */ * from TEST_TABLE where COL1 = 1;
② select /*+ index(TEST_TABLE TEST_INDEX2) */ * from TEST_TABLE where COL2 = 2;

V$INDEX_USAGE_INFO の確認

2つの索引を使用する SELECT 文を実行したため、ACTIVE_ELEM_COUNT が2に変わっていることが確認できます。

    SQL> select INDEX_STATS_ENABLED, INDEX_STATS_COLLECTION_TYPE,
          ACTIVE_ELEM_COUNT, LAST_FLUSH_TIME from V$INDEX_USAGE_INFO;

     INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT     LAST_FLUSH_TIME
     --------------------------- -----------------     ---------------------
                               0                 2<★ 18-06-14="" 19:26:08.748="">

DBA_INDEX_USAGE の確認

ここでは V$INDEX_USAGE_INFO がフラッシュされていないため DBA_INDEX_USAGE に情報が反映されていません。

    SQL> select NAME, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED
          from DBA_INDEX_USAGE where NAME like 'TEST_INDEX%';

     レコードが選択されませんでした。<★>

3. 反映を確認

2で確認した V$INDEX_USAGE_INFO.LAST_FLUSH_TIME の15分後にビューを確認をします。

V$INDEX_USAGE_INFO の確認

V$INDEX_USAGE_INFO.LAST_FLUSH_TIME の時間の更新が確認でき、情報がフラッシュしていることが確認できます。

    SQL> select INDEX_STATS_ENABLED, INDEX_STATS_COLLECTION_TYPE,
          ACTIVE_ELEM_COUNT,LAST_FLUSH_TIME from V$INDEX_USAGE_INFO;

     INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT LAST_FLUSH_TIME
     --------------------------- ----------------- ---------------------
                               0                 0 18-06-14 19:41:11.289 <★>

DBA_INDEX_USAGE の確認

各索引の情報が反映され、SELECT文の実行回数分のアクセス数と索引が最後に使用された期間を含むフラッシュ時間が格納されていることが確認できます。

    SQL> select NAME, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, LAST_USED
          from DBA_INDEX_USAGE where NAME like 'TEST_INDEX%';

     NAME         TOTAL_ACCESS_COUNT TOTAL_EXEC_COUNT LAST_USED
     ------------ ------------------ ---------------- -------------------
     TEST_INDEX1                   4                4 2018-06-14 19:41:11 <★ test_index2="" 1="" 1="" 2018-06-14="" 19:41:11=""><★>

※ 一連の出力結果は索引の使用状況を正確に監視結果を取得するために隠しパラメータ "_iut_stat_collection_type" を SAMPLED (デフォルト) から ALL へ変更しています。
ALL に変更することによって、より正確に使用状況を収集するようになるためパフォーマンスへ影響を及ぼす可能性があります。そのため、索引の監視期間のみ ALL を設定することをお勧めします。
隠しパラメータとなりますのでご利用の際は十分なご確認を頂いた上でご利用ください。
設定による影響の確認などご要望であればサポートセンターまでお問い合わせください。

補足

2の実行前に「alter index TEST_INDEX1 monitoring usage;」を実行し従来の方法で監視情報を取得した結果は以下のようになります。

    SQL> select INDEX_NAME, USED, START_MONITORING from DBA_OBJECT_USAGE
     where INDEX_NAME like 'TEST_INDEX%';

     INDEX_NAME  USED   START_MONITORING
     ----------- ------ -------------------
     TEST_INDEX1 YES    06/14/2018 19:26:37

使用されたのは確認できますが、個々の索引で設定を行うため設定されなかったTEST_INDEX2 索引の情報は取得されず、実行回数は確認できません。

まとめ

使用されていない索引が存在している場合、削除することで DML の不要なオーバヘッドを軽減することが期待できます。上記ご紹介の通り、従来の方法と比較してより多くの情報が取得され、確認が簡単になっていますので、確認の際は使用されてみてはいかがでしょうか。

(オラクル事業部 サポート・サービス担当 加藤)

• Oracle関連サービス