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