exadataと遅延ブロッククリーンアウトとシングルブロックリード [アーキテクチャ]
exadataにおいて、フルスキャンで読み取り一貫性を発生させない（つまりUNDOへの読み込みを発生させない）ことが遅延対策のポイントになるのは言うまでも無いが、それ以外にも遅延ブロッククリーンアウトが遅延の原因となるケースに遭遇したのでメモしておく。
事象としては、1GB程度のテーブルをcsvに出力する処理（夜間バッチ）が50分（SQLレポートのelapsed time）かかったのである。クエリは単純な1テーブルへアクセスするもので母体の件数は600万件、WHERE句でフラグでフィルタしているので、出力は500万件程度。SQLトレースを取得すると、大量のシングルブロックリード（20GB以上)が発生しており、ASHからUNDO表領域へのアクセスであることが判明した。日中帯に同じSQLを実行したら16秒程度(sqlplusからautotrace traceonlyで測定)で完了する。このときスマートスキャンが発生していた（SQLトレースから確認）していたため、通常であればそもそもシングルブロックリードが発生するはずがない。このテーブルは事前にトランケート・インサートされた後にこのクエリが走るだけで、他のトランザクションはないため、読み取り一貫性によるUNDOへのアクセスが発生していた可能性はない。しかも母体のテーブルサイズをはるかに上回るUNDO量である。これは一体どういうことか。。。
一般的なOracle（非Exadata）ではFTSはマルチブロックで読み込んだブロックに対して読み取り一貫性のため必要なUNDOを読み込みCRブロックを生成する。スマートスキャンの恩恵は受けられないが、IOはマルチブロックリードにより効率的に行われる。一方、exadataではFTSはスマートスキャンとなることがあり、その場合は各ストレージサーバへFTSの処理がオフロードされる。このとき、各ストレージサーバはCRブロックを返却する必要があるが、各ストレージサーバは各ブロックが「CRブロックである」ことを認識し、ストレージサーバ毎に独立してプレディケートフィルタやカラムプロジェクションを行うのである。しかし、「CRブロックでない」、つまり読み取り一貫性の為にUNDOを読み込む必要があると、ストレージサーバはスマートスキャンを諦めてシングルブロックを返却する。そして、DBサーバ上のバッファキャッシュ上で必要なUNDOブロックを読み込み（シングルブロックリード）CRブロックを生成することを繰り返す。スマートスキャンの恩恵を受けるため、他トランザクションからの更新を避けるのはこのためである。
実はこの挙動は遅延ブロッククリーンアウトでも発生する。遅延ブロッククリーンアウトは、簡単に言うと最新化されていない未コミット状態のブロックをコミット時ではなく、後でコミット済みに変更するメカニズムである。これが発生すると、ブロック読み込み時に、そのブロックがコミット済みかどうかを確認するために、ITLに記録されるトランザクションID（xid）から、UNDOセグメントヘッダのトランザクション表へアクセスする。コミット済みであることが確認できれば、そのブロックのITLをコミットSCNとあわせてコミット済みに変更する。ブロックへの変更が発生するため、（たとえSELECTであっても）REDOが発生する。
また、UNDOセグメント毎のトランザクション表のスロット数はOracleのバージョンやオプションにより決まる（34,48,96個等）。また、１つのUNDO表領域上に作成できるUNDOセグメント数にも上限があることから、必然的にトランザクション表のスロットは上書きされてしまう。このため、コミット時のSCNを確認するため、トランザクション表への読み取り一貫性、つまりUNDOをたどって過去のSCNを確認するという挙動が発生することもある。これらUNDOへのアクセスは、すべてDBサーバからシングルブロックリードが必要である。
ストレージサーバはCRブロックであることをどのように判断するのだろうか。各ブロックのITLにはそのブロックにアクセスするトランザクション（xid）とその状態（コミット済みがどうか等）、コミット済みSCN、関連するUNDOブロックアドレスが記録されている。したがって、オフロードされたクエリのSCNがコミットSCNより新しければ、そのブロックはCRブロックと判別できるはずである。未コミットのトランザクションがある場合、またはコミット済みでもクエリのSCNがコミットSCNより古い場合はUNDOブロックを読む必要があるので、シングルブロックを返却する流れとなる。実際はもっと複雑な流れだとは思うが、おおまかにはこんなところだろう。
では、遅延ブロッククリーンアウトの状況ではどうだろうか。遅延ブロッククリーンアウトは、コミット済みであるにもかかわらず、ブロックは未コミット状態となっている状況である。上記ロジックが正しいとすれば、この状況は必ずUNDOの読み込みが必要となるため、スマートスキャンはできなく、シングルブロックを返却する流れとなることは容易に想像がつく。
遅延ブロッククリーンアウトが発生する要因の一つは、バッファキャッシュの枯渇である。未コミットのダーティブロックが、トランザクション完了（コミット）前にDBWRによりパージされてしまうと、トランザクション完了してもディスク上には未コミット状態のままとなる。このため、更新時のバッファキャッシュを増加させれば発生頻度を減らすことが可能である。もちろん、コミット間隔を小さくすることも対策として有効だろう。また、UNDOへのアクセスを減らすという意味では、トランザクション表（のスロット）が上書きされなければ、トランザクション表に対するUNDO読み込みは不要となる可能性があるため、更新直後に読み込む、という対処も有効なはずである。
あくまで仮設であるが、今回の遅延事象は、INSERT時（2000件単位でコンベンショナルINSERT・コミット）のバッファキャッシュ上のブロックが、他処理との並走によりバッファキャッシュが不足し、未コミット状態でディスクに反映されたものが増えた。また、コミット後からクエリまで2時間程度経過していたことから、トランザクション表が上書きされ、コミットSCNを確認するためにトランザクション表のUNDOを読み込む必要がある状態になっていた。このため、後続のフルスキャンで遅延ブロッククリーンアウトが発生し、UNDOへの読み込みが必要なことからスマートスキャンは効かず、UNDOへのシングルブロックリードを発生させたのではないか、と考えた。実際、バッファキャッシュを２倍程度に変更することで、50分かかっていたクエリが10秒以下にすることができた。簡単に２つの処理の違いを比べると、如何にExadataにおいて遅延ブロッククリーンアウトの際のオーバーヘッドが大きいかわかる。
◆通常のスマートスキャンの挙動
フルスキャン→ダイレクトパスリード→スマートスキャン（マルチブロックリード）→ストレージオフロードによりフィルタ済みブロックのみ返却→結果セット
◆遅延ブロッククリーンアウトの際の挙動
フルスキャン→ダイレクトパスリード→スマートスキャン→ITLが未コミットなのでシングルブロックで返却→UNDOセグメントヘッダ読み込み→コミットSCN確認のためトランザクション表のUNDO読み込み→コミットクリーンアウト→UNDO変更のREDOチェンジベクター生成→バッファキャッシュ上でフィルタ処理→結果セット
遅延ブロッククリーンアウトが発生したことを示すことは一般的に難しいとされる。今回はAWRやASHから得られる情報から消去法的に仮設を作り上げた。ただ、それでもなお、今回なぜこれだけの量のUNDOを読む必要があるのかまでは追いきれなかった。この事象を通して遅延ブロッククリーンアウトについて改めて理解を深めることができたのは、やはりJonathan Lewis氏のOracle Coreによることが大きい。Exadataや最新のOracleバージョンには記載はないが、根本のアーキテクチャは変わらないものである。
わかればわかるほど、わからないことを知る、ということを感じる今日この頃である。
以上
◆参考
事象としては、1GB程度のテーブルをcsvに出力する処理（夜間バッチ）が50分（SQLレポートのelapsed time）かかったのである。クエリは単純な1テーブルへアクセスするもので母体の件数は600万件、WHERE句でフラグでフィルタしているので、出力は500万件程度。SQLトレースを取得すると、大量のシングルブロックリード（20GB以上)が発生しており、ASHからUNDO表領域へのアクセスであることが判明した。日中帯に同じSQLを実行したら16秒程度(sqlplusからautotrace traceonlyで測定)で完了する。このときスマートスキャンが発生していた（SQLトレースから確認）していたため、通常であればそもそもシングルブロックリードが発生するはずがない。このテーブルは事前にトランケート・インサートされた後にこのクエリが走るだけで、他のトランザクションはないため、読み取り一貫性によるUNDOへのアクセスが発生していた可能性はない。しかも母体のテーブルサイズをはるかに上回るUNDO量である。これは一体どういうことか。。。
一般的なOracle（非Exadata）ではFTSはマルチブロックで読み込んだブロックに対して読み取り一貫性のため必要なUNDOを読み込みCRブロックを生成する。スマートスキャンの恩恵は受けられないが、IOはマルチブロックリードにより効率的に行われる。一方、exadataではFTSはスマートスキャンとなることがあり、その場合は各ストレージサーバへFTSの処理がオフロードされる。このとき、各ストレージサーバはCRブロックを返却する必要があるが、各ストレージサーバは各ブロックが「CRブロックである」ことを認識し、ストレージサーバ毎に独立してプレディケートフィルタやカラムプロジェクションを行うのである。しかし、「CRブロックでない」、つまり読み取り一貫性の為にUNDOを読み込む必要があると、ストレージサーバはスマートスキャンを諦めてシングルブロックを返却する。そして、DBサーバ上のバッファキャッシュ上で必要なUNDOブロックを読み込み（シングルブロックリード）CRブロックを生成することを繰り返す。スマートスキャンの恩恵を受けるため、他トランザクションからの更新を避けるのはこのためである。
実はこの挙動は遅延ブロッククリーンアウトでも発生する。遅延ブロッククリーンアウトは、簡単に言うと最新化されていない未コミット状態のブロックをコミット時ではなく、後でコミット済みに変更するメカニズムである。これが発生すると、ブロック読み込み時に、そのブロックがコミット済みかどうかを確認するために、ITLに記録されるトランザクションID（xid）から、UNDOセグメントヘッダのトランザクション表へアクセスする。コミット済みであることが確認できれば、そのブロックのITLをコミットSCNとあわせてコミット済みに変更する。ブロックへの変更が発生するため、（たとえSELECTであっても）REDOが発生する。
また、UNDOセグメント毎のトランザクション表のスロット数はOracleのバージョンやオプションにより決まる（34,48,96個等）。また、１つのUNDO表領域上に作成できるUNDOセグメント数にも上限があることから、必然的にトランザクション表のスロットは上書きされてしまう。このため、コミット時のSCNを確認するため、トランザクション表への読み取り一貫性、つまりUNDOをたどって過去のSCNを確認するという挙動が発生することもある。これらUNDOへのアクセスは、すべてDBサーバからシングルブロックリードが必要である。
ストレージサーバはCRブロックであることをどのように判断するのだろうか。各ブロックのITLにはそのブロックにアクセスするトランザクション（xid）とその状態（コミット済みがどうか等）、コミット済みSCN、関連するUNDOブロックアドレスが記録されている。したがって、オフロードされたクエリのSCNがコミットSCNより新しければ、そのブロックはCRブロックと判別できるはずである。未コミットのトランザクションがある場合、またはコミット済みでもクエリのSCNがコミットSCNより古い場合はUNDOブロックを読む必要があるので、シングルブロックを返却する流れとなる。実際はもっと複雑な流れだとは思うが、おおまかにはこんなところだろう。
では、遅延ブロッククリーンアウトの状況ではどうだろうか。遅延ブロッククリーンアウトは、コミット済みであるにもかかわらず、ブロックは未コミット状態となっている状況である。上記ロジックが正しいとすれば、この状況は必ずUNDOの読み込みが必要となるため、スマートスキャンはできなく、シングルブロックを返却する流れとなることは容易に想像がつく。
遅延ブロッククリーンアウトが発生する要因の一つは、バッファキャッシュの枯渇である。未コミットのダーティブロックが、トランザクション完了（コミット）前にDBWRによりパージされてしまうと、トランザクション完了してもディスク上には未コミット状態のままとなる。このため、更新時のバッファキャッシュを増加させれば発生頻度を減らすことが可能である。もちろん、コミット間隔を小さくすることも対策として有効だろう。また、UNDOへのアクセスを減らすという意味では、トランザクション表（のスロット）が上書きされなければ、トランザクション表に対するUNDO読み込みは不要となる可能性があるため、更新直後に読み込む、という対処も有効なはずである。
あくまで仮設であるが、今回の遅延事象は、INSERT時（2000件単位でコンベンショナルINSERT・コミット）のバッファキャッシュ上のブロックが、他処理との並走によりバッファキャッシュが不足し、未コミット状態でディスクに反映されたものが増えた。また、コミット後からクエリまで2時間程度経過していたことから、トランザクション表が上書きされ、コミットSCNを確認するためにトランザクション表のUNDOを読み込む必要がある状態になっていた。このため、後続のフルスキャンで遅延ブロッククリーンアウトが発生し、UNDOへの読み込みが必要なことからスマートスキャンは効かず、UNDOへのシングルブロックリードを発生させたのではないか、と考えた。実際、バッファキャッシュを２倍程度に変更することで、50分かかっていたクエリが10秒以下にすることができた。簡単に２つの処理の違いを比べると、如何にExadataにおいて遅延ブロッククリーンアウトの際のオーバーヘッドが大きいかわかる。
◆通常のスマートスキャンの挙動
フルスキャン→ダイレクトパスリード→スマートスキャン（マルチブロックリード）→ストレージオフロードによりフィルタ済みブロックのみ返却→結果セット
◆遅延ブロッククリーンアウトの際の挙動
フルスキャン→ダイレクトパスリード→スマートスキャン→ITLが未コミットなのでシングルブロックで返却→UNDOセグメントヘッダ読み込み→コミットSCN確認のためトランザクション表のUNDO読み込み→コミットクリーンアウト→UNDO変更のREDOチェンジベクター生成→バッファキャッシュ上でフィルタ処理→結果セット
遅延ブロッククリーンアウトが発生したことを示すことは一般的に難しいとされる。今回はAWRやASHから得られる情報から消去法的に仮設を作り上げた。ただ、それでもなお、今回なぜこれだけの量のUNDOを読む必要があるのかまでは追いきれなかった。この事象を通して遅延ブロッククリーンアウトについて改めて理解を深めることができたのは、やはりJonathan Lewis氏のOracle Coreによることが大きい。Exadataや最新のOracleバージョンには記載はないが、根本のアーキテクチャは変わらないものである。
わかればわかるほど、わからないことを知る、ということを感じる今日この頃である。
以上
◆参考
Oracle Core: Essential Internals for DBAs and Developers (Expert's Voice in Databases)
- 作者: Jonathan Lewis
- 出版社/メーカー: Apress
- 発売日: 2011/12/06
- メディア: ペーパーバック
19c新機能 ~ Oracle Cloud Days [最新動向]
8/6,7に新高輪で開催されたOracle Modern Cloud Day Tokyoの１日目に参加し、19cの新機能についての話を聞いてきた。内容は概ね以下のスライド通り。3点ほど気になった点があったので以下にメモしておく。
2019/4/22 TechNight資料
1点目。プレゼンを聞いていてリアルタイム統計がデフォルト有効、かつ、無効化するにはヒント（NO_GATHER_OPTIMIZER_STATISTICS）を使うという話があった。しかし現実問題、更改案件では無効化するために全てのSQLにヒントを入れることは不可能なので、この話は受け入れがたい。性能負荷はわずかである(minやmax、num_rowsといった最小限の統計のみ取得する)といいつつも、負荷もさることながら、統計変動による実行計画変動を避けたい、というニーズは変わらず存在するからだ。
実際、以下の初期化パラメータを設定(FALSE)することで無効化できる。隠しパラメータなので、おそらく積極的に設定されないよう、あまり公にしたくないという意図が感じられた。
"_optimizer_use_stats_on_conventional_dml" ・・・リアルタイム統計を使用するか制御するパラメータ
"_optimizer_gather_stats_on_conventional_dml" ・・・ リアルタイム統計を収集するか制御するパラメータ
また、12cからのダイレクトインサート時(CreateTableAsSelectやInsertAsSelect)の統計収集機能もある。これもヒントで制御できるが以下の初期化パラメータで無効化できる（デフォルト有効化）。
"_optimizer_gather_stats_on_load"
経験上、これは取得にそれなりの負荷がかかるので、意識してインサート時に統計取得したい！、という場合の除いて無効化しておくことが良いだろう。DWHの一時表など、そもそも取得の必要がない、あるいは統計ロック等で変動を抑える対処を検討済みのシステムを更改する場合などではなおさらである。
2点目。19cの高頻度自動オプティマイザ統計収集は、15分間隔でstaleになったテーブルの統計を取得する機能であるが、これが実装されたのは興味深い。以前、同様な機能を作りこもうとしている人をOracleコミュニティで見たことがあったからだ。DWHでロードするタイミングが複雑で、適切な統計取得のウィンドウを１つに決められないケースだったと記憶している。デフォルトオフなので、あまり気にすることはないが、何かに役立つかもしれないので覚えておきたい。
3点目。上記プレゼンの中で、自動インデックスのデモがあった。単純なフルスキャンの実行計画が自動インデックスにより1秒以下にチューニングされたというもの。正直、これだけでは商用でまともに使える感触までは得られなかったが、AUTO_INDEX_MODE=report onlyでレポートだけ作成してくれる機能があるので、例えば開発環境でバッチを一通り流してどのような自動インデックスが作成されるか見るという使い方はできるかもしれない（ただ、それならSQLアドバイザとかを使えばよいだけかもしれないが）。感覚的にはインデックス作成によるチューニングは入念な試験を経て初めて商用リリースできると思っているので、15分間隔のサイクルで自動で新たなインデックスが有効化される、という世界観がどれだけ受け入れられるのか、今後の動向を注視したい。
以上
2019/4/22 TechNight資料
1点目。プレゼンを聞いていてリアルタイム統計がデフォルト有効、かつ、無効化するにはヒント（NO_GATHER_OPTIMIZER_STATISTICS）を使うという話があった。しかし現実問題、更改案件では無効化するために全てのSQLにヒントを入れることは不可能なので、この話は受け入れがたい。性能負荷はわずかである(minやmax、num_rowsといった最小限の統計のみ取得する)といいつつも、負荷もさることながら、統計変動による実行計画変動を避けたい、というニーズは変わらず存在するからだ。
実際、以下の初期化パラメータを設定(FALSE)することで無効化できる。隠しパラメータなので、おそらく積極的に設定されないよう、あまり公にしたくないという意図が感じられた。
"_optimizer_use_stats_on_conventional_dml" ・・・リアルタイム統計を使用するか制御するパラメータ
"_optimizer_gather_stats_on_conventional_dml" ・・・ リアルタイム統計を収集するか制御するパラメータ
また、12cからのダイレクトインサート時(CreateTableAsSelectやInsertAsSelect)の統計収集機能もある。これもヒントで制御できるが以下の初期化パラメータで無効化できる（デフォルト有効化）。
"_optimizer_gather_stats_on_load"
経験上、これは取得にそれなりの負荷がかかるので、意識してインサート時に統計取得したい！、という場合の除いて無効化しておくことが良いだろう。DWHの一時表など、そもそも取得の必要がない、あるいは統計ロック等で変動を抑える対処を検討済みのシステムを更改する場合などではなおさらである。
2点目。19cの高頻度自動オプティマイザ統計収集は、15分間隔でstaleになったテーブルの統計を取得する機能であるが、これが実装されたのは興味深い。以前、同様な機能を作りこもうとしている人をOracleコミュニティで見たことがあったからだ。DWHでロードするタイミングが複雑で、適切な統計取得のウィンドウを１つに決められないケースだったと記憶している。デフォルトオフなので、あまり気にすることはないが、何かに役立つかもしれないので覚えておきたい。
3点目。上記プレゼンの中で、自動インデックスのデモがあった。単純なフルスキャンの実行計画が自動インデックスにより1秒以下にチューニングされたというもの。正直、これだけでは商用でまともに使える感触までは得られなかったが、AUTO_INDEX_MODE=report onlyでレポートだけ作成してくれる機能があるので、例えば開発環境でバッチを一通り流してどのような自動インデックスが作成されるか見るという使い方はできるかもしれない（ただ、それならSQLアドバイザとかを使えばよいだけかもしれないが）。感覚的にはインデックス作成によるチューニングは入念な試験を経て初めて商用リリースできると思っているので、15分間隔のサイクルで自動で新たなインデックスが有効化される、という世界観がどれだけ受け入れられるのか、今後の動向を注視したい。
以上
JPOUG 2019 presentation slide (English) [コミュニティ]
Please find my presentation slide (English) in JPOUG held on July 23, 2019. The content is mostly based on my previous post about truncate, but I have also mentioned new features in 12c.
