はじめに
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パラメータを使用します。