非同期グローバル索引メンテナンスについて考える [SQL・DDL]

20221224_globalindex.jpg

 この記事は、JPOUG Advent Calendar 2022 24日目の記事です。昨日は、玉城 与清哉さんの「久しぶりにGrass Fishを触ってみたんご。」でした。

1.はじめに


 2019年にJPOUGでトランケートの話をしたときに、12cの非同期グローバル索引メンテナンスについて触れた(参考[1])。具体的には、クエリがメンテナンスが終わっていないグローバル索引を使ってどのように正しい結果を返すことができるかについて考察した(参考[2])。今回は、非同期に行われるグローバル索引メンテナンスについて考えてみたい。具体的には、以下の内容について述べる。

・非同期グローバル索引メンテナンスとは
・基本動作
・メンテナンスの内部挙動
・メンテナンスの性能チューニング

2.非同期グローバル索引メンテナンスとは


 非同期グローバル索引メンテナンスとは、パーティション・メンテナンス操作(DROP PARTITIONおよびTRUNCATE PARTITION等)を行った際、メタデータのみの索引メンテナンスを実行することにより、グローバル索引のメンテナンスを先送りする機能である(参考[3])。11gまでは、DDL実行時にグローバル索引のメンテナンス処理の完了を待つ必要があったが、12cからは当該機能により瞬時にコマンドは終了し、デフォルト2amに動作するメンテナンスジョブの中で非同期にメンテナンスが行われるようになった。
 実際のグローバル索引のメンテナンスは、以下の方法で実行できる。通常何も考えなければ(1)のジョブでメンテナンスが行われるが、手動で実行したい場合は(2)の方法で同じことが行える。本稿では、これらの挙動について深堀していく。

(1)SYS.PMO_DEFERRED_GIDX_MAINT_JOBジョブ
 ・・・索引クリーンアップの自動スケジューラジョブ(毎日2:00に実行)
(2)DBMS_SCHEDULER.RUN_JOB
 ・・・上記(1)を手動で起動
(3)DBMS_PART.CLEANUP_GIDXプロシージャ
 ・・・必要な索引すべて行う、または特定の索引だけ指定してクリーンアップを行う
(4)ALTER INDEX REBUILD [PARTITION]コマンド
 ・・・索引全体をリビルドする(従来の方法)
(5)ALTER INDEX [PARTITION] COALESCE CLEANUPコマンド
 ・・・索引のorphanedエントリをクリーンアップする

3.基本動作


 簡単なモデルを作成して上記挙動を実機確認してみよう。Oracleのバージョンは19.11である。emp表のhiredateで年月の12のレンジパーティションを用意し、100万件のデータを積む。hiredateは202201~202212までの日付をランダムに振り、パーティション毎にほぼ均等の件数になるようにする。連番である主キーempnoにグローバル索引(pk_emp)を付与する。また、1~10万までのランダム値の入れたdeptnoにもグローバル索引(idx1_emp)を付与する。
drop table emp cascade constraints;

create table emp(  
  empno    number(7,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(7,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(7,0),  
  constraint pk_emp primary key (empno)
)
partition by range (hiredate) (
 partition p202201 values less than (to_date('20220201','YYYYMMDD')),
 partition p202202 values less than (to_date('20220301','YYYYMMDD')),
 partition p202203 values less than (to_date('20220401','YYYYMMDD')),
 partition p202204 values less than (to_date('20220501','YYYYMMDD')),
 partition p202205 values less than (to_date('20220601','YYYYMMDD')),
 partition p202206 values less than (to_date('20220701','YYYYMMDD')),
 partition p202207 values less than (to_date('20220801','YYYYMMDD')),
 partition p202208 values less than (to_date('20220901','YYYYMMDD')),
 partition p202209 values less than (to_date('20221001','YYYYMMDD')),
 partition p202210 values less than (to_date('20221101','YYYYMMDD')),
 partition p202211 values less than (to_date('20221201','YYYYMMDD')),
 partition p202212 values less than (to_date('20230101','YYYYMMDD')),
 partition pmax values less than (MAXVALUE)
);
create index idx1_emp on emp(deptno);

insert into emp select
rownum,
dbms_random.string('u',10) ename,
dbms_random.string('u',9) job,
dbms_random.value(1,1000000) mgr,
to_date('20230101','YYYYMMDD') - dbms_random.value(1,365) hiredate,
dbms_random.value(3000,9999) sal,
dbms_random.value(3000,9999) comm,
dbms_random.value(1,100000) deptno
from dual connect by level <=1000000;
commit;

exec dbms_stats.gather_table_stats('SCOTT','EMP');

 上記の結果、以下のようなデータが準備できる。
SQL> select count(*) from emp;

   COUNT(*)
___________
    1000000

SQL> select table_name, partition_name, num_rows from user_tab_partitions where
table_name='EMP' order by 1,2;

TABLE_NAME    PARTITION_NAME       NUM_ROWS
_____________ _________________ ___________
EMP           P202201                 85460
EMP           P202202                 77154
EMP           P202203                 84523
EMP           P202204                 82145
EMP           P202205                 84870
EMP           P202206                 82519
EMP           P202207                 85198
EMP           P202208                 85426
EMP           P202209                 82506
EMP           P202210                 85048
EMP           P202211                 82403
EMP           P202212                 82748
EMP           PMAX                        0
13 rows selected.

 この状態で、グローバル索引の状態を確認する。ORPHAND_ENTRIESがNOとなっているので、索引の状態はきれいな状態(メンテナンスは不要)である。
select index_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries 
from user_indexes i, user_segments s where i.index_name = s.segment_name
and     i.table_name='EMP';

INDEX_NAME       NUM_ROWS    BLOCKS    LEAF_BLOCKS STATUS    ORPHANED_ENTRIES   
_____________ ___________ _________ ______________ _________ ___________________
IDX1_EMP          1000000      3968           3859 VALID     NO                 
PK_EMP            1000000      2560           2375 VALID     NO                 

 ここで、P202201のパーティションにトランケートを発行する。グローバル索引の状態を確認すると、以下のようにSTATUSはVALID(利用可能)な状態だが、ORPHANED_ENTRIES列がYESとなり、これらのグローバル索引は非同期メンテナンス対象となっていることがわかる。なお、トランケートはupdate indexesをつけないと索引がUNUSABLEになってしまうので注意する。
SQL> alter table emp truncate partition p202201 update indexes;
Table EMP truncated.
INDEX_NAME       NUM_ROWS    BLOCKS    LEAF_BLOCKS STATUS    ORPHANED_ENTRIES   
_____________ ___________ _________ ______________ _________ ___________________
IDX1_EMP          1000000      3968           3859 VALID     YES                
PK_EMP            1000000      2560           2375 VALID     YES                

 この状態で、これらの索引アクセスすると、以下のように、TBL$OR$IDX$PART$NUM関数のフィルタがプレディケートに付与される。Oracleはこのフィルタにより実体のない行に対するアクセスを排除している(このフィルタについては参考[1][2]参照されたい)。
select count(*) from emp where deptno <1000                                     
Plan hash value: 2417120451                                                     
-----------------------------------------------                                 
| Id  | Operation         | Name     | E-Rows |                                 
-----------------------------------------------                                 
|   0 | SELECT STATEMENT  |          |        |                                 
|   1 |  SORT AGGREGATE   |          |      1 |                                 
|*  2 |   INDEX RANGE SCAN| IDX1_EMP |   9990 |                                 
-----------------------------------------------                                 
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
   2 - access("DEPTNO"<1000)                                                    
       filter(TBL$OR$IDX$PART$NUM(,0,8,0,"EMP".ROWID)=1)★

 ここでsysで以下のように手動でグローバル索引メンテナンスジョブを実行する。
SQL> execute dbms_scheduler.run_job('SYS.PMO_DEFERRED_GIDX_MAINT_JOB',true)
PL/SQL procedure successfully completed.

 グローバル索引の状態を確認すると、ORPHANED_ENTRIES列がNOとなっており、非同期グローバル索引のメンテナンスが完了したことがわかる。トランケートしたのに、NUM_ROWSが変わっていないのは統計情報が古いからである。統計情報を取得し直すと、トランケートの結果が反映される。
INDEX_NAME       NUM_ROWS    BLOCKS    LEAF_BLOCKS STATUS    ORPHANED_ENTRIES   
_____________ ___________ _________ ______________ _________ ___________________
IDX1_EMP          1000000      3968           3859 VALID     NO                 
PK_EMP            1000000      2560           2375 VALID     NO                 

SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');

INDEX_NAME       NUM_ROWS    BLOCKS    LEAF_BLOCKS STATUS    ORPHANED_ENTRIES   
_____________ ___________ _________ ______________ _________ ___________________
IDX1_EMP           914540      3968           2566 VALID     NO                 
PK_EMP             914540      2560           2375 VALID     NO                 

 ここで特筆すべき目は、いずれの索引もメンテナンス前後でBLOCKSは変わっていないことである。つまり、非同期グローバル索引メンテナンスに索引のサイズを減らす効果は認められない。
 また、メンテナンス後にIDX1_EMPのLEAF_BLOCKSは減っているということから、非同期グローバル索引メンテナンスの実体は、リーフブロック数を減らすことであることがうかがえる。一方、PK_EMPの方はLEAF_BLOCKS数に変化がない。恐らくこれは索引の構成に依存した仕様の挙動と思われる。1リーフブロックの格納効率がPK_EMPの方が良い(PK_EMPが421件/BLKに対してIDX1_EMPが259件/BLK)ため、同じ件数を削除したとしても、解放できるリーフブロック数が少ないためと考えられる。PK_EMPの格納効率が良いのは、格納データによる違い(1~100万の連番であるのに対し、IDX1_EMPは1~10万のランダム値)によるものだろう。トランケートするパーティション数を増やしていけば、PK_EMPもLEAF_BLOCKSが減る挙動は確認できる。
 この状態で、これらの索引アクセスすると、まだTBL$OR$IDX$PART$NUM関数のフィルタが残っていたが、共有プールをフラッシュして再度クエリを実行すると、このフィルタが無くなった。恐らくハードパースが走ったタイミングでプレディケートが見直されるのだろう。

4.メンテナンスの内部挙動


 第一の疑問は、PMO_DEFERRED_GIDX_MAINT_JOBなるものが何をしているのか、ということである。AWRを見ると、以下のように、PMO_DEFERRED_GIDX_MAINT_JOBの実行にあわせて、dbms_part.cleanup_gidx_jobが実行されていることがわかる。処理時間から、PMO_DEFERRED_GIDX_MAINT_JOBジョブの内部で呼び出されていると考えられる。
        Elapsed                  Elapsed Time
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
             0.6              1          0.64   21.7   87.9     .1 50n3y8wjbndhp
Module: sqlplus@localhost.localdomain (TNS V1-V3)
   PDB: ORCL
BEGIN dbms_scheduler.run_job('SYS.PMO_DEFERRED_GIDX_MAINT_JOB',true); END; ★

             0.6              1          0.63   21.5   87.9     .1 6frynf9v1m655
Module: DBMS_SCHEDULER
   PDB: ORCL
call dbms_part.cleanup_gidx_job ( :0,:1 ) ★

 実際に実行されている処理を確認するため、以下のように10046トレース(SQLトレース)を取得する。
alter session set events '10046 trace name context forever, level 8';
execute dbms_scheduler.run_job('SYS.PMO_DEFERRED_GIDX_MAINT_JOB',true)
alter session set events '10046 trace name context off';

 結果を確認すると、2つのグローバル索引に対し、以下のALTER INDEX COALESCE CLEANUPが発行されていることがわかる。COALESCE CLEANUPはマニュアルに記載の通り、索引のorphanedエントリをクリーンアップする処理を行うもの、とあるので、メンテナンスジョブの実体はこのコマンドと考えられる。
ALTER INDEX "SCOTT"."PK_EMP" COALESCE CLEANUP PARALLEL 1
ALTER INDEX "SCOTT"."IDX1_EMP" COALESCE CLEANUP PARALLEL 1

 実際、以下のようにP202202をトランケートした後に上記コマンドを直接実行しても、グローバル索引のORPHANED_ENTRIESがNOになることが確認できた。
SQL> alter table emp truncate partition p202202 update indexes;
Table EMP truncated.
SQL> ALTER INDEX "SCOTT"."PK_EMP" COALESCE CLEANUP PARALLEL 1;
Index "SCOTT"."PK_EMP" altered.
SQL> ALTER INDEX "SCOTT"."IDX1_EMP" COALESCE CLEANUP PARALLEL 1;
Index "SCOTT"."IDX1_EMP" altered.
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');
PL/SQL procedure successfully completed.

SQL> select index_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
  2  from user_indexes i, user_segments s where i.index_name = s.segment_name
  3* and     i.table_name='EMP';

INDEX_NAME       NUM_ROWS    BLOCKS    LEAF_BLOCKS STATUS    ORPHANED_ENTRIES   
_____________ ___________ _________ ______________ _________ ___________________
IDX1_EMP           837386      3968           2545 VALID     NO                 
PK_EMP             837386      2560           2374 VALID     NO                 

 次の疑問は、このCOALESCE CLEANUPコマンドにより何が行われるのかである。それを確認するため、dbms_space.space_usageで、グローバル索引メンテナンス前後の状態を確認してみよう。検証用テーブルの状態は初期化し、(a)truncateの実行前、(b)truncate実行後(p202201)、(c)COALESCE CLEANUP実行後の3つの状態を比較する。dbms_spaceの確認は、参考[4]のスクリプトを利用させて頂いた。Freespaceはブロックの空き状況で、左の数字がブロック数、右がバイト数(1ブロック8Kbyte)を表す。
(a)(b)
                              :          idx1_emp                   :          pk_emp
Unformatted                   :           62 /          507,904     :          110 /          901,120
Freespace 1 (  0 -  25% free) :            0 /                0     :            0 /                0
Freespace 2 ( 25 -  50% free) :           53 /          434,176★② :           15 /          122,880
Freespace 3 ( 50 -  75% free) :            0 /                0     :            0 /                0
Freespace 4 ( 75 - 100% free) :            0 /                0     :            0 /                0
Full                          :        3,783 /       30,990,336★① :        2,387 /       19,554,304☆

(c)
                              :          idx1_emp                   :          pk_emp
Unformatted                   :           62 /          507,904     :          110 /          901,120
Freespace 1 (  0 -  25% free) :            0 /                0     :            0 /                0
Freespace 2 ( 25 -  50% free) :        1,237 /       10,133,504★② :           15 /          122,880
Freespace 3 ( 50 -  75% free) :            0 /                0     :            0 /                0
Freespace 4 ( 75 - 100% free) :            0 /                0     :            0 /                0
Full                          :        2,599 /       21,291,008★① :        2,387 /       19,554,304☆

 この結果を見ると、(a)と(b)の結果が一致していることから、トランケートの実行は全くグローバル索引のブロック数に影響を与えていない。また、(c)の結果から、idx1_empグローバル索引においては、メンテナンスによりFullで格納されているブロック数が減り(★①)、その変わりFreespace2がその分増えている(★②)。トータルブロック数の変化はないことから、COALESCE CLEANUPは、トランケートにより不要となった索引のリーフブロックの状態を空き状態に変更し再利用可能とするものの、未使用ブロックを解放し表領域に返却するようなことは行わない、ということである。そして、PK_EMPの方はメンテナンス後に☆部分に全く変化がないことは、リーフブロックの状態変化がないことが原因であることがわかる。
 次に、この状態で、(d)索引のリビルドを行って、ブロックの状態の変化を確認してみよう。結果、Fullのブロック数はほぼ同じだが、Freespace2に割り当てられていたブロックが解放されている(★③)ことがわかる。つまり、索引のリビルドにより索引トータルのブロックを減らすことができている。当然、この状態では、TBL$OR$IDX$PART$NUM関数のフィルタは発生しない。
alter index idx1_emp rebuild online;
alter index pk_emp rebuild online;

(d)
                              :          idx1_emp                   :          pk_emp
Unformatted                   :            0 /                0     :            0 /                0
Freespace 1 (  0 -  25% free) :            0 /                0     :            0 /                0
Freespace 2 ( 25 -  50% free) :            1 /            8,192★③ :            1 /            8,192★③
Freespace 3 ( 50 -  75% free) :            0 /                0     :            0 /                0
Freespace 4 ( 75 - 100% free) :            0 /                0     :            0 /                0
Full                          :        2,542 /       20,824,064     :        2,424 /       19,857,408

5.メンテナンスの性能チューニング


 ここまで、非同期グローバル索引メンテナンスの基本的な動作について述べた。ここでは、非同期グローバル索引メンテナンスのメンテナンス時間について考えてみたい。
 まず、非同期グローバル索引メンテナンスの処理時間は、メンテナンス対象が大きければ大きいほど処理時間がかかる傾向がある、ということご確認したい。そのため、トランケート対象のパーティションを1,2,4,8と増やして、メンテナンス時間(coalesce cleanup)を比較してみた。結果は以下の通り、処理時間はトランケートしたパーティション数(=~レコード数)に対して線形に増加することがわかる。 
202212_globalind_1.jpg

 上記のグラフで興味深いのは、どちらも索引メンテナンス時間の傾きは同じに見えることである。あたかも、COALESCE CLEANUPの処理は(1)切片にあたる処理時間(索引によって異なる)と(2)件数に応じて増加する処理時間(索引によって変わらない)、に分割されており、件数が十分に大きいときは(2)が支配的になるように見える。つまり、索引メンテナンス時間は、この傾きと、削除件数と、索引の数をパラメータとして、おおよそ推測できるのではないか、と考えられる。
 さて、次に、パラレル度のチューニングができるかを確認する。8パーティション削除のケースで、パラレル度を1、2、4にして処理時間を確認した。手元の環境はIntel i7-10510U(1.8GHz)コア4(論理プロセッサ8)で、OracleVMで4コアまで有効にしているので、CPU_COUNT=4となっている。キャッシュの効果を配慮し、各測定前にバッファキャッシュをフラッシュする。
ALTER INDEX "SCOTT"."PK_EMP" COALESCE CLEANUP PARALLEL <パラレル度>;
ALTER INDEX "SCOTT"."IDX1_EMP" COALESCE CLEANUP PARALLEL <パラレル度>;

202212_globalind_2.jpg

 上記結果から、パラレル度を上げることによる性能向上が確認できた。ただし、それ以上パラレル度を上げても線形には性能向上する訳ではなく、その効果はグローバル索引によって異なることがわかる。
 なお、実運用上は、個別の索引にCOALESCE CLEANUPを直接実行せず、メンテナンスジョブから実行することが想定される。PMO_DEFERRED_GIDX_MAINT_JOBに対して、以下のようにデフォルトのパラレル度を設定することができる(参考[5])。10046トレースを取得すると、きちんとPARALLEL 2が付与されていることが確認できる。
exec dbms_scheduler.set_job_argument_value('SYS.PMO_DEFERRED_GIDX_MAINT_JOB', 'parallel','2')
---10046トレースに現れたDDL
ALTER INDEX "SCOTT"."PK_EMP" COALESCE CLEANUP PARALLEL 2
ALTER INDEX "SCOTT"."IDX1_EMP" COALESCE CLEANUP PARALLEL 2

6.まとめ


 本稿では、非同期グローバル索引メンテナンス、特にそのメンテナンスジョブについて述べた。主に以下の知見が得られた。

・メンテナンスジョブ(PMO_DEFERRED_GIDX_MAINT_JOB)の実体はALTER INDEX COALESCE CLEANUPである
・メンテナンスにより、リーフブロック数は減るが、グローバル索引のサイズが減ることはない(領域を減らすにはリビルドが必要)
・メンテナンスすれば、TBL$OR$IDX$PART$NUM関数のフィルタはクエリから無くなるが、直ちに無くなる訳ではない(ハードパース等の契機が必要)
・メンテナンス時間は削除するパーティションの件数およびグローバル索引数に依存し、パラレル度を設定することで処理時間のチューニングが可能

 上記から、グローバル索引の肥大化を防ぐ意味では、基本的にメンテナンスジョブの実行はこまめに実施することが望ましく、多くの場合デフォルトの日次実行のままで良いだろう。メンテナンスジョブが長時間化する場合はパラレル度を上げることを検討し、それでも肥大化傾向が続くようであれば、索引個別に定期的なリビルド運用を検討する必要があるだろう。
 一度に大量のパーティションを削除する必要がある場合は、メンテナンスジョブが長時間化するリスクがある。1度に大量のパーティションを削除するより、こまめに少しずつ削除するアプローチの方が、メンテナンスがサービス影響を与えるリスクを減らすことができるだろう。
 
 明日は最終日ですね。

◇参考


[1]:トランケートについて知っておくべきこと, JPOUG講演スライド, 2019
[2]:トランケートの非同期グローバル索引メンテナンスについて
[3]:VLDBおよびパーティショニング・ガイド 19c, 4.3.2 パーティションを削除および切り捨てる非同期グローバル索引メンテナンス
[4]:dbms_spaceの確認用スクリプト, Orache Scratchpad, Jonathan Lewis
[5]:Bug 24515918 - PMO_DEFERRED_GIDX_MAINT_JOB causes TX contention on busy tables and runs serially (ドキュメントID 24515918.8)

以上

コメント(0) 

コメント 0

コメントを書く

お名前:
URL:
コメント:
画像認証:
下の画像に表示されている文字を入力してください。

※ブログオーナーが承認したコメントのみ表示されます。