Advanced Oracle Troubleshooting Guide, Part 7:LatchProfを使用してラッチホルダの統計情報をサンプリング


http://blog.tanelpoder.com/2008/07/09/advanced-oracle-troubleshooting-guide-part-7-sampling-latch-holder-statistics-using-latchprof/
の翻訳です。

本文書に関する指摘については、原文著者のTanel Poderさんではなく、渡部まで御連絡お願いいたします。

注意事項


  • 自動翻訳に手を加えたレベルの翻訳と理解してください
  • 意味をとりやすくするために追加した箇所については、《…》で囲っています。
  • 訳がわからない、または、自信がない箇所は★をつけています。


日本語訳:渡部 亮太 (WR at Csus4 dot net)

履歴


  • 2013-12-12: 初稿up


Advanced Oracle Troubleshooting Guide, Part 7:LatchProfを使用してラッチホルダの統計情報をサンプリング

休暇から戻ってから非常に忙しかったので、しばらく投稿がなかった。しかし、私は待っただけの価値があることを願い、平易なSQLとSQL\*Plusを使用してラッチ競合の問題をdiggingするツールLatchProfをプレゼントする。

私はまだ忙しいので、簡単にします。

LatchProfはWaitProfに似たスクリプトです。
V$LATCHHOLDERからラッチホルダー統計をサンプリングするだけです。
V$LATCHHOLDERにはSID列(ラッチホルダのセッションID)が含まれているので、ラッチをヒットしている人を見つけることができるようになる。(V$SQLを定期的にスキャンする安っぽい監視ツールが、自らライブラリ?キャッシュ?ラッチ競合を引き起こすことを証明する1つの方法★)

いつの日か、私はこれについて長く書きたいと思う??が(そして、スクリプト自体を確認してください)、構文をここに示します:

@latchprof <what> <sid|%> <latch|%|childaddr> <#samples>

ここでパラメータの定義は以下のとおりです。

1) 選択する列は、実際には、V$LATCHHOLDERの列にマップされます。だから、例えばそこに、SID、名前、LADDRを使用することができます。
2)SIDは、SID番号か、すべてのSIDが保持しているラッチを見るための%のどちらかである
3)latch_nameは、全てのラッチに対する%、または、その名前にlibraryを含むすべてのラッチを見るための%library%のようなLIKE文字列、実際のラッチアドレスのいずれかになります!これは、特定の子ラッチを監視することを可能にします。
4)取得するサンプル数。私は通常、出発点として100 000サンプルを使用します。

(waitprofも同じだが)latchprofは決してsleepしないので、出来る限り多くのCPUリソースを消費します。これは、サンプリング精度のために良いですが、1つのCPUを消費します。(よって、少なくとも十分な予備のCPUキャパシティを持つマルチプロセッサシステム《が必要です》)だから、あなたは一度に長時間これを実行したくない。(Jonathan Lewisによる素敵なトリックを使って、あなたは、短い待ち時間を実装するかもしれない)。

私のテストキットにおいて、約1秒間で通常100Kサンプル実行される。(100kHz以上のサンプリングレートを与えている :) 。これは、ハードウェアやOracleのバージョン、どんなプロセスパラメータかによって異なります。(V$LATCHHOLDERは繰り返しプロセスのステートオブジェクトを介する★iterate throughので。 プロセス数が増えれば増えるほど、より時間がかかる)

とにかく、我々はパフォーマンスの問題があり、待機インタフェースを使用して、あるセッションに対するキャッシュ?バッファ?チェーン ラッチの待機が不当に高いと判断しているとしましょう。

今、一つの方法は、(X$BH.HLADDR = V$LATCH_CHILDREN.ADDRマッピングを使用して)どのブロックがラッチで保護されているものを見ること、X$BH.TCHのtouchcountにより最もホットなブロックを把握しようとすることによって、この問題に関係するデータベースのブロックを識別することであろう。しかし、それは私たちをどこに導くのでしょうか?私たちは、そのブロックが属するセグメントに見つけるだろう。しかし、それは、この問題の原因となっているトラブルメーカーが誰がを私たちが本当に理解する助けにはならない。

そこで我々は、別のアプローチを使用する – 最もラッチを保持しているセッションを参照するため、V$LATCHHOLDERをサンプリングする:

ちょうどデモのために、(SIDを保持することにより、ブレークダウンされていない)一般的なlatchholderステータスを一覧にすることから始めます。

SQL> @latchprof name % % 100000
 
NAME                                 Held       Gets  Held %     Held ms Avg hold ms
\------------------------------ ---------- ---------- ------- ----------- -----------
cache buffers chains                21248      17418   21.25     267.725        .015
simulator lru latch                  7192       7169    7.19      90.619        .013
simulator hash latch                   66         66     .07        .832        .013
enqueue hash chains                     4          4     .00        .050        .013
ges resource hash list                  3          3     .00        .038        .013
process allocation                      2          2     .00        .025        .013
library cache                           2          2     .00        .025        .013
name-service namespace bucket           2          2     .00        .025        .013
name-service memory objects             1          1     .00        .013        .013
ges caches resource lists               1          1     .00        .013        .013
library cache pin                       1          1     .00        .013        .013
 
11 rows selected.

はい、キャッシュ?バッファ?チェーンのラッチが大部分を占めていることがわかります。Held列は、ラッチが保持されていたサンプル数を意味します。Get列は、サンプリング時間の間にそのラッチがおよそ何回取得されていていたかが表示されます。
我々は100000のサンプルを取り、そのうちの21248キャッシュバッファチェーンラッチが保持されていたので、このラッチは時間の21.25%忙しかった(保持されていた)と結論付けることができます。サンプリングを約1.3秒間継続し、1.3秒のそれ《状態》を推定できるので、キャッシュ?バッファ?チェーンのラッチが大体267ミリ秒保有する状態にあった。《ラッチを》取得した数を知っているので、ミリ秒単位での平均ラッチホールド時間を計算することができます。これは、サーバのCPUが不足状態にあるか、誰かがX$KSMSPをスキャンしている時に起こりうることのような、非常に長いラッチの保持を検出するのに役立つ可能性があります。現在の推定値である、ラッチホールドあたり15マイクロ秒はそんなに悪くはありません。(10000以下のような非常に少数のサンプルを用いると、平均保持時間の推定値が信頼できなくなることに注意してください)??

とにかく、今、ラッチを保持している個々のセッションをブレークダウンしてみましょう:

SQL> @latchprof sid,name % % 100000
 
       SID NAME                                 Held       Gets  Held %     Held ms Avg hold ms
\---------- ------------------------------ ---------- ---------- ------- ----------- -----------
        81 cache buffers chains                14916      14915   14.92     187.942        .013
        81 simulator lru latch                  6797       6745    6.80      85.642        .013
        85 cache buffers chains                 6195       2284    6.20      78.057        .034
        85 simulator lru latch                   524        506     .52       6.602        .013
        81 simulator hash latch                  121         85     .12       1.525        .018
       112 name-service namespace bucket           2          2     .00        .025        .013
       100 enqueue hash chains                     2          2     .00        .025        .013
        98 library cache                           2          2     .00        .025        .013
       112 process allocation                      2          2     .00        .025        .013
       112 ges caches resource lists               1          1     .00        .013        .013
       112 enqueues                                1          1     .00        .013        .013
       104 messages                                1          1     .00        .013        .013
       100 cache buffers chains                    1          1     .00        .013        .013
       100 active service list                     1          1     .00        .013        .013
       100 enqueues                                1          1     .00        .013        .013
        85 simulator hash latch                    1          1     .00        .013        .013
       100 library cache                           1          1     .00        .013        .013
 
17 rows selected.

セッション81と85が、CBCラッチをもっともヒットしていることがわかります。SID 85が悪いパフォーマンスに出くわし、この問題を調査するために私達を導いたとしましょう??。(あらためて・・・デモが目的です)

まともなOracleデータベースには、何千(またはそれ以上)CBC ラッチがあるので、もちろん唯一の集約されたラッチ名レベルの統計を見るのでは十分ではありません。セッションは、1つの子ラッチで競合するか?それとも、異なる多くのCBCラッチを使用し、全く衝突しない?これはLatchProf助けることができる質問です。

ラッチアドレス(子ラッチアドレス)をpicture《結果、調査対象ぐらいの意味か》に含め、"キャッシュ"に関連するラッチだけを照会してみましょう。多数の行が返されますので、最初に表示される結果が最も重要なので、結果が1ページ程度表示されたあと、通常、Ctrl + Cキーを押します。(結果は、最も忙しいものが最初になる順序に並べられています)

SQL> @latchprof sid,name,laddr % cache 100000
 
       SID NAME                           LADDR          Held       Gets  Held %     Held ms Avg hold ms
\---------- ------------------------------ -------- ---------- ---------- ------- ----------- -----------
        81 cache buffers chains           41AACEEC      15061      15017   15.06     186.756        .012
        85 cache buffers chains           41B2C37C         34         34     .03        .422        .012
        85 cache buffers chains           41B85D64         31         31     .03        .384        .012
        85 cache buffers chains           41BCF7FC         30         30     .03        .372        .012
        85 cache buffers chains           41B415EC         28         28     .03        .347        .012
        85 cache buffers chains           41BCA658         25         25     .03        .310        .012
        85 cache buffers chains           41B4E738         25         25     .03        .310        .012
        85 cache buffers chains           41AE4694         25         25     .03        .310        .012
        85 cache buffers chains           41AB02E0         25          9     .03        .310        .034
        85 cache buffers chains           427FA1F8         24         24     .02        .298        .012
        85 cache buffers chains           427F6E04         22         22     .02        .273        .012
        85 cache buffers chains           41B78D94         22         22     .02        .273        .012
        85 cache buffers chains           41BA2220         21         21     .02        .260        .012
        85 cache buffers chains           41BEDA68         21         21     .02        .260        .012
        85 cache buffers chains           41BC8D1C         21         21     .02        .260        .012
        85 cache buffers chains           41B486BC         20         20     .02        .248        .012
        85 cache buffers chains           42B99698         19         19     .02        .236        .012
        85 cache buffers chains           41AE6EA8         19         14     .02        .236        .017
        85 cache buffers chains           41B1F230         19         19     .02        .236        .012
        85 cache buffers chains           41B09E44         19         17     .02        .236        .014
        85 cache buffers chains           41AE7A88         19         19     .02        .236        .012
        85 cache buffers chains           41B69E98         18         18     .02        .223        .012
[...snip...]

そして、出来上がり。SID 85がたくさんの異なるCBCラッチをヒットするように、正常に動作している一方で(100Kのサンプリング中に、単一の子ラッチの取得数が34を超えていないことがわかる)、SID 81が狂乱した方法で1つの子ラッチをヒットしてしていることがわかる。
興味深い。
今、子ラッチのアドレス(LADDR)がわかったので、他のセッションが影響を受ける可能性かどうかを確認するためにその子ラッチを監視するよう、クエリを絞り込むことができます。

SQL> @latchprof sid,name,laddr % 41AACEEC 100000
 
       SID NAME                           LADDR          Held       Gets  Held %     Held ms Avg hold ms
\---------- ------------------------------ -------- ---------- ---------- ------- ----------- -----------
        81 cache buffers chains           41AACEEC      14058      14047   14.06     177.131        .013
        85 cache buffers chains           41AACEEC         18         18     .02        .227        .013

今のところ、狂乱したSID 81と被害者のSID 85を除いた他の誰も、ラッチを争っていないように見えます。(そうでなければ、そのラッチに対してすくなくとも数回ラッチ保持が成功する様子を見ることができると期待される)

だから、ここでは、SID 81が本当に明らかなトラブルメーカーである。それが何をしているかチェックしてみましょう:

SQL> select sql_text from v$sql where hash_value = (select sql_hash_value from v$session where sid = 81);
 
SQL_TEXT
\---------------------------------------------------------------------------------------------------------
select count(*) X from kill_cpu connect by n > prior n start with n = 1
 
1 row selected.

これは動作しているJonathan Lewisのkill_cpuスクリプト です。connect by を使用して同じデータブロックをhammeredさんざん叩くものです。
(これを動作させるためには、9i以上では_old_connect_by_enabled = TRUEを設定する必要です :-)

私は(うまくいけば)近い将来に、いくつかのより多くのユースケースと、LatchProfスクリプト拡張版についてのブログを書く予定です。

このスクリプトは、ラッチ保持者を検知することにあらためて注意してください。最も多くラッチを保有しても、そのラッチが最も多く待たせていたというわけでは必ずしもありません。まだ、例えばSnapperWaitProfを使って、待機インタフェースから診断を開始する必要があります。これらのツールがかなりのラッチ待機を示した場合にのみ、ラッチにドリルダウンする必要があります。最近Anjo Kolkは(とてもよく)言っている。ラッチ競合は症状であり問題でなない。根本的な原因は、どこか別の場所(アプリケーションかもしれない)である。それを修正したらラッチ競合は消えてなくなるだろう。

更新:私は、拡張データを取得するためにX$表を使用し、ラッチの所有者についてのさらに詳細な情報を与えることができる、より詳細なラッチプロファイリングツール(latchprofX)を公開しています。この資料に加えてリンクをお読みください。

http://blog.tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block/