はじめに

Automatic Indexing は、Oracle Autonomous AI Database を支える代表的な機能のひとつです。(下記資料の左下)
OCIチュートリアルを参考にしながら、ディクショナリを追加でいくつか参照して挙動やアウトプットを確認します。

参照するディクショナリ

チュートリアルの実施前に、参照するディクショナリを整理します。

dba_autotask_schedule_control

Automatic Indexing は、Auto STS (Auto SQL Tuning Set / 自動SQLチューニング・セット) をインプットにインデックスを作成します。
Auto STS は 15分間隔で稼働する Auto STS Capture Task というメンテナンスタスクで収集されますが、このタスクの実行状況などが dba_autotask_schedule_control で確認可能です。
Auto STS は Automatic Indexing 以外でも使用されるため (e.g. Automatic SQL Plan Management)、Automatic Indexing を無効にしていても Auto STS Capture Task は稼働しています。

レイアウト

dba_sqlset_statements

STS の内容が確認可能です。
sqlset_name列が SYS_AUTO_STS のものが、Auto STS Capture Task で収集された SQL です。

レイアウト

dba_auto_index_executions

Automatic Indexing の実行状況が確認可能です。

(マニュアルには記載なし…)

dba_indexes

AUTO列が YES のものが Automatic Indexing で作成されたインデックスです。

レイアウト

やってTRY

テーブル作成

チュートリアルに記載の通り、テーブルを作成しオプティマイザ統計を収集します。

SQL> create table AUTO_INDEX_TEST_TABLE as
  2  select 1                sum_col,
  3      rownum              f1,
  4      mod(rownum,100000)  f2,
  5      mod(rownum,1000000) f3,
  6      mod(rownum,500000)  f4,
  7      mod(rownum,200000)  f5,
  8      mod(rownum,100000)  f6,
  9      mod(rownum,10000)   f7,
 10      mod(rownum,5000)    f8,
 11      mod(rownum,1000)    f9,
 12      mod(rownum,100)     f10,
 13      pad
 14  from   (select dbms_random.string('u',1000) pad from dual connect by level<=100) a,
 15*     (select 1 c from dual connect by level<=100000) b;

Table AUTO_INDEX_TEST_TABLE created.

SQL>
SQL> -- 統計情報は上記の表の作成時に生成されますが、統計情報を確実に最新のもの                                                                                                               にするため、以下を実行します。。
SQL> -- 自動索引は、統計情報が古くなった表に対する新しい索引を考慮しません。
SQL>
SQL> exec dbms_stats.gather_table_stats('ADMIN','AUTO_INDEX_TEST_TABLE')

PL/SQL procedure successfully completed.

SQL>
SQL> info AUTO_INDEX_TEST_TABLE
TABLE: AUTO_INDEX_TEST_TABLE
         LAST ANALYZED:2026-02-20 01:58:19.0
         ROWS         :10000000
         SAMPLE SIZE  :10000000
         INMEMORY     :DISABLED
         COMMENTS     :

Columns
NAME         DATA TYPE              NULL  DEFAULT    COMMENTS
 SUM_COL     NUMBER                 Yes
 F1          NUMBER                 Yes
 F2          NUMBER                 Yes
 F3          NUMBER                 Yes
 F4          NUMBER                 Yes
 F5          NUMBER                 Yes
 F6          NUMBER                 Yes
 F7          NUMBER                 Yes
 F8          NUMBER                 Yes
 F9          NUMBER                 Yes
 F10         NUMBER                 Yes
 PAD         VARCHAR2(32767 BYTE)   Yes

Automatic Indexing の有効化

Automatic Indexing はデフォルト無効のため、有効化します。

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                    OFF  ★無効
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', 'IMPLEMENT')

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                    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>

ワークロード実行

次の Auto STS Capture Task が完了したことを dba_autotask_schedule_control から確認し、ワークロードを実行します。
teraterm を新たに 2つ起動し、ワークロードを追加で実行します。(見分けられるよう、SQL内のコメントを少し変更しています)

SQL> r
  1  SELECT
  2      task_name,
  3      status,
  4      enabled,
  5      interval,
  6      last_schedule_time,
  7      SYSTIMESTAMP - last_schedule_time AS ago
  8  FROM
  9      dba_autotask_schedule_control
 10  WHERE
 11      dbid = SYS_CONTEXT('USERENV', 'CON_DBID')
 12*     AND task_name LIKE '%STS Cap%';

TASK_NAME                STATUS       ENABLED       INTERVAL LAST_SCHEDULE_TIME                     AGO                    
________________________ ____________ __________ ___________ ______________________________________ ______________________ 
Auto STS Capture Task    SUCCEEDED    TRUE               900 20-FEB-26 02.15.12.787000000 AM GMT    +00 00:00:02.718812    


SQL> set serveroutput on
    qry varchar2(1000);
    talias  varchar2(30);
    n1  number;
    n2  number;
begin
    for i in 1..10
    loop
SQL> declare
  2      qry varchar2(1000);
  3      talias  varchar2(30);
  4      n1  number;
  5      n2  number;
  6  begin
  7      for i in 1..10
  8      loop
  9          talias := 'T'||to_char(sysdate,'YYYYMMDDHH24MISS');
 10          qry := 'select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f'||i||' = 1 group by f10';
 11          execute immediate qry into n1,n2;
 12          execute immediate qry into n1,n2;
 13          execute immediate qry into n1,n2;
 14          dbms_output.put_line('QRY = '||qry);
 15          dbms_output.put_line('SUM = '||n2);
 16      end loop;
 17      qry := 'select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f1 = 1 and f2 = 1 group by f10';
 18      execute immediate qry into n1,n2;
 19      execute immediate qry into n1,n2;
 20      execute immediate qry into n1,n2;
 21      dbms_output.put_line('QRY = '||qry);
 22      qry := 'select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f1 = 1 and f2 = 1 group by f10';
 23      execute immediate qry into n1,n2;
 24      execute immediate qry into n1,n2;
 25      execute immediate qry into n1,n2;
 26      dbms_output.put_line('QRY = '||qry);
 27  end;
 28* /
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021522 where f1 = 1 group by f10
SUM = 1
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021637 where f2 = 1 group by f10
SUM = 100
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021730 where f3 = 1 group by f10
SUM = 10
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021828 where f4 = 1 group by f10
SUM = 20
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021925 where f5 = 1 group by f10
SUM = 50
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022018 where f6 = 1 group by f10
SUM = 100
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022107 where f7 = 1 group by f10
SUM = 1000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022156 where f8 = 1 group by f10
SUM = 2000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022235 where f9 = 1 group by f10
SUM = 10000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022329 where f10 = 1 group by f10
SUM = 100000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022329 where f1 = 1 and f2 = 1 group by f10
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022329 where f1 = 1 and f2 = 1 group by f10


PL/SQL procedure successfully completed.

SQL> set serveroutput off
SQL>
SQL> SELECT
  2      task_name,
  3      status,
  4      enabled,
  5      interval,
  6      last_schedule_time,
  7      SYSTIMESTAMP - last_schedule_time AS ago
  8  FROM
  9      dba_autotask_schedule_control
 10  WHERE
 11      dbid = SYS_CONTEXT('USERENV', 'CON_DBID')
 12*     AND task_name LIKE '%STS Cap%';

TASK_NAME                STATUS       ENABLED       INTERVAL LAST_SCHEDULE_TIME                     AGO
________________________ ____________ __________ ___________ ______________________________________ ______________________
Auto STS Capture Task    SUCCEEDED    TRUE               900 20-FEB-26 02.15.12.787000000 AM GMT    +00 00:12:24.088687

追加1

SQL> set serveroutput on
    talias  varchar2(30);
    n1  number;
    n2  number;
begin
    for i in 1..10
    loop
        talias := 'T'||to_char(sysdate,'YYYYMMDDHH24MISS');
        qry := 'select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f'||i||' = 1 group by f10';
        execute immediate qry into n1,n2;
SQL> declare
  2      qry varchar2(1000);
  3      talias  varchar2(30);
  4      n1  number;
  5      n2  number;
  6  begin
  7      for i in 1..10
  8      loop
  9          talias := 'T'||to_char(sysdate,'YYYYMMDDHH24MISS');
 10          qry := 'select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f'||i||' = 1 group by f10';
 11          execute immediate qry into n1,n2;
 12          execute immediate qry into n1,n2;
 13          execute immediate qry into n1,n2;
 14          dbms_output.put_line('QRY = '||qry);
 15          dbms_output.put_line('SUM = '||n2);
 16      end loop;
 17      qry := 'select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f1 = 1 and f2 = 1 group by f10';
 18      execute immediate qry into n1,n2;
 19      execute immediate qry into n1,n2;
 20      execute immediate qry into n1,n2;
 21      dbms_output.put_line('QRY = '||qry);
 22      qry := 'select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f1 = 1 and f2 = 1 group by f10';
 23      execute immediate qry into n1,n2;
 24      execute immediate qry into n1,n2;
 25      execute immediate qry into n1,n2;
 26      dbms_output.put_line('QRY = '||qry);
 27  end;
 28* /
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021540 where f1 = 1 group by f10
SUM = 1
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021711 where f2 = 1 group by f10
SUM = 100
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021755 where f3 = 1 group by f10
SUM = 10
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021842 where f4 = 1 group by f10
SUM = 20
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021936 where f5 = 1 group by f10
SUM = 50
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022029 where f6 = 1 group by f10
SUM = 100
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022116 where f7 = 1 group by f10
SUM = 1000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022159 where f8 = 1 group by f10
SUM = 2000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022255 where f9 = 1 group by f10
SUM = 10000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022359 where f10 = 1 group by f10
SUM = 100000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022359 where f1 = 1 and f2 = 1 group by f10
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022359 where f1 = 1 and f2 = 1 group by f10


PL/SQL procedure successfully completed.

SQL> set serveroutput off
SQL>

追加2

SQL> set serveroutput on
    n1  number;
    n2  number;
begin
    for i in 1..10
    loop
        talias := 'T'||to_char(sysdate,'YYYYMMDDHH24MISS');
        qry := 'select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f'||i||' = 1 group by f10';
        execute immediate qry into n1,n2;
        execute immediate qry into n1,n2;
SQL> declare
  2      qry varchar2(1000);
  3      talias  varchar2(30);
  4      n1  number;
  5      n2  number;
  6  begin
  7      for i in 1..10
  8      loop
  9          talias := 'T'||to_char(sysdate,'YYYYMMDDHH24MISS');
 10          qry := 'select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f'||i||' = 1 group by f10';
 11          execute immediate qry into n1,n2;
 12          execute immediate qry into n1,n2;
 13          execute immediate qry into n1,n2;
 14          dbms_output.put_line('QRY = '||qry);
 15          dbms_output.put_line('SUM = '||n2);
 16      end loop;
 17      qry := 'select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f1 = 1 and f2 = 1 group by f10';
 18      execute immediate qry into n1,n2;
 19      execute immediate qry into n1,n2;
 20      execute immediate qry into n1,n2;
 21      dbms_output.put_line('QRY = '||qry);
 22      qry := 'select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE '||talias||' where f1 = 1 and f2 = 1 group by f10';
 23      execute immediate qry into n1,n2;
 24      execute immediate qry into n1,n2;
 25      execute immediate qry into n1,n2;
 26      dbms_output.put_line('QRY = '||qry);
 27  end;
 28* /
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021557 where f1 = 1 group by f10
SUM = 1
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021721 where f2 = 1 group by f10
SUM = 100
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021805 where f3 = 1 group by f10
SUM = 10
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021903 where f4 = 1 group by f10
SUM = 20
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220021958 where f5 = 1 group by f10
SUM = 50
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022048 where f6 = 1 group by f10
SUM = 100
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022135 where f7 = 1 group by f10
SUM = 1000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022214 where f8 = 1 group by f10
SUM = 2000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022255 where f9 = 1 group by f10
SUM = 10000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022350 where f10 = 1 group by f10
SUM = 100000
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022350 where f1 = 1 and f2 = 1 group by f10
QRY = select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE T20260220022350 where f1 = 1 and f2 = 1 group by f10


PL/SQL procedure successfully completed.

SQL> set serveroutput off
SQL>

STS確認

Auto STS Capture Task が完了したことを確認し dba_sqlset_statements を参照すると、実行したワークロード (SQL ID で数えると 11×3) がキャプチャされていることがわかります。

SQL> SELECT
  2      task_name,
  3      status,
  4      enabled,
  5      interval,
  6      last_schedule_time,
  7      SYSTIMESTAMP - last_schedule_time AS ago
  8  FROM
  9      dba_autotask_schedule_control
 10  WHERE
 11      dbid = SYS_CONTEXT('USERENV', 'CON_DBID')
 12*     AND task_name LIKE '%STS Cap%';

TASK_NAME                STATUS       ENABLED       INTERVAL LAST_SCHEDULE_TIME                     AGO
________________________ ____________ __________ ___________ ______________________________________ ______________________
Auto STS Capture Task    SUCCEEDED    TRUE               900 20-FEB-26 02.15.12.787000000 AM GMT    +00 00:14:19.038414

SQL> select sql_text
  2  from   dba_sqlset_statements
  3  where  sql_text like '%AUTO_INDEX_TEST_QUERY%'
  4* and    sqlset_name = 'SYS_AUTO_STS';

no rows selected  ★この時点では STS なし

SQL> SELECT
  2      task_name,
  3      status,
  4      enabled,
  5      interval,
  6      last_schedule_time,
  7      SYSTIMESTAMP - last_schedule_time AS ago
  8  FROM
  9      dba_autotask_schedule_control
 10  WHERE
 11      dbid = SYS_CONTEXT('USERENV', 'CON_DBID')
 12*     AND task_name LIKE '%STS Cap%';

TASK_NAME                STATUS       ENABLED       INTERVAL LAST_SCHEDULE_TIME                     AGO
________________________ ____________ __________ ___________ ______________________________________ ______________________
Auto STS Capture Task    SUCCEEDED    TRUE               900 20-FEB-26 02.30.14.075000000 AM GMT    +00 00:00:10.318705

SQL> select sql_id, sql_text
  2  from   dba_sqlset_statements
  3  where  sql_text like '%AUTO_INDEX_TEST_QUERY%'
  4* and    sqlset_name = 'SYS_AUTO_STS';

SQL_ID           SQL_TEXT
________________ ___________________________________________________________________________________
byzdkx5gcstg6    select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
0dffbp38hac8h    select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
17fy9n35wdj44    select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
288q6bgyanv49    select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
fz2r32a1rna8k    select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
ftzgvmpg4d55s    select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
9885uw4htng77    select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
gv49g9532avrb    select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
0zyyn0774x2pg    select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
675b3t80xpu03    select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
ca9vcyxud110m    select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
0dbkqcvg23657    select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
74yzmcq834wk3    select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
d8gw76mg0kvmn    select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
7kjnwvrf2f1u3    select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
ck5cz85sdqxyj    select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
329kp8zb3029t    select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
4t78rc0u2zqhy    select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
62zkm7x69vs2a    select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
1zkvhupxdpnvu    select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
8678jku44x07v    select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
bnh1djm1m8g1s    select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
20c7vb7p1654y    select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
655gfxb9g340x    select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
fmaty480wms3n    select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
49j07y4qwyavq    select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
bvz6j75aaw5ts    select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
fpydhc8hfn12j    select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
14nzpsk7twdhz    select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
5syqswtnu5sv7    select sql_text
                 from   dba_sqlset_statements
                 where  sql_text like '%AUTO_INDEX
fy92qjtmd9rck    select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
1ad95mg3wnn78    select /* AUTO_INDEX_TEST_QUERY_SESS1 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
4g6ymbuckcktc    select /* AUTO_INDEX_TEST_QUERY_SESS3 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T
56bcsamza34fv    select /* AUTO_INDEX_TEST_QUERY_SESS2 */ f10,sum(sum_col) from AUTO_INDEX_TEST_T

34 rows selected.

SQL>

Automatic Indexing の実行状況確認

dba_auto_index_executions を参照すると、Automatic Indexing は Auto STS Capture Task の 1分後に 10分程度稼働していました。
並行して dba_indexes を 1分おきに参照すると、Automatic Indexing の開始と同時に 10 のインデックスが UNUSABLE/INVISIBLE で作成され、徐々に UNUSABLE から VALID へ変更され、最後にまとめて INVISIBLE から VISIBLE へ変更されている状況が確認できました。

SQL> SELECT
  2      execution_name,
  3      TO_CHAR(execution_start, 'YYYY/MM/DD HH24:MI:SS') AS execution_start,
  4      TO_CHAR(execution_end,   'YYYY/MM/DD HH24:MI:SS') AS execution_end,
  5      status
  6  FROM
  7      dba_auto_index_executions
  8  ORDER BY
  9*     execution_start;

EXECUTION_NAME                EXECUTION_START        EXECUTION_END          STATUS
_____________________________ ______________________ ______________________ ____________
SYS_AI_2026-02-20/02:16:12    2026/02/20 02:16:18    2026/02/20 02:16:18    COMPLETED
SYS_AI_2026-02-20/02:31:14    2026/02/20 02:31:14    2026/02/20 02:43:53    COMPLETED

Running 24 of 999,999  @ 02:30:54.726 with a delay of 60s

Running 25 of 999,999  @ 02:31:54.736 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    

Running 26 of 999,999  @ 02:32:54.878 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    

Running 27 of 999,999  @ 02:33:55.185 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     VALID       INVISIBLE     ADVANCED LOW    

Running 28 of 999,999  @ 02:34:55.199 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     VALID       INVISIBLE     ADVANCED LOW    

Running 29 of 999,999  @ 02:35:55.402 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     VALID       INVISIBLE     ADVANCED LOW    

Running 30 of 999,999  @ 02:36:55.634 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     VALID       INVISIBLE     ADVANCED LOW    

Running 31 of 999,999  @ 02:37:55.788 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     VALID       INVISIBLE     ADVANCED LOW    

Running 32 of 999,999  @ 02:38:56.108 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     VALID       INVISIBLE     ADVANCED LOW    

Running 33 of 999,999  @ 02:39:56.290 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     VALID       INVISIBLE     ADVANCED LOW    

Running 34 of 999,999  @ 02:40:56.481 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     VALID       INVISIBLE     ADVANCED LOW    

Running 35 of 999,999  @ 02:41:56.642 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     VALID       INVISIBLE     ADVANCED LOW    

Running 36 of 999,999  @ 02:42:56.944 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS      VISIBILITY    COMPRESSION     
________ _______________________ _______ ___________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     UNUSABLE    INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     VALID       INVISIBLE     ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     VALID       INVISIBLE     ADVANCED LOW    

Running 37 of 999,999  @ 02:43:57.121 with a delay of 60s

OWNER    INDEX_NAME              AUTO    STATUS    VISIBILITY    COMPRESSION     
________ _______________________ _______ _________ _____________ _______________ 
ADMIN    SYS_AI_3vj4dhuq4n300    YES     VALID     VISIBLE       ADVANCED LOW    
ADMIN    SYS_AI_gdu3958w4jtrf    YES     VALID     VISIBLE       ADVANCED LOW    
ADMIN    SYS_AI_7tsh190vs7fnk    YES     VALID     VISIBLE       ADVANCED LOW    
ADMIN    SYS_AI_8asuvwcvrdmsc    YES     VALID     VISIBLE       ADVANCED LOW    
ADMIN    SYS_AI_cx646fvtr64u5    YES     VALID     VISIBLE       ADVANCED LOW    
ADMIN    SYS_AI_b9wjbw50d46mw    YES     VALID     VISIBLE       ADVANCED LOW    
ADMIN    SYS_AI_fb8yhtuk4m7p1    YES     VALID     VISIBLE       ADVANCED LOW    
ADMIN    SYS_AI_7ydrxqpm6693n    YES     VALID     VISIBLE       ADVANCED LOW    
ADMIN    SYS_AI_cb4ay0uh6ava9    YES     VALID     VISIBLE       ADVANCED LOW    
ADMIN    SYS_AI_3j13qx7f8hsa5    YES     VALID     VISIBLE       ADVANCED LOW

レポート出力

Automatic Indexing のレポートを出力します。
少々長いので割愛しますが、32 の SQL の検証結果が記載されていました。(検証されたのは 33)
キャプチャされた SQL (34) のうち記載されていなかったのは 5syqswtnu5sv7 と 1zkvhupxdpnvu で、5syqswtnu5sv7 はディクショナリを参照しているため検証から除外されたものと考えられます。
1zkvhupxdpnvu は検証で改善しなかったため記載されていないということになりますが、他のセッションから実行した同じ SQL は記載されているため、腑に落ちない所です

REPORT
____________________________________________________________________________________________________________
GENERAL INFORMATION
-------------------------------------------------------------------------------
 Activity start               : 18-FEB-2026 05:01:21
 Activity end                 : 20-FEB-2026 05:01:21
 Executions completed         : 11
 Executions interrupted       : 0
 Executions with fatal error  : 0
-------------------------------------------------------------------------------

SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
 Index candidates                              : 10
 Indexes created (visible / invisible)         : 10 (10 / 0)
 Space used (visible / invisible)              : 1.47 GB (1.47 GB / 0 B)
 Indexes dropped                               : 0
 SQL statements verified                       : 33  ★33 の SQL が検証された
 SQL statements improved (improvement factor)  : 32 (221.5x)  ★うち、32 の SQL が改善した
 SQL plan baselines created                    : 0
 Overall improvement factor                    : 28.8x
-------------------------------------------------------------------------------

SUMMARY (MANUAL INDEXES)
-------------------------------------------------------------------------------
 Unused indexes    : 0
 Space used        : 0 B
 Unusable indexes  : 0
-------------------------------------------------------------------------------

INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
*: invisible
-------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
| Owner | Table                 | Index                | Key   | Type   | Properties |
--------------------------------------------------------------------------------------
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_3j13qx7f8hsa5 | F8    | B-TREE | NONE       |
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_3vj4dhuq4n300 | F9    | B-TREE | NONE       |
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_7tsh190vs7fnk | F1,F2 | B-TREE | NONE       |
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_7ydrxqpm6693n | F6    | B-TREE | NONE       |
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_8asuvwcvrdmsc | F2    | B-TREE | NONE       |
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_b9wjbw50d46mw | F4    | B-TREE | NONE       |
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_cb4ay0uh6ava9 | F7    | B-TREE | NONE       |
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_cx646fvtr64u5 | F3    | B-TREE | NONE       |
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_fb8yhtuk4m7p1 | F5    | B-TREE | NONE       |
| ADMIN | AUTO_INDEX_TEST_TABLE | SYS_AI_gdu3958w4jtrf | F10   | B-TREE | NONE       |
--------------------------------------------------------------------------------------
-------------------------------------------------------------------------------

VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
 Parsing Schema Name  : ADMIN
 SQL ID               : 0dbkqcvg23657
 SQL Text             : select /* AUTO_INDEX_TEST_QUERY_SESS1 */
                      f10,sum(sum_col) from AUTO_INDEX_TEST_TABLE
                      T20260220022107 where f7 = 1 group by f10
 Improvement Factor   : 1658.62x

Execution Statistics:
-----------------------------
                    Original Plan                 Auto Index Plan
                    ----------------------------  ----------------------------
 Elapsed Time (s):  76555717                      34250
 CPU Time (s):      26904781                      28400
 Buffer Gets:       5277124                       1005
 Optimizer Cost:    251150                        1001
 Disk Reads:        5076719                       1005
 Direct Writes:     0                             0
 Rows Processed:    3                             1
 Executions:        3                             1


PLANS SECTION
---------------------------------------------------------------------------------------------

- Original
-----------------------------
 Plan Hash Value  : 772221241

-----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                  | Rows | Bytes | Cost   | Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                       |      |       | 251150 |          |
|   1 |   PX COORDINATOR           |                       |      |       |        |          |
|   2 |    PX SEND QC (RANDOM)     | :TQ10001              |  100 |  1000 | 251150 | 00:00:10 |
|   3 |     HASH GROUP BY          |                       |  100 |  1000 | 251150 | 00:00:10 |
|   4 |      PX RECEIVE            |                       |  100 |  1000 | 251150 | 00:00:10 |
|   5 |       PX SEND HASH         | :TQ10000              |  100 |  1000 | 251150 | 00:00:10 |
|   6 |        HASH GROUP BY       |                       |  100 |  1000 | 251150 | 00:00:10 |
|   7 |         PX BLOCK ITERATOR  |                       |  995 |  9950 | 251149 | 00:00:10 |
| * 8 |          TABLE ACCESS FULL | AUTO_INDEX_TEST_TABLE |  995 |  9950 | 251149 | 00:00:10 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 8 - access(:Z>=:Z AND :Z<=:Z)
* 8 - filter("F7"=1)


Notes
-----
- dop_reason = degree limit
- dop = 2
- px_in_memory_imc = no
- px_in_memory = no
- ads_system = yes
- Dynamic sampling used for this statement ( level = 11 )


- With Auto Indexes
-----------------------------
 Plan Hash Value  : 3710348590

---------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                  | Rows | Bytes | Cost | Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                       |  100 |  1000 | 1001 | 00:00:01 |
|   1 |   HASH GROUP BY                        |                       |  100 |  1000 | 1001 | 00:00:01 |
|   2 |    TABLE ACCESS BY INDEX ROWID BATCHED | AUTO_INDEX_TEST_TABLE |  995 |  9950 | 1000 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN                   | SYS_AI_cb4ay0uh6ava9  |  995 |       |    4 | 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("F7"=1)
(snip)

漠然とした不安がある機能ではありますが、対象とするスキーマを選択することもできるようなので、小さくはじめて範囲を広げていくのが良いかと思います

AUTO_INDEX_SCHEMA: 自動索引の使用を包含または除外するスキーマ。その値は大/小文字が区別され、ワイルドカードを使用できます。動作の制御には、allowパラメータを使用します。