19c新機能検証の第6回になります。
今回は自動索引について検証します。
この機能はExadataのみで使用可能で、検証に使用した環境は以下の通りです。
まず、機能の概要です。
・機能について
自動索引作成機能では、Oracleデータベース内の索引管理タスクが自動化される。
自動索引作成では、アプリケーション・ワークロードの変化に基づいてデータベース内の索引が自動的に作成、再作成および削除されるため、データベース・パフォーマンスが向上する。自動的に管理される索引は、自動索引と呼ばれる。
・処理概要
自動索引作成プロセスは、バックグラウンドで15分ごとに実行され、次の操作を実行する。
-自動索引候補の識別
自動索引候補は、SQL文での表の列の使用方法に基づいて識別される。
-自動索引候補に対する不可視の自動索引の作成
自動索引候補は、不可視の自動索引として作成される。つまり、これらの自動索引は、SQL文で使用できない。
-SQL文に対する不可視の自動索引の検証
不可視の自動索引は、SQL文に対して検証される。
これらの索引を使用することでSQL文のパフォーマンスが向上する場合、索引は、SQL文で使用できるように、可視索引として構成される。
これらの索引を使用してもSQL文のパフォーマンスが向上しない場合は、索引が使用禁止索引として構成され、SQL文がブラックリスト登録される。
使用禁止の索引は、後で自動索引作成プロセスによって削除される。ブラックリスト登録されたSQL文は、今後は自動索引の使用が許可されない。
以上、日本語の「データベース管理者ガイド」からの抜粋ですが、英語版管理者ガイドからの補足を付けておきます。
Ensure that table statistics are up to date. Tables with stale statistics are not considered for auto indexing.
統計情報が失効していると、自動索引は作成されないと言っていますね。
更に、「The Optimizer In Oracle Database 19c」からの補足 です。
Creates unusable and invisible index candidates (this is a data dictionary metadata change only)
不可視インデックスはデータディクショナリ上の、メタデータのみの変化だそうです。
マニュアルの記述は判りにくいですが、まずメタデータのみで実体を持たない不可視インデックスが作成され、検証の結果、効果ありと認められれば実体化し、使用されるようになる、という流れですね。
デフォルトでは無効なので、以下の手順で自動索引の作成が可能となります。
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
PL/SQL procedure successfully completed.
SQL> select PARAMETER_NAME,PARAMETER_VALUE from DBA_AUTO_INDEX_CONFIG;
PARAMETER_NAME PARAMETER_
-------------------------------------------------- ----------
AUTO_INDEX_COMPRESSION OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE IMPLEMENT
AUTO_INDEX_REPORT_RETENTION 31
AUTO_INDEX_RETENTION_FOR_AUTO 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 50
8 rows selected.
更にCOMPRESSION設定もする場合の手順は以下の通りです。
SQL> EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION', 'ON');
PL/SQL procedure successfully completed.
SQL> select PARAMETER_NAME,PARAMETER_VALUE from DBA_AUTO_INDEX_CONFIG;
PARAMETER_NAME PARAMETER_VALUE
----------------------------------------- ------------------------
AUTO_INDEX_COMPRESSION ON
準備が整ったので、検証して行きます。まずは単純なSQLで検証です。
Exadataで小さなテーブルだとインデクスを使用するまでもなく一瞬で結果が返ってくるので、大きなテーブルを用意しました。
SQL> select segment_name,bytes/1024/1024/1024 from user_segments;
SEGMENT_NA BYTES/1024/1024/1024
---------- --------------------
T1 6.61132813
T2 149.741211
T3 235.197266
T4 236.581055
自動索引が作成されるようにSQLを複数回、実行します。
SQL> update t1 set col6=systimestamp where col1 <= 100;
5795 rows updated.
Elapsed: 00:00:32.44
一定時間経過、4つの自動索引と、ジャーナル・テーブル*1が作成されていることが確認できました。
SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME from dba_indexes where TABLE_NAME in ('T1','T2','T3','T4');
OWNER INDEX_NAME TABLE_OWNE TABLE_NAME
---------- ------------------------------ ---------- ------------------------
TEST SYS_AI_5uqzu72x08xpm TEST T1
TEST SYS_AI_2zcj3rpa771w8 TEST T1
TEST SYS_AI_fjjrg7da66v50 TEST T2
TEST SYS_AI_8x10xb5syn3u0 TEST T3
SQL> select object_name,object_type,created from user_objects order by 3;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ----------------------- -------------------
T1 TABLE 2019/11/05 17:48:48
T2 TABLE 2019/11/05 17:57:21
T3 TABLE 2019/11/05 17:57:45
T4 TABLE 2019/11/05 17:59:19
SYS_AI_5uqzu72x08xpm INDEX 2019/11/06 14:53:01
SYS_AI_2zcj3rpa771w8 INDEX 2019/11/06 14:53:02
SYS_AI_fjjrg7da66v50 INDEX 2019/11/06 15:23:41
SYS_AI_8x10xb5syn3u0 INDEX 2019/11/06 15:55:02
SYS_AI_6dv62bpn4hsjt INDEX 2019/11/06 16:26:24
SYS_JOURNAL_73376 TABLE 2019/11/06 16:26:33
SYS_IOT_TOP_73378 INDEX 2019/11/06 16:26:33
ジャーナル・テーブルが作成されたという事はインデックスのオンライン・リビルドが行われたという事なのでAWRで確認してみました。
確かにREBUILDが実行されています。まだ不可視索引の状態で実体が無いのに、なぜリビルドが行われたのでしょう…？
しかも21時間もかかっています。
| Total | Per Execution | Percentage of Total | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SQL Id | Elapsed (s) | CPU (s) | IOWait (s) | Gets | Reads | Rows | Cluster (s) | Execs | Elapsed (s) | CPU (s) | IOWait (s) | Gets | Reads | Rows | Cluster (s) | DB time | DB CPU | IO Wait | Gets | Reads | Cluster | Execs | SQL Text |
| 5fxrps8qrps6b | 76,623.42 | 17.93 | 9.10 | 1,371,098 | 1,280,848 | 1 | 0.45 | 1 | 76,623.42 | 17.93 | 9.10 | 1,371,098.00 | 1,280,848.00 | 1.00 | 0.45 | 351.39 | 0.26 | 0.00 | 0.30 | 1.04 | 0.16 | 0.00 | DECLARE job BINARY_INTEGER := ... |
| f6j6vuum91fw8 | 76,623.39 | 17.89 | 9.10 | 1,370,904 | 1,280,848 | 0 | 0.45 | 1 | 76,623.39 | 17.89 | 9.10 | 1,370,904.00 | 1,280,848.00 | 0.00 | 0.45 | 351.39 | 0.26 | 0.00 | 0.30 | 1.04 | 0.16 | 0.00 | begin /*KAPI:task_proc*/ dbms_... |
| arxsxgxpcnh89 | 76,602.34 | 4.83 | 0.02 | 1,618 | 41 | 0 | 0.02 | 0 | 351.30 | 0.07 | 0.00 | 0.00 | 0.00 | 0.01 | 0.00 | /*AUTO_INDEX:ddl*/ALTER INDEX ... |
※一番上が呼び出し元プロシージャ、 2番目が呼び出されたプロシージャ、3番目がプロシージャ内で実行されたalter index rebuild onlineです。
まだ実体は無いので、セグメントは存在していません。実体の無いインデックスのリビルドに21時間かかるとは、どいう事でしょう…？
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name like 'SYS_AI%';
no rows selected
翌日、再度確認すると全てINVISIBLEのままでした。いろいろSQLを実行したものの、INVISIBLEのままとなっています。
SQL> select table_name,index_name,VISIBILITY from ind;
TABLE_NAME INDEX_NAME VISIBILIT
-------------------- ------------------------------ ---------
T1 SYS_AI_5uqzu72x08xpm INVISIBLE
T1 SYS_AI_2zcj3rpa771w8 INVISIBLE
T2 SYS_AI_fjjrg7da66v50 INVISIBLE
T3 SYS_AI_8x10xb5syn3u0 INVISIBLE
T4 SYS_AI_6dv62bpn4hsjt INVISIBLE
翌週になってから確認した所、一部のインデックスがVISIBLEになっていたのでSQLを実行しましたが、使用されませんでした。
SQL> select table_name,index_name,auto,visibility from ind;
TABLE_NAME INDEX_NAME AUT VISIBILIT
-------------------- ------------------------------ --- ---------
T1 SYS_AI_5uqzu72x08xpm YES VISIBLE
T1 SYS_AI_2zcj3rpa771w8 YES INVISIBLE
T2 SYS_AI_fjjrg7da66v50 YES INVISIBLE
T3 SYS_AI_8x10xb5syn3u0 YES VISIBLE
T4 SYS_AI_6dv62bpn4hsjt YES INVISIBLE
なので、ヒント句を付けて無理やり使わせてみました。
SQL> select /*+ index(t2 t3 t4) */ count(t4.col1) from t2,t3,t4 where t3.col1=t4.col1 and t3.col1=t2.col1 and t3.col1=1;
COUNT(T4.COL1)
--------------------
1,542,080,000
Elapsed: 00:00:50.52
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID cbsmwfaqg3783, child number 0
-------------------------------------
select /*+ index(t2 t3 t4) */ count(t4.col1) from t2,t3,t4 where t3.col1=t4.col1 and t3.col1=t2.col1 and t3.col1=1
Plan hash value: 2156460168
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13M(100)| |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | HASH JOIN | | 36M| 620M| 13M (1)| 00:08:58 |
|* 3 | TABLE ACCESS STORAGE FULL | T4 | 452 | 2712 | 8427K (1)| 00:05:30 |
|* 4 | HASH JOIN | | 79934 | 936K| 5333K (1)| 00:03:29 |
|* 5 | INDEX RANGE SCAN | SYS_AI_8x10xb5syn3u0 | 279 | 1674 | 4 (0)| 00:00:01 |
|* 6 | TABLE ACCESS STORAGE FULL| T2 | 286 | 1716 | 5333K (1)| 00:03:29 |
------------------------------------------------------------------------------------
（略）
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (2))
---------------------------------------------------------------------------
6 - SEL$1 / T2@SEL$1
U - index(t2 t3 t4)
U - index(t2 t3 t4)
こちらがヒント句無しの実行結果です。
SQL> select count(*) from t2,t3,t4 where t3.col1=t4.col1 and t3.col1=t2.col1 and t3.col1=1;
COUNT(*)
--------------------
1,542,080,000
Elapsed: 00:00:51.30
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 67aqtn5s1cy5z, child number 1
-------------------------------------
select count(*) from t2,t3,t4 where t3.col1=t4.col1 and t3.col1=t2.col1
and t3.col1=1
Plan hash value: 4137499944
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18M(100)| |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | HASH JOIN | | 36M| 620M| 18M (1)| 00:12:21 |
|* 3 | TABLE ACCESS STORAGE FULL | T4 | 452 | 2712 | 8427K (1)| 00:05:30 |
|* 4 | HASH JOIN | | 79934 | 936K| 10M (1)| 00:06:52 |
|* 5 | TABLE ACCESS STORAGE FULL| T3 | 279 | 1674 | 5205K (1)| 00:03:24 |
|* 6 | TABLE ACCESS STORAGE FULL| T2 | 286 | 1716 | 5333K (1)| 00:03:29 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
（略）
Note
-----
- SQL plan baseline SQL_PLAN_1t92mn4pnkp0b648a58a7 used for this statement
処理時間はほぼ同じ、つまりインデックスを使っても速くなりませんでした…。
T3に付与されたインデックスしかVISIBLEになっておらず、T2、T4はフルスキャンのままなので、効果が出なかったようです。
T1に付与されたインデックスもVISIBLEになっているので、T1に対してupdateを実行してみます。
SQL> update t1 set col6=systimestamp where col1 <= 100;
5795 rows updated.
Elapsed: 00:00:00.05
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 1bgnkw9mapyuv, child number 0
-------------------------------------
update t1 set col6=systimestamp where col1 <= 100
Plan hash value: 1249943959
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 15754 (100)| |
| 1 | UPDATE | T1 | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 16030 | 407K| 41 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_AI_5uqzu72x08xpm | 16030 | 407K| 41 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"<=100)
Note
-----
- dynamic statistics used: statistics for conventional DML
比較の為、no_indexヒントを付けて、インデックスなしで実行してみます。インデックスありが速くなっているのが判りますね。
SQL> update /*+ no_index(t1) */ t1 set col6=systimestamp where col1 <= 100;
5795 rows updated.
Elapsed: 00:00:32.44
ここまで余り良い結果は得られていないのですが、さらに意地の悪いケースでテストしてみます。
Nullの他に2種類しか値を持たないカラムをwhere句に指定してselect文を複数回実行し、インデックスが作成されるか確認してみました。
自動インデックスが作成され、visibleになっています。
SQL> select table_name,index_name,column_name from user_ind_columns where index_name in (select index_name from ind where visibility='VISIBLE') order by 1,3;
TABLE_NAME INDEX_NAME COLUMN_NAM
-------------------- ------------------------------ ----------
T1 SYS_AI_5uqzu72x08xpm COL1
T12 SYS_AI_907ywv2amp4da COL7
T3 SYS_AI_8x10xb5syn3u0 COL1
TEST1 SYS_AI_b54vrqkvwd39x COL1
TEST1 SYS_AI_3tndrwmyt3k0q COL2
TEST1 SYS_AI_cdq3cwcd8p1ac COL7
TEST2 SYS_AI_d8um6z8gc0gmd COL1
TEST2 SYS_AI_3fra6uk8v9jbk COL7
TEST3 SYS_AI_310mju3y45090 COL1
TEST3 SYS_AI_d1798g0553uxs COL2
TEST3 SYS_AI_7m0m5m59yu50p COL7
11 rows selected.
SQL> select col7,count(*) from t12 group by col7;
COL7 COUNT(*)
-------------------- --------------------
57,822,926
ZZZ 4,991
test 84,702,072
まずはそのまま実行します。
SQL> select count(*) from t12 where col7='test';
COUNT(*)
--------------------
84,702,072
Elapsed: 00:00:11.29
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID fhb44wvfdxt35, child number 0
-------------------------------------
select count(*) from t12 where col7='test'
Plan hash value: 894622912
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 51632 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX STORAGE FAST FULL SCAN| SYS_AI_907ywv2amp4da | 84M| 323M| 51632 (1)| 00:00:03 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL7"='test')
filter("COL7"='test')
自動インデックスが使用され、11.5分、かかりました。
次にヒント句を付けて、インデックスを使わないようにします。
SQL> select /*+ no_index(t12) */ count(*) from t12 where col7='test';
COUNT(*)
--------------------
84,702,072
Elapsed: 00:00:01.36
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID bxcngz8ag6jcx, child number 0
-------------------------------------
select /*+ no_index(t12) */ count(*) from t12 where col7='test'
Plan hash value: 4279859672
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 198K(100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS STORAGE FULL| T12 | 84M| 323M| 198K (1)| 00:00:08 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("COL7"='test')
filter("COL7"='test')
インデックスを使用しないと1.5分で終わります。
データにこれだけ偏りがあるのだから当然な気がしますが、自動索引の検証アルゴリズムではカーディナリティを気にしないのでしょうか…？
一応、補足しておきますがwhere col7='test'を何度か実行した結果、自動索引が作成されたのであって、where col7='ZZZ'を実行してOracleを"騙して"自動索引を作らせてからwhere col7='test'を実行して遅い結果を出したのではありません。
col7に自動索引を作成したのはまあ良しとして、where col7='test'の時にはインデックスを使わずフルスキャンを選択して欲しかったですね。
今回の検証で一番興味があったのは、Exadataでは下手なインデックスを使うより、テーブルフルスキャンでSmartScanを効かせた方が速いのですが、それを考慮して自動索引が作成・使用されるかどうかでした。
さすがにそこまでは考慮していないようです。
まあ、自動索引ではない普通のインデックスでも、あれば使われてしまってSmartScanより遅い結果になるので、それを避けるために不可視索引としてOLTPには使わせ、バッチには使わせないようにする設計にしたりするので、自動索引とは言え、そこは DBAがきちんと考慮・設計すべき点のようです。
余り良い結果が得られないままですが、次回に続きます。
*1:インデックスのオンライン・リビルド実行時に自動作成される一時テーブル
