待ちイベントに関する検証 その４
＜待ちイベントに関する検証 その４＞
ペンネーム： ダーリン
【latch: shared pool/library cache】
さて、先週は結局 library cache の待ちが発生 “する” のか “しない” のか
よくわからない解説で終了してしまいました。
今週はもう少し解説を続けます。
まず、実際に待ちが発生しているときの統計情報をもう一度見てみましょう。
今回は、v$latch の情報も同時に取得してみました。
前回と同様、同時 20 Session での検索処理です。
[ V$SYSTEM_EVENT の情報 ] event wait_class total_wait time_waited ---------------------------- ------------ ---------- ------------ latch: cache buffers chains Concurrency 9 4635 latch: shared pool Concurrency 4 564 latch: library cache Concurrency 2 389 latch: session allocation Other 2 233 log file sync Commit 29 136 control file parallel write System I/O 80 32 log file parallel write System I/O 604 24 db file scattered read User I/O 5 9 db file sequential read User I/O 3 4
[ V$LATCH の情報 ] name gets misses im_gets im_misses wait_time ---------------------- ------ ------ ------- ---------- --------- cache buffers chains 310991 11 199 0 46342678 shared pool 22291 4 0 0 5643956 library cache 41529 2 53 0 3889388 session allocation 3745 2 0 0 2332737 * im_gets : immediate gets * im_misses : immediate misses
一方、待ちが発生しない場合の統計値はどうでしょうか。
同じく、同時 20 Session での検索処理です。
[ V$SYSTEM_EVENT の情報 ] event wait_class total_wait time_waited ---------------------------- ------------ ---------- ------------ control file parallel write System I/O 79 298 log file parallel write System I/O 15 2 latch free Other 6 1 [ V$LATCH の情報 ] name gets misses im_gets im_misses wait_time ---------------------- ------ ------ ------- ---------- --------- session idle bit 267123 6 0 0 17355 * im_gets : immediate gets * im_misses : immediate misses
待ちイベントの内容が前回とちょっと変わってしまったのは、ご勘弁いただく
として、待ちイベントが多い場合と、少ない場合とを比較すると library cache
の Concurrency が多いことは前回の検証結果と変わりありません。
さて library cache の競合が多くなる理由のひとつに、排他モードでのラッ
チ取得があります。まったく同じ SQL が実行されている環境では、共有モー
ドで library cache のラッチが取得されるので、この場合は競合は発生しに
くいはずです。 これが、”待ちが発生しない”ケースです。
で、では”待ちが発生する”ケースはどんな状況かというと、ここでは library
cache のラッチが排他モードで取得されやすい状況をさしています。
検証時に実行していた SQL はどちらも同じですが、違いは、共有プールの空き
を 12% 程度まで減少させた点です。(つまり使用率が 88% 。)
さらに、同じSQLですが、一部の Session でスキーマが異なる SQL を実行し
てみました。
すると、”待ちが発生する”ような結果が得られます。
実際の環境に置き換えて考えてみると、OLTP環境で実行しているSQLがほぼ、
再利用率の高いSQLだったとします。ただし、オンライン中のバッチ処理で、
再利用されないSQLが実行されたりしているような場合です。
徐々に共有プールの使用率が上昇し、空きがなくなってきた状況で、突然、
OLTP系の処理のレスポンスが低下するかもしれません。
ちなみに、”待ちが発生する”場合の待ちイベントの中でどれがインパクトが
大きいのでしょうか？
単純に time_waited を total_wait で割ってみると、
event total_wait(A) time_waited(B) (B)/(A) ---------------------------- ------------- -------------- -------- latch: cache buffers chains 9 4635 515.0 latch: shared pool 4 564 141.0 latch: library cache 2 389 194.5 latch: session allocation 2 233 116.5 log file sync 29 136 4.7 control file parallel write 80 32 0.4 log file parallel write 604 24 0.0 db file scattered read 5 9 1.8 db file sequential read 3 4 1.3
さてさて、”latch: xxxxxx” の待ちイベントの1回あたりの待ち時間が他の待
ちイベントと比較して、桁違いに大きな値をしています。少なくともこの環境
では、ラッチ待ちの影響がかなり大きいようです。
もちろん共有プールの使い方にもよりますが。
library cache のラッチを簡単に説明すると主に 3 種類あります。
library cache lock
SQL や、プロシージャなどを実行中に、共有プールに存在するそれら
の管理情報に対して取得するラッチ。情報が変更されない場合は共有
モードで取得する。子カーソルに変更がある場合などは排他モードで
取得される。
library cache pin
SQL の実行計画や、プロシージャの中間コードなどが格納されている
領域に対して取得するラッチ。こちらも通常、共有モードで取得され
る。ただし、新規に実行計画がロードされる際などは、排他モードで
取得される。
library cache load lock
新規のSQLやプロシージャで共有プールに情報をロードする際に取得す
るラッチ。同じSQLを同時にロードさせないためのラッチで排他モード
で取得される。
ついでに、shared pool のラッチについても触れておきます。
これは、今回のように共有プールの空きが足りなくなった場合など、メモリ領
域確保の際にも取得されます。このラッチは SGA のヒープに対して1つしか取
得できないため、常に排他モードで取得されます。平たく言えばボトルネック
になりやすいので、注意が必要ということです。
共有プールに関するラッチ待ち(library cache: xxxx)を引き起こさないよう
にするには、なるべく再利用可能な SQLを使用することが大切です。物理メモ
リに余裕があるのであれば、共有プールが不足しないよう、shared_pool_size
の値を適切に設定するのも効果があります。
(10g で動的 SGA ならば sga_target に注意してください。)
メモリ量にも制限があり、かつ、SQL の再利用化も難しい場合には、
定期的に共有プールを FLUSH することを運用に盛り込むことも検討してくだ
さい。ただし、共有プールの FLUSH 自体も負荷が高い処理なので、間違って
も処理のピーク時には実行されません様に。
続きはまた来週。
ストーブのありがたみをひしひしと感じる 茅ヶ崎にて