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

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

【latch: shared pool/library cache】補足

あっという間に今年も1月が終わりです。気が付けば”今年もあともう少し…”
なーんてことにならないように気を付けなくちゃ。

前回、前々回と shared pool と、library cache のラッチについてお話をし
てきました。これらのラッチを発生させるために、同時に複数のセッションか
らSQLを実行したり、あるいは、共有プールの空きを減らしてみたりしました。

ここまできて、これらのラッチと共有プールの使用率の間にどんな関係がある
のか気になってきました。共有プールがいっぱいになると、SQLやパッケージ
などの情報が追い出されたり、新たに読み込まれたりします。ということは、
共有プールがいっぱいになるまで、shared pool や library cache のラッチ
待ちは発生しないのでしょうか。

そこで、今回は、共有プールの使用率と、上記のラッチ待ちの状態を計測して
みました。

そのときのイメージをざっくりと表現すると以下のようになります。

                  共有プール使用率 の変化(≒ 300MB)
                 ~~~~~~~~~~~~~~~~~~~~~~~~
  %
     |
 100 |
     |                         *  *  *  *  *
     |                      *
     |                   *
     |                *
  50 |             *
     |          *
     |       *
     |    *
     | *
   0 +---------------------------------------------------
                                                    TIME
                 shared pool ラッチ待ちの変化
                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 count
     |
     |
   1M|                           *  *  *  *
     |                         *
     |                       *
     |                     *
 0.5M|                   *
     |                 *
     |               *
     |             *
     | *  *  *  *
   0 +---------------------------------------------------
                                                    TIME
                 library cache ラッチ待ちの変化
                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 count
     |
     |
 0.5M|                         
     |
     |                             
     |            *
     |
     |                            *  *
     |                   
     |    *     *    *         *        *  *
     | *     *           *  *
   0 +---------------------------------------------------
                                                    TIME

今回計測した状況では、共有プールの使用率が上昇すると、shared pool のラ
ッチ待ちも明らかに増加してくることが確認できました。
また、今回の検証結果においては、shared pool のラッチ待ちが増加し始める
のは、共有プールの使用率が 40% を超えたあたりであることも確認できました。

shared pool のラッチ待ちがどの程度発生するようになると処理のパフォーマ
ンスに影響してくるかを具体的に示すことは難しいのですが、SQLの再利用率
を上げるなどの対応をしないで、「とにかく共有プールを大きくしてしまえ。」
という考え方では、共有プールの使用率が上がったときに shared pool のラ
ッチ待ちを増大させることになりそうです。

一方 library cache のラッチ待ちと共有プールの使用率との間には、明確な
相関関係が見られませんでした。同時実行数のほうが影響するのかも知れません

ちなみに、今回の環境では動的SGAを使用しており、計測の最後のほうで共有
プールが拡張していました。
共有プールの空きが少なくなるとすぐに拡張するわけではなく、しばらくは
LRU 管理に従い、使用していない領域を解放するようです。また、拡張する
際には、一部のSQLの情報を解放するようで、共有プール上に存在する SQL
の数が一旦減少するような動きをします。

                 共有プールのサイズとSQL COUNT
 SQL            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 count(*)                                       共有プールサイズ(x)
     |                                                  |
20000|              *  *  *  *  *     *     *     x  x  |  312M
     |           *                 *     *              |
     |        *                                *     *  |
     |     *                                            |
19000|  *                                         *     |
     |                                                  |
     |  x  x  x  x  x  x  x  x  x  x  x  x  x  x        |  308M
     |                                                  |
     |                                                  |
     +---------------------------------------------------
                                                    TIME

ただし、この間に shared pool や library cache のラッチが大きく変動する
様子は見られません。共有プールの拡張自体は他の処理に影響を与えないんで
しょうか。少なくとも今回は影響なかったようです。

共有プールの使用率を無駄に上げないためには、SQLを再利用できるようにす
ることが大切です。もし、現在 SQL の再利用が十分でないと認識されている
環境では注意してください。

今週はちょっと短いですが、この辺で。
iPod 争奪戦に敗れた… 茅ヶ崎にて