待ちイベントに関する検証 その4

<待ちイベントに関する検証 その4>
ペンネーム: ダーリン

【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 自体も負荷が高い処理なので、間違って
も処理のピーク時には実行されません様に。

続きはまた来週。

ストーブのありがたみをひしひしと感じる 茅ヶ崎にて