Oracle 19c 自動索引の検証 - おらくるのいる生活

19c新機能検証の第10回になります。

今回は自動索引および19c新機能検証の最終回となります。

検証に使用した環境は以下の通りです。

Oracle RAC（2ノード） EE 19.5.0.0.0

Oracle Linux Server release 7.6

EXADATA DATABASE MACHINE X6-2

図解入門よくわかる最新 Oracleデータベースの基本と仕組み［第5版］ [ 水田巴 ]

価格: 2420 円
楽天で詳細を見る

今回は自動索引関連のプロシージャdbms_auto_index.DROP_SECONDARY_INDEXESについて検証してみます。

まず、マニュアルの記述です。

This procedure deletes all the indexes, except the ones used for constraints, from a schema or a table.

--PL/SQL Packages and Types Reference--

制約に使用されていない全てのインデックスを削除するプロシージャ、となっています。

どういう時に使うかと言うと、元々、二次インデックスが付与されている環境で自動索引の効果を試す時、一旦、既存の二次インデックスを全て削除するのに便利、という事のようです。

さっそく検証です。

SQL> create table t1(col1 number,col2 number,col3 date);

Table created.

SQL> create unique index idx1 on t1(col2);

Index created.

SQL> alter table t1 add constraint pk_t1 primary key (col1) using index;

Table altered.

SQL> create index idx2 on t1(col3);

Index created.

SQL> select table_name,index_name,UNIQUENESS from ind order by 1;

TABLE_NAME INDEX_NAME UNIQUENES
------------------------------ --------------------------------------- ---------
T1 IDX1 UNIQUE
T1 PK_T1 UNIQUE
T1 IDX2 NONUNIQUE

SQL> select TABLE_NAME,CONSTRAINT_NAME from user_constraints order by 1;

TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
T1 PK_T1

主キー(PK_T1)、ユニークインデックス(IDX1)、それ以外(IDX2)の3つを作りました。

準備が整ったのでdbms_auto_index.DROP_SECONDARY_INDEXESを実際に試してみます。

SQL> exec dbms_auto_index.drop_secondary_indexes('TEST','T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.56
SQL> select table_name,index_name,UNIQUENESS from ind order by 1;

TABLE_NAME INDEX_NAME UNIQUENES
------------------------------ ---------------------------------------- ---------
T1 IDX1 UNIQUE
T1 PK_T1 UNIQUE

主キー(PK_T1)は想定通り、残りましたが、ユニークインデックス(IDX1)も消えずに残っています。

どうやら制約に使用されていなくてもユニークインデックスであれば削除されないようなので、サポートに確認してみました。

Q.添付の通り、制約に使用されていないユニークインデックスが削除されませんが、これは仕様ですか？

A.弊社の実機で同じ動作できることと確認されております。
そのために、仕様動作としてご認識いただけますようにお願いいたします。

マニュアル・バグとして登録してくれたそうなので、その内、修正される事でしょう。

2020年7月28日現在では修正されていませんが。

ここまで自動索引の検証を行ってきましたが、エラーが発生して検証ができなくなりました。

SQL> select dbms_auto_index.report_activity() from dual;

（略）

ERRORS

---------------------------------------------------------------------------------------------

- ORA-13629: The task or object SYS_AUTO_INDEX_TASK is being used by another operation.

DBMS_AUTO_INDEX.REPORT_ACTIVITY()

--------------------------------------------------------------------------------

- ORA-13629: The task or object SYS_AUTO_INDEX_TASK is being used by another operation.

- ORA-13629: The task or object SYS_AUTO_INDEX_TASK is being used by another op

（略　丸一日ずっと出続けている）

SQL> SELECT task_id, task_name, status, execution_start, execution_end,ERROR_MESSAGE FROM sys.dba_advisor_tasks where task_name like '%AUTO_INDEX%';

TASK_ID TASK_NAME STATUS EXECUTION_START EXECUTION_END

---------- ------------------------------ ----------- ------------------- -------------------

ERROR_MESSAGE

---------------------------------------------------------------------------------------------

5 SYS_AUTO_INDEX_TASK FATAL ERROR 12-10-2019 16:54:42 12-10-2019 16:54:44

ORA-13629: The task or object is being used by another operation.

SYS_AUTO_INDEX_TASK、つまり自動索引の作成を検討し・効果を確認し・実体化する自動タスクがエラーで実行できなくなっている状態です。

実を言うと、このエラーは上記よりも前に出ていて、検証用に新しいDBを作って検証を続けていたのですが、2つ目のDBでも同じエラーが発生し、自動タスクが止まってしまいました…

以下、サポートに確認した結果です。

Call Stackの情報より以下の不具合を確認できておりました。Call Stackなどの内容はBugとマッチしております。

Bug 30142927 - AUTOMATIC INDEXING JOB FAILS REPEATEDLY AFTER MANUALLY DISABLED THEN RE-ENABLED AFTER DATABASE RESTORE FROM AUTO-BACKUP

上記不具合はデータベースのリスドアした後、一回自動索引機能を無効化/有効化した後事象が発生しました。しかしながら、開発部署より引き続き調査中の状態で原因や回避策は判明できていない状況です。

Bug 30142927 の調査進展を監視させていただきまして、進捗がありましたらご報告させていただきます。恐れ入りますが、お時間いただけますようお願い申し上げます。

SRを起票して7か月が経ちましたが、まだ進捗は無いそうです…

検証結果の総括は以下の通りです。

invisibleで作成されるまでにかなりの時間を要する事があり、そこからvisibleになるまで更に時間がかかる事がある。
性能向上するインデックス候補のみがvisibleになる筈だが、実際にはvisibleになったインデックスが使用されず、使用しても性能向上しないケースもある。
明らかにインデックス候補に不向きなカラムにインデックスが作成され、却って性能劣化するケースもある。
同じサイズ・同じ構成の複数テーブルに対して同じようなSQLを実行しても作成されるインデックスが異なる。
invisible/visibleの制御、削除はOracle任せで手動で変更できない。
※削除についてはｘ日以上、使用されていないインデックスを削除するという指定のみ可能。
使用可能かを検証する前に「rebuild」を行い、実体が作成される為、それなりのリソースと領域が使用される。
※対象テーブルにDMLを実行中だとrebuildは待機する
検証は15分おきの任意のタイミングで実行される為、業務処理ピーク時などには性能影響が懸念される。
調査中の不具合により、自動索引タスクが実行できなくなる事がある。
インデックスのrebuildに長時間かかり、その間、 SYS_AUTO_INDEX_TASKが実行できなくなる場合がある。
関数索引については、使用頻度の高そうなものについては自動作成されない。

本番稼働中に自動索引を有効にしておくと、rebuildの実行でパフォーマンス影響が懸念されるだけでなく、自動作成されたインデックスによって性能劣化する可能性もあるので、使用するのであればリリース前の試験フェーズで有効にし、実際のワークロードに合わせたテストを行って適切なインデックスが作成されるようにするのが安全と思われます。

ですが、実際にはそのようなテストが実施できる環境ばかりではなく、また、インデックスはあっても実行計画の変化により性能劣化するケースも多いので、安定したシステム運用を目指した環境には向かない気がします。

特にExadataの場合はインデックス・スキャンよりもスマート・スキャンの方が早いので、スマート・スキャンを使用したい環境に自動でインデックスが作られてしまうとスマート・スキャンが効かなくなり、遅くなってしまう可能性が低くありません。

ですが自動索引の有効・無効はスキーマ単位でしか指定できないので、OLTP用の小規模～中規模のテーブルと、DWH用の大規模テーブルはスキーマを分け、 OLTP用の小規模～中規模のテーブルのみ有効にするなどの運用が必要であろうと思われます。

…と言っても、Bug 30142927が解消しない限りは使えないのですが、修正されるのは次バージョンになってからですかね…

おらくるのいる生活

OracleのDBAとしての、障害対応やらパフォーマンス・チューニングやらの日々を綴っています

自動索引の検証(5)