アプリケーション開発者からの相談

システムの「心臓」こと、Oracle Databaseに日々携わるたろーちゃん。
そんなたろーちゃんのところに、アプリケーション開発者のAさんがまた訪ねてきました。
今回は何があったのでしょうか？

たろーちゃんの連載「心臓外科医の術式」はこちら。

 

Aさん「たろーちゃん、ちょっと相談があるんだけど・・・。」
たろー「うん、どうしたの？」
Aさん「いつものWEBシステムで性能テストをしてるんだが、データベースが期待した動作をしなくて困ってるんだ。」
※Aさんのシステムについては以前の『正体不明のパフォーマンス悪化の謎を解き明かせ！』の回を参照して下さい。

たろー「どういうこと？具体的に教えてよ。」
Aさん「うん。問題になっているのは インターバル・パーティション 化された表なんだ。使わなくなった古いデータを御客様が選択して、管理者画面から削除を実行すると、DROP PARTITIONする仕組みなんだが、その時の動作が全体的に遅いんだ。」
たろー「ほう。」
Aさん「そのテーブルはWEB側でも使用していて、DROP PARTITIONを実行している間の SELECT文 や DML文も遅いんだ。」
たろー「んん？ややこしいな。分からなくなってきたぞ。ちょっと図を描きながら教えてよ。」
Aさん「分かった。」

Aさん「こんな感じ。伝わるかな？」
たろー「これは・・・。１００個のパーティションに対して同時に DROP PARTITION を実行していて、WEBからはそれ以外のパーティションに対してSELECTやDMLが実行されているのか？」
Aさん「そうだ。DROP PARTITION する対象はかなりの大きさで時間が掛かるから、１００同時多重で実行してるんだ。」
たろー「ふーむ………。」
Aさん「パーティションって、それぞれ別物として扱われるから、DROP PARTITION を１００同時多重で実行するのは問題ないよな？」
たろー「うむ………。」
Aさん「WEBからエンドユーザ様がアクセスしてきて SELECT や DML が発行されるけど、対象はDROP PARTITION を実行しているパーティションではないから、これも問題ないよな？」
たろー「うん………。」
Aさん「どれも干渉し合わないから問題ない筈なんだけど、性能テストすると遅いんだ。なんでだろうか？？」
たろー「…………。」
Aさん「…………。」

---

何とも言えない重苦しい時間が続きました。その時、たろーちゃんが何か閃きました。

たろー「待てよ、そうか！」
Aさん「え、何か分かったの？」
たろー「えーっと、まずどこから話そうかな………。
Aさんは、パーティションの利点を活かして、この仕組みを考えたんだよね？」
Aさん「そうだ。このDROP PARTITION は、WEB側に影響を与えない筈だよな？」
たろー「実はね、Aさん……。影響があるんだよ。」
Aさん「いやいや、そんな筈はないだろう。異なるパーティションへの操作は互いに干渉を受けない筈だ。
それがパーティションの利点だって色んな本に書かれてるぞ。パーティションを活用するために、俺も山のように勉強したんだ。そこは自信を持って言える！」
たろー「……Aさんが陥っているのは、『パーティションの闇』だ。」
Aさん「『パーティションの闇』？」

パーティションの闇とは？

Aさん「『パーティションの闇』って何だよ？」
たろー「えーっと、まずAさんは、『ミニ・チェックポイント』って知ってるか？」
Aさん「え？なんだそれ？」
たろー「じゃあ、『library cache lock』って分かるかな？」
Aさん「？？」
たろー「そんな顔しないでよ。」
Aさん「だって、何を言っているのかサッパリ分からないんだもん。」
たろー「えーっと、この場合、DROP PARTITIONが結果的にテーブル全体にロックを掛けてしまってるんだ。
たとえ WEB側が SELECTであっても、そのロックに引っ掛かってしまう。」
Aさん「いやいや、そんな筈は無いって。そもそも SELECT がロックに引っ掛かかるなんて有り得ないだろう？」
たろー「論より証拠だな。見てみるか？」
Aさん「え？」
たろー「『血液検査』だ。」

---

たろーちゃんは該当時間帯のV$ACTIVE_SESSION_HISTORYの情報を調べることにしました。
※V$ACTIVE_SESSION_HISTORYに関しては以前の記事『正体不明のパフォーマンス悪化の謎を解き明かせ！』の回をご参照下さい。

たろー「・・・やっぱりだ。間違いない！」

伝家の宝刀『血液検査』が、今回も謎を切り裂きます！

血液検査の結果は？

たろーちゃんは該当時間帯の V$ACTIVE_SESSION_HISTORY の結果をAさんに見せました。

たろー「ほら、これを見てくれ。」
Aさん「どれどれ………ん？
なんだこの『enq: RO – fast object reuse』って待機イベントは？
あと、さっきたろーちゃんが言ってた『library cache lock』って待機イベントも多いな。」
たろー「これが『パーティションの闇』の正体だ。このテーブルにアクセスしようとしているセッションの全てが『library cache lock』で待たされているだろう？」
Aさん「しかもこれ………WEB側の処理だけでなく、DROP PARTITION同士の処理すらも『library cache lock』で待機してるのか？！」
たろー「そうだ。」
Aさん「一体どういうことなんだ？異なるパーティションに対する処理は互いに干渉しないのがパーティションの売りの筈だろう？！」
たろー「それはメリットだけを謳った上っ面の情報だ。実際には影響するんだよ。」
Aさん「どういうメカニズムなのか、もっと詳しく教えてくれ。」

ミニ・チェックポイントとは？

たろー「まず、『enq: RO – fast object reuse』の説明からだ。
さっき、ミニ・チェックポイントって知ってるかって聞いたよな？」
Aさん「ああ。聞いたことがない言葉だ。普通のチェックポイントと何がどう違うんだ？」
たろー「普通のチェックポイントは、ダーティブロック全てを書き出す処理だ。それに対して、ミニ・チェックポイントは、該当テーブル（パーティション）のダーティブロックのみを書き出すんだ。」
Aさん「ふむ。」
たろー「ミニ・チェックポイントの発生タイミングは、TRUNCATEもしくはDROPが実行された時だ。」
Aさん「TRUNCATEかDROPが実行された時？！」
たろー「そう。今回の場合、DROP PARTITIONされたパーティションのダーティブロックが全て書き出されるんだ。その時に発生する待機イベントが、『enq: RO – fast object reuse』だ。ダーティブロックが多ければ多いほど、この待機イベントが多くなる。これは My Oracle Support のドキュメントID 1475659.1 にも書かれている情報だ。」
Aさん「そんなの聞いたこともないぞ・・・。どうせ DROP するデータなんだから、わざわざ書き出さなくてもいいのに・・・。」

library cache lock とは？

たろー「さて。通常、テーブルロックされた場合の待機イベントは『enq: TM – contention』だよな？」
Aさん「ああ、その待機イベントは知ってる。よく見かけるよね。」
たろー「でも今回はそうじゃない。『library cache lock』だ。」
Aさん「何者なんだ、そいつは？」
たろー「こいつは共有プール上のライブラリキャッシュをロックする際に発生する待機イベントだ。」
Aさん「ライブラリキャッシュ？」
たろー「ああ。DROP や TRUNCATE されたオブジェクトに対する共有カーソルは
全て無効（INVALID）にしなければならないという Oracle Databaseの仕様があって、
そのためにライブラリキャッシュをロックする必要があるんだ。
しかも、このライブラリキャッシュってのはパーティション毎じゃなく、テーブル全体に対して排他モードで取得されるんだ。」
Aさん「パーティションではなく、テーブル全体に対して？排他モードで？」
たろー「そう。だからパーティション同士が互いに干渉しないというのは確かにその通りなんだけど、
ライブラリキャッシュにおいてはテーブル単位なのでどうしても干渉してしまうんだ。」
Aさん「なんだそりゃ・・・。」
たろー「ここが一番難しい所なんだけど、分かるかなAさん。
１００同時多重で DROP PARTITION を実行したとしても、あるパーティションを DROP 中は
ライブラリキャッシュをずっと保持しているから、他の DROP PARTITION は同時に動作することが出来ないんだ。」
Aさん「な、なんてこった・・・。」
たろー「共有カーソルの無効化についてはMy Oracle Support のドキュメントID 1731739.1に詳しく書かれているよ。また『library cache lock』についても、ドキュメントID 2087456.1 に書かれているよ。」

SELECTが待機するのは何故？

Aさん「でも、たろーちゃん。WEB側のSELECTが待機してしまうのは何故なんだ？？」
たろー「それもライブラリキャッシュのロックで説明がつくよ。
SQLを実行する時って、最初ハードパースを行うよね？」
Aさん「うん。」
たろー「SELECT を実行するときに、ソフトパースが使えなければ、ハードパースが実行される。
つまり、ライブラリキャッシュのロックが必要なんだ。」
Aさん「げぇ！ということは・・・。」
たろー「そう。『enq: TM – contention』でもなく、『enq: TX – row lock contention』でもなく、
『library cache lock』でSELECTが待たされているんだよ。」
Aさん「し、信じられん・・・。」

まとめると？

たろー「今回の事象はかなり複雑だ。もう一度振り返ってみるよ。」

①	バッチ処理１が DROP PARTITION を実行しようとする。
②	バッチ処理２が DROP PARTITION を実行しようとする。
③	バッチ処理１は該当パーティションに関わる共有カーソルを無効(INVALID)にするため、『library cache lock』を取得する。そして、DROP PARTITIONを実行するが、ダーティブロックが多いためミニ・チェックポイントが『enq: RO – fast object reuse』で待機する。
④	バッチ処理２も該当パーティションに関わる共有カーソルを無効(INVALID)にするため、『library cache lock』を取得しようとするが、バッチ処理１が既に取得しているため、待ちが発生する。
⑤	バッチ処理１のミニ・チェックポイントが終了し、ようやく共有カーソルを無効(INVALID)にすることができ、『library cache lock』を解放する。
⑥	バッチ処理１が終了したことで、バッチ処理２はようやく『library cache lock』を取得することができ、ミニ・チェックポイントを実行する。
⑦	上記がパーティション１００まで、ずっと続く・・・。

たろー「ここまでが、DROP PARTITION側の動作だ。ここに更にWEB側の動作が加わると・・・。」

①	WEB処理１がSELECTを実行しようとする。
②	WEB処理２がSELECTを実行しようとする。
③	WEB処理１はハードパースを実行するため、『library cache lock』を取得しようとするが、バッチ処理１が既に取得しているため、待ちが発生する。
④	WEB処理２もハードパースを実行するため、『library cache lock』を取得しようとするが、バッチ処理１が既に取得しているため、同じく待ちが発生する。しかも『library cache lock』が取得できるのは、WEB処理１が終わってからである。
⑤	上記がWEB処理ｎまで続く・・・。

Aさん「嘘だろ、おい・・・。全部シリアル（直列）に動作するのかよ。」
たろー「………これが『パーティションの闇』だ。パーティションも万能じゃないってことさ。」

改善案

Aさん「メカニズムは分かった。でも改善するにはどうしたらいいのかな？？」
たろー「１つ目の改善案だが、まずDROP PARTITION の１００同時多重を辞めて、ループ処理で１００回繰り返すようにするんだ。」
Aさん「そうすると何が変わるんだ？」
たろー「DROP PARTITION を一気に１００同時多重で実行すると、ライブラリキャッシュを取得するための待ち行列がいきなり１００個出来てしまう。こんな感じだ。」

たろー「これだと、WEB側のSELECTが待ち行列の一番最後に並んでしまう。」

たろー「ブラウザから操作しているエンドユーザ様からすると、全然応答が返ってこないことになるよな？」
Aさん「ああー、それはマズイな。」
たろー「だろ？下手をすると、HTTPレベルでタイムアウトが発生してしまうよ。」
Aさん「だから DROP PARTITIONをループ処理にするのか？」
たろー「そうだ。DROP PARTITIONをループ処理にすれば、『enq: RO – fast object reuse』で待機してる間にSELECTがきた時、その後ろに並ぶことが出来る。」

Aさん「なるほど。ぶっちゃけ、DROP PARTITION の優先度なんて低いもんな。
一番恐いのは、『エンドユーザ様からのクレーム』だ。応答がいつまで経ってもなくて、
HTTPレベルでタイムアウトが発生なんかしたら・・・。」

たろー「次に２つ目の改善案だ。WEB側のSQLはなるべくバインド変数を使うようにして、極力ハードパースの回数を減らすんだ。
ハードパースをしなければ、ライブラリキャッシュをロックする必要はないからな。」
Aさん「なるほど。該当のプログラムをもう一回見直してみるよ。」
たろー「最後に３つ目の改善案は・・・ありきたりだけど、
『WEBアクセスが少ない時間帯に DROP PARTITION を実行する』だ。
御客様が管理者画面から操作した時に即時 DROP PARTITION を実行するのではなく、
処理開始時刻を指定できるようにしてみては？」
Aさん「なるほど・・・。」
たろー「確かこのシステムは、午前４時頃が一番アクセスが少ないんだよね？」
Aさん「そうだ。」
たろー「なら、その時間帯に DROP PARTITION を実行するのがベストだろうね。
また予算に跳ねるかもしれないけど、改修してみては？」
Aさん「分かった。御客様に提案してみるよ。」

そもそも情報はどこにもないのか？

Aさん「しかし、今回のメカニズムはマニュアルとかに書かれているのか？聞いたことがないんだが・・・。」
たろー「いや、動作仕様なので、ここまで細やかに書かれた資料は見たことがない。
でもオラクル社のこのサイトの一番下のほうに少しだけ書かれているよ。」
Aさん「え？！………本当だ。こんなの気付かないよ（泣）」

---

今回の「心臓外科医の術式」いかがだったでしょうか？
伝家の宝刀『血液検査』でパーティションの闇を切り裂く、見事な「術式」だったのではないでしょうか？

次回も頑張りますので、応援よろしくお願い致します。