はじめに
Automatic Indexing には IMPLEMENTモードと REPORT_ONLYモードがあります。
マニュアルではそれぞれ次のように説明されています。
IMPLEMENT: このモードでは、新しい自動索引が可視索引として作成されます。また、既存の不可視の自動索引も可視索引に設定されます。このモードでは、SQL文で自動索引が使用できるようになります。
REPORT ONLY: このモードでは、新しい自動索引が不可視の索引として作成され、SQL文では使用できなくなります。
前回の Automatic Indexing の投稿と同じ流れで REPORT_ONLYモードを試し、挙動を確認します。
やってTRY
モードを REPORT_ONLY に変更します。
SQL> select * from dba_auto_index_config;
PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY
__________________________________ __________________ __________________________________ ___________________
AUTO_INDEX_COMPRESSION ON 19-FEB-26 07.23.46.000000000 AM C##CLOUD$SERVICE
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE IMPLEMENT ★ 20-FEB-26 02.01.03.000000000 AM ADMIN
AUTO_INDEX_REPORT_RETENTION 7 19-FEB-26 07.23.46.000000000 AM C##CLOUD$SERVICE
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 100 19-FEB-26 07.23.46.000000000 AM C##CLOUD$SERVICE
8 rows selected.
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE', 'REPORT ONLY');
PL/SQL procedure successfully completed.
SQL> select * from dba_auto_index_config;
PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY
__________________________________ __________________ __________________________________ ___________________
AUTO_INDEX_COMPRESSION ON 19-FEB-26 07.23.46.000000000 AM C##CLOUD$SERVICE
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE REPORT ONLY ★ 24-FEB-26 03.00.30.000000000 AM ADMIN
AUTO_INDEX_REPORT_RETENTION 7 19-FEB-26 07.23.46.000000000 AM C##CLOUD$SERVICE
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 100 19-FEB-26 07.23.46.000000000 AM C##CLOUD$SERVICE
8 rows selected.
SQL>
前回 (IMPLEMENTモード) と同様、dba_indexes を定期的に参照しながらワークロードを実行すると下記のようになります。
Running 14 of 999,999 @ 03:39:29.571 with a delay of 60s
Running 15 of 999,999 @ 03:40:29.582 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 16 of 999,999 @ 03:41:29.742 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 17 of 999,999 @ 03:42:29.980 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 18 of 999,999 @ 03:43:30.275 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 19 of 999,999 @ 03:44:30.976 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 20 of 999,999 @ 03:45:31.520 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 21 of 999,999 @ 03:46:31.729 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 22 of 999,999 @ 03:47:31.902 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 23 of 999,999 @ 03:48:32.084 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 24 of 999,999 @ 03:49:32.472 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 25 of 999,999 @ 03:50:32.806 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES UNUSABLE INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 26 of 999,999 @ 03:51:33.198 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ ___________ _____________ ______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES UNUSABLE INVISIBLE ADVANCED LOW
Running 27 of 999,999 @ 03:52:33.792 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ _________ _____________ _______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES VALID INVISIBLE ADVANCED LOW
Running 28 of 999,999 @ 03:53:33.810 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ _________ _____________ _______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES VALID INVISIBLE ADVANCED LOW
Running 29 of 999,999 @ 03:54:33.826 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ _________ _____________ _______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES VALID INVISIBLE ADVANCED LOW
Running 30 of 999,999 @ 03:55:33.841 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ _________ _____________ _______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES VALID INVISIBLE ADVANCED LOW
(snip)
Running 60 of 999,999 @ 04:25:34.282 with a delay of 60s
OWNER INDEX_NAME AUTO STATUS VISIBILITY COMPRESSION
________ _______________________ _______ _________ _____________ _______________
ADMIN SYS_AI_1vv28hpgzfdah YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_26pfzfjyhq4j6 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_2hn1cxc9hsrp0 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_6840rbkd066h1 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_888jj1fhxxpkh YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_88c5kxwhgam08 YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_ccyv43k3wcdwp YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_d8ja2k3qszbku YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gc14f2wksb4fk YES VALID INVISIBLE ADVANCED LOW
ADMIN SYS_AI_gpshaaw6k7zax YES VALID INVISIBLE ADVANCED LOW
STATUS列が UNUSABLE から VALID に変わっていくのは前回 (IMPLEMENTモード) と同様ながら、VISIBILITY列は INVISIBLE のままであることがわかります。
UNUSABLE/VALID と VISIBLE/INVISIBLE
簡単に言うと、UNUSABLE/VALID はそのインデックスが利用可能か否か、VISIBLE/INVISIBLE は実行計画を生成するオプティマイザからそのインデックスが見えるか否か、を示しています。
つまり、STATUS列が VALID で VISIBILITY列が INVISIBLE の場合、「元となっているテーブルの更新はそのインデックスに反映されるが、元となっているテーブルの参照にそのインデックスは利用されない状態」となります。
参考：
'REPORT ONLY' と聞くとほとんど影響がなさそうな印象を持ちますが、インデックスを作成する分容量は食いますし、更新時のオーバーヘッドは発生しますので、この点を認識のうえ利用しましょう 