トランケートの非同期グローバル索引メンテナンスについて [SQL・DDL]
以前、「トランケートについて知っておくべきこと」の中でグローバル索引のメンテナンス(update global indexesオプション）について書いた。当時11gR2を使っていたため、12cの新機能である「非同期グローバル索引メンテナンス(Asynchronous Global Index Maintenance)」については記載しなかった。今日はこの機能についてメモを残しておく。残念ながら実機で検証した訳ではないため、あくまで机上で調べた内容に基づき、推測した部分がある点、留意頂きたい。
11gR2までは、truncate/drop partition ... update global indexを発行した際、グローバル索引のメンテナンスが完了するまではコマンドの完了を待たなければならなかった。この処理は、グローバル索引のリーフブロックの不要となった各ROWIDエントリを削除（削除フラグを立てる）する処理を含むため、非常に時間がかかることがあった。一方、12cR1からはトランケート時のグローバル索引メンテナンスは「非同期」に実行されるのがデフォルトの挙動となった。つまり、truncate partition ... update global indexを発行した際、索引のメタデータのみメンテナンスして実際の索引メンテナンスを「先送り」にするのである。このため、トランケート処理は即時に完了し、さらに、グローバル索引はUSABLEのままなので、通常通りにクエリによる正しい問い合わせ結果を得ることができる。実際のグローバル索引のメンテナンスは、以下の方法で実行できる。
(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エントリをクリーンアップする
以上が概略である。さて、この非同期グローバル索引メンテナンス、どのような仕組みで実現されているのだろうか考えてみよう。
まず、truncate partitionした後のグローバル索引の状態であるが、これは一切メンテナンスがされない。つまり、索引のリーフブロックに記録されたROWIDは、そのまま残っている。したがって、トランケートされたパーティション内の行を参照していたROWIDは、参照先がない状態、すなわち親なし(orphaned)エントリが含まれる状態となる。グローバル索引にorphandエントリが含まれるかは、USER_INDEXES.ORPHANED_ENTRIESで確認できる（YESなら含まれる）。このような状態で一体クエリはどのように正しい問い合わせ結果を返すことができるのだろうか。
その答えを知るには、以下について理解しておく必要がある。
まず、truncate partitionした後のメタデータの更新とは、具体的にはグローバル索引のオブジェクトIDと、トランケートしたオブジェクトIDの関連を保持することである。これは、sys.index_orphand_entry$で確認することができる。indexobj#にグローバル索引のオブジェクトID、tabpartobj#にトランケート対象のオブジェクトIDが格納される。
次に、ROWIDについてである。ROWIDはレコードを一意に識別するIDであり、18バイトで構成される。先頭から、6バイトがオブジェクトID、次の3バイトがデータファイル番号、次の6バイトがブロック番号、次の3バイトが行番号という具合である。したがって、グローバル索引のリーフブロックに格納されるROWIDを見れば、それがどのパーティションに属しているかわかるのである。
そして、TBL$OR$IDX$PART$NUM関数である。この関数の仕様は非公開であるが、引数に表の名前とROWIDを与えると、そのROWIDがorphanedかどうかを判別することができる（orphandでなければ1を返す）ようだ。これは、おそらくROWIDからその参照先のパーティションのオブジェクトIDを識別し、それを上記のsys.index_orphand_entry$の情報からorphanedかどうかを判別していると思われる。
さて、上記を踏まえた上で、非同期グローバル索引メンテナンスが行われたグローバル索引を用いたレンジスキャンがどのように動くか考えてみよう。まず、クエリは検索条件からグローバル索引のブランチを通り、リーフブロックに到達し、そこから横にリーフブロックをたどる動きをする。この際、リーフブロックに格納されるROWIDを1つずつTBL$OR$IDX$PART$NUM関数を用いたfilterでorphanedエントリを取り除く（実行計画のpredicateにはfilterが現れる）。そして得られたROWIDの集合について、パーティションへアクセスしそれぞれに対応する行を取得する。実際は、USER_INDEXES.ORPHANED_ENTRIESがNOの場合は、このfilter処理は不要といったロジックは組み込まれているのだろう。
このアーキテクチャでは、orphanedエントリが多くなればなるほど、filter処理が増えることとなる。そのため、返却されるレコード数に対して、必要となる処理時間が延びていくことが想定される。また、INSERT時にリーフブロックが再利用されるのはUNIQUE制約のあるときのみであるらしい（参考１）ため、いくらパーティショントランケートしても、グローバル索引は小さくならず、むしろレコードの増加に伴って単調に増加していってしまう可能性がある。これを避けるために、上記5つのいずれかの方法でorpahnedエントリを定期的に綺麗に削除しておく必要があるだろう。ある意味、非同期による副作用といえる。
実際、Oracleのコミュニティでも以下のスレッド（2018.11)で12cのグローバル索引が肥大化する問題についての議論がされていた。SYS.PMO_DEFERRED_GIDX_MAINT_JOBによる自動メンテナンスが走っているにもかかわらず、である。
Index size of partitioned table is growing
今後、同様の事象があちこちで発生してくるのかもしれない。11gからのハード更改でこの挙動が問題となる場合は、従来の動作にするために"_fast_index_maintenance"=falseとすることも一考の価値があるかもしれない。ただし、隠しパラメータなので、利用についてはサポートに確認した方がよいだろう。
以上
◆参考文献
[1]12c Asynchronous Global Index Maintenance Part II by Richard Foote
[2]12cマニュアル Database VLDB and Partitioning Guide, Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
[3]津島博士のパフォーマンス講座 第43回 パーティションについて（３）
[4]How to Drop/Truncate Multiple Partitions in Oracle 12C (ドキュメントID 1482264.1)
11gR2までは、truncate/drop partition ... update global indexを発行した際、グローバル索引のメンテナンスが完了するまではコマンドの完了を待たなければならなかった。この処理は、グローバル索引のリーフブロックの不要となった各ROWIDエントリを削除（削除フラグを立てる）する処理を含むため、非常に時間がかかることがあった。一方、12cR1からはトランケート時のグローバル索引メンテナンスは「非同期」に実行されるのがデフォルトの挙動となった。つまり、truncate partition ... update global indexを発行した際、索引のメタデータのみメンテナンスして実際の索引メンテナンスを「先送り」にするのである。このため、トランケート処理は即時に完了し、さらに、グローバル索引はUSABLEのままなので、通常通りにクエリによる正しい問い合わせ結果を得ることができる。実際のグローバル索引のメンテナンスは、以下の方法で実行できる。
(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エントリをクリーンアップする
以上が概略である。さて、この非同期グローバル索引メンテナンス、どのような仕組みで実現されているのだろうか考えてみよう。
まず、truncate partitionした後のグローバル索引の状態であるが、これは一切メンテナンスがされない。つまり、索引のリーフブロックに記録されたROWIDは、そのまま残っている。したがって、トランケートされたパーティション内の行を参照していたROWIDは、参照先がない状態、すなわち親なし(orphaned)エントリが含まれる状態となる。グローバル索引にorphandエントリが含まれるかは、USER_INDEXES.ORPHANED_ENTRIESで確認できる（YESなら含まれる）。このような状態で一体クエリはどのように正しい問い合わせ結果を返すことができるのだろうか。
その答えを知るには、以下について理解しておく必要がある。
まず、truncate partitionした後のメタデータの更新とは、具体的にはグローバル索引のオブジェクトIDと、トランケートしたオブジェクトIDの関連を保持することである。これは、sys.index_orphand_entry$で確認することができる。indexobj#にグローバル索引のオブジェクトID、tabpartobj#にトランケート対象のオブジェクトIDが格納される。
次に、ROWIDについてである。ROWIDはレコードを一意に識別するIDであり、18バイトで構成される。先頭から、6バイトがオブジェクトID、次の3バイトがデータファイル番号、次の6バイトがブロック番号、次の3バイトが行番号という具合である。したがって、グローバル索引のリーフブロックに格納されるROWIDを見れば、それがどのパーティションに属しているかわかるのである。
そして、TBL$OR$IDX$PART$NUM関数である。この関数の仕様は非公開であるが、引数に表の名前とROWIDを与えると、そのROWIDがorphanedかどうかを判別することができる（orphandでなければ1を返す）ようだ。これは、おそらくROWIDからその参照先のパーティションのオブジェクトIDを識別し、それを上記のsys.index_orphand_entry$の情報からorphanedかどうかを判別していると思われる。
さて、上記を踏まえた上で、非同期グローバル索引メンテナンスが行われたグローバル索引を用いたレンジスキャンがどのように動くか考えてみよう。まず、クエリは検索条件からグローバル索引のブランチを通り、リーフブロックに到達し、そこから横にリーフブロックをたどる動きをする。この際、リーフブロックに格納されるROWIDを1つずつTBL$OR$IDX$PART$NUM関数を用いたfilterでorphanedエントリを取り除く（実行計画のpredicateにはfilterが現れる）。そして得られたROWIDの集合について、パーティションへアクセスしそれぞれに対応する行を取得する。実際は、USER_INDEXES.ORPHANED_ENTRIESがNOの場合は、このfilter処理は不要といったロジックは組み込まれているのだろう。
このアーキテクチャでは、orphanedエントリが多くなればなるほど、filter処理が増えることとなる。そのため、返却されるレコード数に対して、必要となる処理時間が延びていくことが想定される。また、INSERT時にリーフブロックが再利用されるのはUNIQUE制約のあるときのみであるらしい（参考１）ため、いくらパーティショントランケートしても、グローバル索引は小さくならず、むしろレコードの増加に伴って単調に増加していってしまう可能性がある。これを避けるために、上記5つのいずれかの方法でorpahnedエントリを定期的に綺麗に削除しておく必要があるだろう。ある意味、非同期による副作用といえる。
実際、Oracleのコミュニティでも以下のスレッド（2018.11)で12cのグローバル索引が肥大化する問題についての議論がされていた。SYS.PMO_DEFERRED_GIDX_MAINT_JOBによる自動メンテナンスが走っているにもかかわらず、である。
Index size of partitioned table is growing
今後、同様の事象があちこちで発生してくるのかもしれない。11gからのハード更改でこの挙動が問題となる場合は、従来の動作にするために"_fast_index_maintenance"=falseとすることも一考の価値があるかもしれない。ただし、隠しパラメータなので、利用についてはサポートに確認した方がよいだろう。
以上
◆参考文献
[1]12c Asynchronous Global Index Maintenance Part II by Richard Foote
[2]12cマニュアル Database VLDB and Partitioning Guide, Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
[3]津島博士のパフォーマンス講座 第43回 パーティションについて（３）
[4]How to Drop/Truncate Multiple Partitions in Oracle 12C (ドキュメントID 1482264.1)
2019-05-23 01:24
nice!(1)
コメント(0)
コメント 0