前回のおさらい

前回は、たろーちゃんがＡさんに対し、とある風にアプリケーションの変更指示をしたところ、一気に SELECT が早くなったというところで終わりました。
さて、たろーちゃんは一体何を指示したのでしょうか？

 

アプリケーションをどのように変更したのか？

Aさん「たろーちゃんに言われたとおり、INSERT じゃなく、CTAS（Creata Table As Select）にしただけなのに、どうしてこんなに早くなるんだ？？」

変更内容

ここで、たろーちゃんがAさんに提案した変更内容をご説明します。
従来では、

① MST_TBL 表を TRUNCATE
② 別テーブルからアプリケーションを経由して MST_TBL 表に大量 INSERT
③ MST_TBL 表を SELECT

という処理を行っていました。
これを、

① MST_TBL 表を DROP TABLE
② CTAS（CREATE TABLE MST_TBL AS SELECT … FROM 別テーブル WHERE …）
で、MST_TBL 表を CREATE TABLE
③ MST_TBL 表を SELECT

という処理に変更したのです。
さて、どうしてこれで SELECT が早くなるのでしょうか？

たろー「えーっと、どこから説明しようかな・・・。結論から言うと、今回は『遅延ブロッククリーンアウト』が原因なんだ。『遅延ブロッククリーンアウト』については、My Oracle Support のドキュメントID 1716869.1に書かれているよ。」
Aさん「『遅延ブロッククリーンアウト』？？聞いたことがない言葉だな。ちょっと My Oracle Support のドキュメントを読んでみるか。」
Aさん（・・・。）
Aさん（・・・・・・。）
Aさん（・・・・・・・・・。）

・・・たろーちゃん。
ハッキリ言おう。読んでもサッパリ分からない。

 

 

 

 

 

たろー「すごく複雑な仕組みだからなぁ・・・。どうやって説明しよう？」
Aさん「サルでも分かるように説明してくれ。（俺、犬だけど）」
たろー「そうだな・・・。まずは UNDO のおさらいから始めよう。
UPDATE 文を例にあげて話しをするね。」
Aさん「うむ。」
たろー「まず、MST_TBL 表にこんなデータが入っているとするよ。
ここで、ID 列は主キーとするね。」

たろー「そして、こんな UPDATE 文が実行されたとする。」

UPDATE MST_TBL SET NAME = 'Ｅさん' WHERE ID = '03' ;

たろー「すると Oracle Database は、まず、データファイルからバッファキャッシュに該当のデータブロックを読み取るんだ。」

たろー「そして変更前の情報を UNDO データファイルに書き出す。」

たろー「それから、バッファキャッシュ上のデータを初めて更新するんだ。」

たろー「さて。ここで別セッションから、こんな SELECT 文が実行されたとする。」

SELECT NAME FROM MST_TBL WHERE ID = '03' ;

たろー「この場合、返される NAME は何になるか分かる？」
Aさん「まだ COMMIT されてないから、当然『Dさん』だろ？」
たろー「正解。さすがだな。」
Aさん「たろーちゃん、ひょっとして俺のことをバカにしてるのか？」
たろー「いやいや、そんなことはない。これは遅延ブロッククリーンアウトを理解する上で、大切な大前提なんだ。ここからが重要だよ？」
Aさん「ふむ。」
たろー「Aさんが言った通り、このレコードはまだ COMMIT されていない。
さて。Oracle Database は、『COMMIT されたかどうか？』をどうやって区別してると思う？」
Aさん「どうやって？！」
たろー「うん。」
Aさん「う゛・・・すまん。さすがにそれは分からない・・・。」
たろー「データブロックには ITL（Interested Transaction List）っていうのがあるんだけど、Oracle Database はそれを見て『COMMIT されたかどうか？』を区別しているんだ。」
Aさん「ITL？？」
たろー「うん。ITL はデータブロック単位に存在するんだけど、ここでは分かりやすくするため、レコード単位で表すことにするね。」

Aさん「おお、なるほど。フラグみたいなもんか。」
たろー「そう思ってもらって差し支えない。Oracle Database はこれを見て、『COMMIT されたかどうか？』を区別しているんだ。」
Aさん「ふむ。」
たろー「それで COMMIT が実行されたら、」

たろー「このように ITL が変更されて、次回チェックポイント時に DBWn がデータファイルに書き出すんだ。」

たろー「以上が基本動作だ。ここまでで何か質問はあるかい？」
Aさん「いや、大丈夫だ。まだついていけてるぞ。」

遅延ブロッククリーンアウトとは？

たろー「ここからが本番だ。しっかりついてきてよ？」
Aさん「ああ、任せとけ。」
たろー「さっき説明したのは一番簡単なケースだけど、実際には色んな処理が並列で動作しているから、バッファキャッシュの負荷が高くなるケースがあるんだ。」

たろー「こうなると、Oracle Database はバッファキャッシュに空きを作るために、仕方なく、ITL は未 COMMIT 状態のままでデータファイルに書き出すんだ。」

Aさん「ほう・・・・・・。」
たろー「データファイルに書き出してはいるけど、ITL が未 COMMIT 状態だから、別セッションから SELECT されても、変更前の NAME である『Dさん』が返却される。UNDO を使ってね。Oracle Database はここでもきちんと読み取り一貫性を保証しているんだ。」
Aさん「なるほど。」
たろー「さて、ここで。ようやく COMMIT が実行されたとする。しかし、バッファキャッシュ上にはもうさっきのデータは存在しないよね？。この場合、Oracle Database はどうすると思う？」
Aさん「えーっと・・・フラグである ITL を更新しないといけないから、こんな風にバッファキャッシュ上に読み込んで、」

Aさん「こんな風に ITL を COMMIT 済に更新して、」

Aさん「こんな風にデータファイルに書き出すんじゃないかな？」

たろー「普通に考えるとそうだよね。でも、Oracle Database は高速化のために敢えてその動作をしないんだ。」
Aさん「どういうことだ？」
たろー「『わざわざ ITL の更新のためにデータブロックを読み込むのは無駄』という考え方のもと、即座に ITL の更新は行わないんだ。」
Aさん「・・・ほう？？」
たろー「その代わり、COMMIT 済であることは、UNDO のトランザクション表（トランザクションスロット）に記録しておくんだよ。」

Aさん「ちょっと待て。じゃあ、データファイル上の ITL はいつ更新されるんだ？？」
たろー「さすがAさん、いい質問だ。データファイル上の ITL が更新されるのは、次回アクセスがあった時だ。」
Aさん「次回アクセスがあった時？」
たろー「そう。例えば、次の SQL を実行したとするよ。」

SELECT NAME FROM MST_TBL WHERE ID = '03' ;

たろー「この時の Oracle Database の動きを説明すると、まず、バッファキャッシュにデータファイルから該当のデータブロックを読み込む。」

たろー「しかしここで、Oracle Database は ITL が未 COMMIT 状態を示していることに気付くんだ。だから、UNDO のトランザクションスロットにアクセスする。」

たろー「トランザクションスロットにアクセスした結果、このレコードは COMMIT 済であることが分かったから、ITL を COMMIT 済に変更して、データファイルの更新を行うんだ。」

Aさん「え？SELECT なのに更新がかかることになるのか？？」
たろー「そういうことだ。これが『遅延ブロッククリーンアウト』のメカニズムだ。」

今回のケースでは？

たろー「今回は、」

① MST_TBL 表を TRUNCATE
② 別テーブルからアプリケーションを経由して MST_TBL 表に大量 INSERT
③ MST_TBL 表を SELECT

たろー「という処理を行っていて、かつ、他にも処理が流れていてバッファキャッシュに負担がかかっているよね？」
Aさん「ああ、そうだな。」
たろー「②の大量 INSERT でログスイッチが発生し、チェックポイントが起こるとさっきの例のように、ITL が未 COMMIT 状態になったデータブロックがデータファイルに大量に書き出されているんだ。」
Aさん「ふむ。」
たろー「そして③の SELECT が実行されると、ITL が未だクリーンアウトされていないため、UNDO のトランザクションスロットに大量にアクセスしなければならない。UNDO へのアクセスはマルチブロックではなく、シングルブロックで行われる仕様なんだ。今回の待機イベント『cell single block physical read』が大量に発生している原因はそこにあったんだよ。」
Aさん「な、なるほど・・・。」
たろー「厄介なのは UNDO へのシングルブロックリードだ。これだと Exadata の売りである SmartScan が効かないんだよ。」

た、たしかに・・・。

 

 

 

回避方法だとどうして遅延が起きないのか？

Aさん「でも、たろーちゃんが提案してくれた DROP TABLE＆CTAS だと、どうして遅延が起きないんだ？」
たろー「だってAさん、考えてもみてよ。INSERT は DML だよ？
DML である以上、どうやっても UNDO はつきまとうよね？」
Aさん「ああ、そうだな。」
たろー「それに対して、DROP TABLE＆CTAS は DDL だよ？
UNDO なんて発生しないじゃん。」
Aさん「あ！そうか！！」

たろー「但し、TRUNCATE＆INSERT に比べて注意しなければいけないことがあるんだ。DROP TABLE するということは、そのテーブルに対する SELECT 権限等を他のスキーマに付与していた場合は権限を付与し直さなければならないし、インデックスも作り直さなければならない。また、そのテーブルを参照しているオブジェクトが INVALID になってしまう恐れもある。そこはちゃんと見極めて使う必要があるよ。」
Aさん「うむ、そうだな。」
たろー「今回はアプリケーション的に TRUNCATE＆INSERT を DROP TABLE＆CTAS に置き換えられたからよかったけど、それが出来ない場合は、素直にバッファキャッシュを増やすしか回避手段がないよ。バッファキャッシュを増やし、ITL が未 COMMIT 状態のままキャッシュアウトされるのを極力避けて『遅延ブロッククリーンアウト』が発生しないようにするしかないんだ。」
Aさん「な、なるほど・・・。
今回はすごく勉強になったよ。ありがとう、たろーちゃん！！」

クリーンアウト処理が行われるタイミング

たろーちゃんの説明の繰り返しとなりますが、クリーンアウト処理が行われるのは、対象データブロックに次回アクセスがあった時です。極端なことを言うと、たとえ shutdown immediate でデータベースを正常停止したとしても、対象のデータブロックにはアクセスしていないため、クリーンアウト処理は行われません。これは、次回 startup 時でも同様です。あくまで「対象データブロックに次回アクセスがあった時」のみに、クリーンアウト処理が行われます。
以前の記事『UNDO障害』では、UNDO データファイルが無くてもデータベースを復旧できるケースをご紹介しましたが、仮に未クリーンアウトブロックが存在した場合、データベースは正しく動作しないので、注意して下さい。

 

---

今回の「心臓外科医の術式」いかがだったでしょうか？
遅延ブロッククリーンアウトは発見しづらい症状の一つです。
今回は Exadata 環境を例にあげましたが、非 Exadata 環境でも同様の事象は起こり得ます。
大量 INSERT のあとの SELECT が遅く感じられる場合は、今回ご紹介した遅延ブロッククリーンアウトを視野に入れてみてください。
それでは、次回も頑張りますので、応援よろしくお願い致します。