Automatic indexingを試してみた

8440 ワード
oracle Autonomous oracle19c oracle テキストリンク

Automatic indexingを試してみましたので、ここに検証内容をメモとして残します。
Automatic indexingは新しい機能だからなのか、まだあまり情報がないので、少しでもお役に立てれば幸いです。

※本記事は検証段階のメモですし、もしコピー&ペーストでコマンドを試す場合は自己責任でお願いします。

機能について

・EXADATA限定の19ｃ新機能
・SQLを定期的に取得・監視して、自動的にindexの作成や削除の管理を行う
・index作成する索引候補は利用前に検証
・バックグラウンドで自動index作成プロセスを15分毎に実行

設定確認

・DBA_AUTO_INDEX_CONFIG ビューより確認できる
・AUTO_INDEX_MODEで、OFF、ON、REPORT ONLYが設定できる。
・デフォルトではAUTO_INDEX_MODEはOFFになっているので、ONで有効にすることで機能が使えるようになる。
・スキーマ単位でも設定が可能

一度に全スキーマを導入すると問題が起きた場合、影響範囲が広いのでスキーマ単位で導入した方がよさそうだと思いました。

SQL> SELECT * FROM DBA_AUTO_INDEX_CONFIG ;

結果
PARAMETER_NAME                                     PARAMETER_VALUE          LAST_MODIFIED           MODIFIED_BY
-------------------------------------------------- ------------------------ ----------------------- -------------
AUTO_INDEX_COMPRESSION                             OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE                                    OFF
AUTO_INDEX_REPORT_RETENTION                        31
AUTO_INDEX_RETENTION_FOR_AUTO                      373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET                            50

8行が選択されました。

自動索引の有効化

有効化、無効化、REPORT ONLYの設定方法については、次のように設定します。
また、表領域も指定することができます。
REPORT ONLYとは、マニュアルによると自動索引作成を有効にするが、不可視索引として作成することで、SQL文で使用できないようにする設定のことです。
なので、実際にはSQLは遅くならないということだと思います（SQL速度検証はできていませんが汗）
ただ、大量に（私のテスト環境では500個くらい）自動索引が作成はされてしまうので、dba_indexes等で見ると多すぎてビックリしました。

スキーマを指定せず、全スキーマを対象とする
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

レポートオンリーを指定する
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

自動索引の無効化
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

表領域の指定
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', '表領域名');

REPORT ONLYにすると以下★印が「REPORT ONLY」になりました。
表領域も指定すると以下●印のように「TBS_AUTO」になる事が確認できました。
※表領域は、事前に表領域「TBS_AUTO」を作成しました。

SQL> SELECT * FROM DBA_AUTO_INDEX_CONFIG ;

結果
PARAMETER_NAME                                     PARAMETER_VALUE          LAST_MODIFIED           MODIFIED_BY
-------------------------------------------------- ------------------------ ----------------------- -------------
AUTO_INDEX_COMPRESSION                             OFF
AUTO_INDEX_DEFAULT_TABLESPACE　　　　　　　　　　　　TBS_AUTO●
AUTO_INDEX_MODE                                    REPORT ONLY★
AUTO_INDEX_REPORT_RETENTION                        31
AUTO_INDEX_RETENTION_FOR_AUTO                      373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET                            50

8行が選択されました。

実行履歴の確認

自動索引の実行履歴は、次のように確認することができます。
約15分に1回動いていることが分かります。

SELECT
execution_name,
TO_CHAR(execution_start,'YYYY/MM/DD HH24:MI:SS') AS execution_start,
TO_CHAR(execution_end,'YYYY/MM/DD HH24:MI:SS') AS execution_end ,
status
FROM DBA_AUTO_INDEX_EXECUTIONS ORDER BY execution_start desc;

結果
execution_name                 execution_start        execution_end           status
------------------------------ ---------------------- ----------------------- -------------
SYS_AI_2020-11-16/14:41:23     2020/11/16 14:41:23     2020/11/16 14:41:40    COMPLETED
SYS_AI_2020-11-16/14:57:08     2020/11/16 14:57:08     2020/11/16 14:58:27    COMPLETED
SYS_AI_2020-11-16/15:12:25     2020/11/16 15:12:25     2020/11/16 15:12:37    COMPLETED
SYS_AI_2020-11-16/15:28:09     2020/11/16 15:28:09     2020/11/16 15:28:12    COMPLETED
SYS_AI_2020-11-16/15:43:25     2020/11/16 15:43:25     2020/11/16 15:43:36    COMPLETED

実行結果の確認方法

select DBMS_AUTO_INDEX.REPORT_ACTIVITY(
TO_TIMESTAMP('2021/01/04 12:00:00.00'),
TO_TIMESTAMP('2021/01/05 16:00:00.00'),
'TEXT' ,'ALL' , 'ALL'
) FROM DUAL;

以下、赤字部分が自動で作成された索引です。
VISIBLEのものがSQLでも使用される自動索引で、接頭語としてINDEX名に「SYS_AI_」が付きます。
レポートが出来たので、とりあえずautomatic indexingが動作できた事が確認できました！
動いただけですが嬉しいです。やったーーー

自動で作成されたINDEX一覧の確認

AUTO列がYESのものが自動で作成されたINDEXのことです。
自動で作成されたINDEXは、誤って削除してしまったので、結果は見せることができません。すみません。。

select owner,index_name,auto,status,visibility,compression from dba_indexes where owner in('スキーマ名')and AUTO='YES';

切り戻しについて

AUTO_INDEX_MODEをOFFにするとautomatic indexingを無効化にすることができます。
ただし、無効化前に作成された自動索引は削除されずにそのまま残ります。

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

また、未使用の自動索引については、保持期間を短縮することで全削除が可能です。
REPORT ONLYだけで設定を入れていれば、自動索引は未使用なので
この方法で自動で作成された全索引を削除することができます。

次の文は、未使用の自動索引の保存期間を10日間に設定しています。
10日前の未使用の自動索引は全て削除されることになります。
ただ、1つだけ削除したい場合は、この手順は使えません。。

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '10');

その他には、表領域は移動できるらしいので、次のように表領域を移動して表領域毎削除すれば、1つだけ削除が可能かもしれません。
残念ながら環境の問題でテスト続行が難しくなったので、今回は試すことができませんでした。。

削除する為に移動用の表領域作成
CREATE BIGFILE TABLESPACE ＜表領域名＞ ~;

自動indexの表領域を移動
alter index ＜自動index名＞ rebuild tablespace ＜表領域名＞;

表領域毎削除
DROP TABLESPACE ＜表領域名＞ INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

検証結果の総括

環境：テスト環境
・DBMS_AUTO_INDEX.CONFIGUREで有効/無効等の各種設定を行う
・有効化された自動索引は、その有効性や実行計画の差異をレポートで確認できる
・スキーマ単位で自動索引の使用を指定、除外できる
・REPORT ONLY（自動索引は作成するが、不可視としてSQL文では使用できない）設定ができる
・自動索引用に表領域を指定できる
・15分に1回自動監視、作成、削除、検証が実行される
・invisible/visibleへ変更したり、VALIDをUNUSABLEに変更したり
　自動索引の削除はOracle任せで手動で変更できない
・自動索引名に接頭語として”SYS_AI_”がつく
・自動索引は、UNUSABLE INVISIBLEとして作成される
　L検証後にパフォーマンスが向上するINDEXのみをVISIBLEにする
・仕様上は、性能向上するインデックス候補のみがvisibleになる（実際にはSQL速度検証していないので、要テスト）

・DBA_INDEXビューでAUTO列がYESのものが自動索引である

切り戻しについて

・自動索引を無効化にできるが、既存の自動索引は有効のまま（無効化しても、自動で作成された索引は削除できない）
・REPORT ONLYで設定した場合、未使用の自動索引は保持期間を短縮することで全削除が可能
　（保存期間より前の未使用の自動索引は削除される）
・自動索引を別表領域に再構築後、表領域毎DROP TABLESPASEで削除する（試していない為、要テスト）
・DBMS_AUTO_INDEX.DROP_AUTO_INDEXESでも削除できるかも？（試していない為、要テスト）

懸念事項

・性能向上するインデックス候補のみがvisibleになる筈だが、実際にはvisibleになったインデックスが使用されず、
　使用しても性能向上しない可能性あり（実際にはSQL速度検証していないので、要テスト）
・使用可能かを検証する前に「rebuild」を行い、実体が作成される為、それなりのリソースと領域が使用される
　→なので、表領域は自動索引用に作成運用した方がよいと思う
・automatic　indexingの検証は15分おきに実行される為、業務処理ピーク時には性能影響が懸念される

以上です。今回はここまで。
ここでは、automatic indexingが動作することが確認できました。
次回は、エラーで動作しなくなった件について記載したいと思います。。

参考資料

・Automatic Indexingのドキュメント
21.7 自動索引の管理
https://docs.oracle.com/cd/F19136_01/admin/managing-indexes.html#GUID-D1285CD5-95C0-4E74-8F26-A02018EA7999

・Automatic Indexingはいかに 運用/チューニングを変えるのか
https://www.oracle.com/jp/a/ocom/docs/jp-db-technight-content/30-1-automatic-indexing-print-final.pdf