latch: cache buffers chains - 日本エクセム株式会社 Oracle待機イベント情報

基本情報

　バッファ・キャッシュを使うためハッシュ・チェーンを探索したり変更を加えようとするセッションは、必ずそのチェーンを管理しているcache buffers chainsラッチ(以降、CBCラッチ)を獲得しなければなりません。このラッチを獲得する時に、競合が発生するとlatch: cache buffers chains待機イベントで待機します。

　Oracle9i以降からは読み取り専用を保護するため、チェーンを探索する場合にはCBCラッチを
共有モードで獲得し、競合を減らせるようになっています。これには一つ注意すべきことがあります。理論的にはSELECT文が同時に発行された場合は、CBCラッチを共有できるため、CBCラッチ競合が発生してはいけませんが、実際テストをしてみるとラッチ競合は相変らず発生します。その理由は、バッファ・ロックと関連があります。読み取り専用で作業を行うために共有モードでラッチを獲得した場合、バッファをスキャンする時はバッファ・ロックを共有モードで獲得しますが、バッファ・ヘッダーの情報を一部変更するため、バッファ・ロックを獲得する時と解除する時には、ラッチを排他モードに変更する必要があります。この過程で競合が発生し、これによっlatch: cache buffers chains待機イベントで待機します。

　CBCラッチ競合が発生する代表的なケースは2つあります。

１）非効率なSQL
２）ホット・ブロック

非効率なSQL

　非効率なSQLは、CBCラッチ競合を引き起こす最も重要な原因です。例えば、同時に複数のセッションが広い範囲の索引や広い範囲の表に対してスキャンする場合、CBCラッチ競合が多く発生します。

　CBCラッチ競合が発生した場合、正確な競合の原因を調べることが大事です。ラッチの競合がホット・ブロックで発生しているのか、それとも非効率なSQLによって発生しているのかを判断するための、最も確実な根拠はSQLそのものにあります。従って、効率が悪いSQLが作成されていると判断できる根拠がある場合は、SQLをチューニングすることで問題を解決することができます。

　もし、SQLに対する情報がない場合は、ホット・ブロックによる問題なのか、非効率なSQLによる問題なのかを判断できる別の方法があります。まずは、V$LATCH_CHILDRENビューで子CBCラッチに該当するCHILD#とGETS、SLEEPS値を比較して、ある特定の子ラッチに使用回数と競合が集中していないかを確認する方法です。下記のコマンドでSLEEPSの回数が多い子ラッチを確認します。

SQL>
select * from
(select child#, gets, sleeps from v$latch_children 
          where name = 'cache buffers chains'
          order by sleeps desc
) where rownum <= 20

　もし、ある子ラッチのGETS、SLEEPS値が他の子ラッチに比べて非常に高いと、該当ラッチが管理するチェーンにホット・ブロックがあることが推測できます。上記のテストを実行した結果、ある特定のラッチに重なる現象は見られないためホット・ブロックによる問題はないと判断します。

    CHILD#       GETS     SLEEPS
---------- ---------- ----------
       329      81160         78
       940      79773         74
       232      62792         69
       791      99123         68
       905      99185         68
       408      80687         65
       259     101793         62
       611      82187         62
       466      99870         60
       839      79744         60
       ...

　ホット・ブロック発生を判断するもう一つの方法は、V$SESSION_WAITビューからラッチのアドレスを確認して比較することです。CBCラッチの場合V$SESSION_WAIT.P1RAWが子ラッチのアドレスに該当します。万一V$SESSION_WAITビューで同じラッチのアドレスが過度に発生しているようであれば、ホット・ブロックによる競合と解釈することができます。

ホット・ブロック

　SQLが適切にチューニングされたにもかかわらず、CBCラッチの競合が解消しない場合があります。SQLが少数の特定ブロックを何回もスキャンする形で作成されたアプリケーションでは、様々なセッションで同時にこのSQLを実行する場合、ホット・ブロックによるCBCラッチ競合が発生します。

　V$LATCH_CHILDRENビューで、ある特定の子ラッチだけ重なって使用されていることを確認すれば、ホット・ブロックによるラッチ競合が発生していたと判断できます。または、V$SESSION_WAITビューのP1RAWカラムの値をラッチのアドレスとして検索時に利用しても構いません。

　下記のコマンド実行結果では、CHILD# 569、827、178の3つの子ラッチが集中的に使われており、これによってラッチ競合が発生したことを確認できます。

SQL> 
select * from
(select addr, child#, gets, sleeps from v$latch_children 
        where name = 'cache buffers chains'
order by sleeps desc
) where rownum <= 20
;

ADDR                 CHILD#       GETS     SLEEPS
---------------- ---------- ---------- ----------
C0000000CDFF24F0        569   10500275      11298  <-- ラッチを集中して使用
C0000000CE3ADDF0        827    5250508       8085
C0000000CDF18A98        178    5250192       4781
C0000000CDEDB6E8         68       3786         17
C0000000CE3CBEE0        881       2121          8
C0000000CE359430        675       1768          1
C0000000CDEB6230          1        235          0
C0000000CDEB6B18          2        171          0
C0000000CDEB7400          3        390          0
C0000000CDEB7CE8          4        192          0
C0000000CDEB85D0          5        151          0
...

　X$BHビューを利用すれば、どのブロックがホット・ブロックになっているかを確認することができます。ユーザーオブジェクト(表、索引)でありながら、接触した回数(TCH)が高いブロックを抽出し、ホット・ブロックを確認します。

　下記の結果では、CBC_TEST_IDX索引の45918、45919、100ブロックでほとんどの競合が発生していることが推論できます。

SQL> select hladdr, obj,
(select object_name from dba_objects where
     (data_object_id is null and object_id = x.obj) or
      data_object_id = x.obj and rownum = 1) as object_name,
       dbarfil, dbablk, tch from x$bh x
where hladdr in 
    ('C0000000CDFF24F0', 'C0000000CE3ADDF0', 'C0000000CDF18A98')
order by hladdr, obj;

HLADDR                OBJ      OBJECT_NAME    DBARFIL     DBABLK    TCH
------------------- -------- ---------------- ---------- ---------- ------
C0000000CDF18A98       57          OBJAUTH$        1         43911     1
C0000000CDF18A98       73          IDL_UB1$        1         27025     1
C0000000CDF18A98      181   C_TOID_VERSION#        1         26792     1
C0000000CDF18A98      181   C_TOID_VERSION#        1         14244     1
…
C0000000CDF18A98    55236      CBC_TEST_IDX        4          45919  130
…
C0000000CDFF24F0    55236      CBC_TEST_IDX        4          45918  130
C0000000CE3ADDF0        2              IND$        1           7933    1
C0000000CE3ADDF0        2              IND$        1          60455    9
C0000000CE3ADDF0       18              OBJ$        1          29623    1
...
C0000000CE3ADDF0    55236      CBC_TEST_IDX        4            100  130

待機パラメータと待機時間

待機パラメータ

・P1:ラッチのアドレス
・P2:ラッチ番号(V$LATCHNAME.LATCH#と同一)。
　　  ラッチ番号に該当するラッチ名を確認するためには、下記のSQLを実行します。
　　  SELECT * FROM v$latchname WHERE latch# = &p2_value;
・P3:試行回数。ラッチを獲得するため、プロセスがトライした回数。

待機時間

　待機時間は、指数的に増加します。

チェックポイントとソリューション

非効率なSQLのチューニング

　非効率なSQLをチューニングして論理読取りを減らすと、バッファ・キャッシュに対するアクセスが減るため、それだけCBCラッチ競合も減少します。

ホット・ブロックの分散

　ホット・ブロックのためCBCラッチ競合が発生した場合は、ホット・ブロックを分散することによって競合を減らすことができます。ホット・ブロックを分散させる方法は下記の通りです。

・PCTFREEを高くするか、小さいブロックを使うことでブロック競合を減らします。
　PCTFREEを高くすることと小さいブロックを使うことは、一つのブロックに含まれる行数を減らして
　ブロック競合を避けるといった面では、基本的に同じ方法です。この方法は確実にブロック競合を
　減らす効果はあるが、それだけ管理しなければならないブロック数が増え、
　システムに影響を与える可能性があります。
　例えば、ブロック数が増え、同じSQLでも非常に多くのブロックをスキャンしなければならないため、
　性能が低下します。ホット・ブロックによるラッチ競合は減ったものの、
　スキャン回数が増え、ラッチ競合が増加します。従って、テストによる十分な検証を推奨します。

・パーティショニング機能を使って、物理的に行を他のブロックに入れるようにします。
　この機能を使うと、問題になりうる行を物理的に他のブロックに分散させるため、
　ラッチ競合を避けることができます。
　しかし、表にこの機能を適用する場合には索引のクラスター化係数が非効率になり、
　索引範囲スキャンによるデータのスキャン速度が低下する可能性もあります。
　従ってパーティショニングを適用する場合にもその影響については十分に考慮する必要があります。

・問題になりうるブロックの行だけを削除した後、再挿入を行います。
　この方法は表のみにけ可能です。問題になるブロックと該当ブロックに含まれた行のROWIDが
　正確に分かれば、該当行を削除した後再挿入して各行が別々のブロックに分散できます。
　ブロックのダンプとDBMS_ROWIDパッケージを利用すればホット・ブロックになったROWIDが分かります。
　この方法は表の属性を変更しないため、最も理想的な方法です。
　しかし、ホット・ブロックが固定されていなくて、SQLのWHERE条件句によって毎回変わる場合は適用できません。
　また、索引に対してはこの方法は使用できません。

　表でCBCラッチの競合が発生した場合は、比較的簡単に解決できます。行を分散させるために、様々な方法があるためです。一方、索引で競合が発生した場合は問題を解決するのが非常に難しいです。なぜなら、ソートされた形で格納されている索引の固有の特性のため、任意のブロックに分散させることが不可能な場合があるためです。この場合にはPCTFREEを高くするか、ブロックを小さくする方法以外には解決策はありません。この場合にブロック数が増え、逆にラッチ競合が増える場合もあるため適用時には必ず注意が必要です。

豆知識

バッファ・キャッシュの構造

　Oracleは物理I/Oを最小化するために、最近使われたブロックに対する情報をメモリー領域に保存します。このメモリー領域をバッファ・キャッシュと言います。バッファ・キャッシュは共有プール、REDOログ・バッファと共にSGAを構成する最も重要なメモリー領域の一つです。

　下記のコマンドでインスタンスのバッファ・キャッシュの大きさが分かります。

SQL> show sga

Total System Global Area  314572800 bytes
Fixed Size                   788692 bytes
Variable Size             144963372 bytes
Database Buffers          163577856 bytes
Redo Buffers                5242880 bytes

　Database Buffersの値がインスタンスの現在バッファ・キャッシュの大きさです。Oracleはバッファ・キャッシュを効率的に管理するためにハッシュ・チェーン構造を使います。ハッシュ・チェーンは共有プール内に存在し、Oracleの典型的なメモリー管理手法であるバケット→チェーン→ヘッダーの仕組みで構成されています。

　ハッシュ・チェーン構造の開始点はハッシュ表です。ハッシュ表はいくつかのハッシュ・バケットで構成されています。一つのハッシュ・バケットはハッシュ関数の結果です。Oracleはブロックのアドレス(DBA: Data Block Address。 File#とBlock#で構成されている)とブロック・クラスに簡単なハッシュ関数を適用した結果を利用してハッシュ・バケットを探していきます。ハッシュ・バケットには同じハッシュ値を持つバッファ・ヘッダーがチェーンの形で繋がっています。バッファ・ヘッダーはバッファに対するメタ情報を持っており、メモリー領域での実際バッファに対するポインタ値を持っています。ハッシュ・チェーン構造は共有プールに存在していますが、バッファに対する実情報はバッファ・キャッシュ領域に存在しています。

　ハッシュ・チェーン構造はCBCラッチで保護されます。特定のブロックをスキャンするセッションは、必ず該当ブロックに対するハッシュ・チェーンを管理しているCBCラッチを獲得しなければなりません。基本的には、一つのセッションだけが一つのCBCラッチを獲得できます。また、一つのCBCラッチはいくつかのハッシュ・チェーンを管理します。従って、同時に多数のセッションがバッファ・キャッシュを探索する場合は、CBCラッチを獲得するため競合が発生し、latch: cache buffers chains待機イベントで待機します。

　Oracle9iからは読み取り専用の作業に限ってCBCラッチを共有モードで獲得します。従って、同時に読み取り作業を実行するセッション間にはCBCラッチを共有することができます。しかし、前述したようにバッファに対してバッファ・ロックを獲得したり解除する時に、CBCラッチを排他モードに変える必要があるため、読み取り作業だけの場合にもCBCラッチ競合は相変らず発生します。

　下記のコマンドでCBCラッチ数を求めることができます。

SQL> select count(*) from v$latch_children where name = 
        'cache buffers chains';
  COUNT(*)
  ----------
       1024

　または、_DB_BLOCK_HASH_LATCHES隠しパラメータを確認しても同じ結果を得ることができます。ハッシュ・バケットの数は、_DB_BLOCK_HASH_BUCKETS隠しパラメータで確認可能です。

分析事例

不適切な索引カラムによる待機

　latch: cache buffers chains待機イベント発生の検証シナリオは下記の通りです。

・t_cache_buffers_chains_latch(type、name、id)表には16万件のデータが入力されており、
　この中でtype=4、name=’KIM’を満足するデータ件数は15万件です。
・idx_cache_buffers_chains_latch索引はtype、nameの2つのカラムで構成されています。
・10セッションが同時に(type=4、name=’KIM’、id=4)を満足するデータの件数を求めるSQLを
　idx_cache_buffers_chains_latch索引を使って実行します。
・セッションは実行中、latch: cache buffers chains待機イベントで待機します。

　同時に多数のセッションから下記のSQLを実行します。

　この状況で監視した結果が下記の通りです。latch: cache buffers chains待機イベントが最も発生していることが分かります。

　MaxGaugeのアクティブ・セッション・リストで、ほとんどのアクティブ セッションがlatch: cache buffers chains待機イベントで待機していることを確認します。

　このような状況でlatch:cache buffers chains待機イベントが発生する理由は次の通りです。

・idx_cache_buffers_chains索引を使ってデータを検索すると、16万件中の15万件のデータが条件を足します。
　この15万件に対して再び表へのアクセスが発生し、結果的に広い範囲のブロックに対して
　繰り返しランダムなアクセスが発生することになります。

・バッファ・キャッシュは検索の性能を向上させるために、ハッシュ・チェーンで管理されており、
　このチェーンを検索するためには必ずラッチを獲得します。
　しかし、10セッションが同時にラッチを獲得するために競合が発生し、
　latch: cache buffers chains待機イベントでの待機は避けられなくなります。

　SQLの検索条件の中、idカラムは選択度がいいので、索引使用時にはこのカラムを利用するようにします。

・既存のidx_cache_buffers_chainsにidカラムを追加します。
・15万回も表を検索していたのが、1回の検索で結果を得られるようになるため競合は顕著に減ります。

-- 既存のidx_cache_buffers_chains索引を削除
DROP INDEX idx_cache_buffers_chains;

-- 索引にidカラムを追加し再作成
CREATE INDEX idx_cache_buffers_chains 
ON t_cache_buffers_chains_latch(type,name,id);

　性能改善後のシステム監視結果は下記の通りです。latch: cache buffers chains待機イベントの待機時間が1133(cs)で、性能改善前の待機時間が3061(cs)だったことに比べると、3倍ほどの改善効果がありました。

過度な論理読取りによる待機

　同時ユーザー数が多いOLTPおよびWEB環境で、適切ではない索引の使用による過度なI/O発生は
性能面で深刻な問題を引き起こす場合が多いです。MaxGaugeを使用して、不適切な索引の使用による過度なI/O発生が引き起こす性能低下問題の原因を究明します。

性能低下区間の確認

　性能問題が発生したインスタンスで収集した稼動ログからグラフを確認すると、21時35分前後で「Active session」数、「Wait」イベントが、類似した推移で変化しています。

■「Active Session」の推移グラフ

■「Wait」イベントの推移グラフ（待機時間もしくは待機回数）

待機イベントの検出および分析

　アクティブ・セッションの急増による性能低下の原因を究明するため、問題時点(21時35分)の待機イベントの発生内容を確認します。

　「Value」タブでこの時点のトップ待機イベントを確認した結果、Idle Event(= SQL*Net message from client)を除いたトップ待機イベントはlatch freeであることが確認されます。

　アクティブ・セッションの急増に対するlatch free待機イベントの関連性を判断するために、待機イベントとの発生パターンを比較してみた結果、アクティブ・セッションの発生推移と非常に類似しており、問題時点に発生した待機イベントの待機時間の約53.4%(全体109.89秒中、58.69秒)を占めていることから、アクティブ・セッションの急増はlatch free待機イベントの急激な発生と関連があることが推論されます。

　実際に、同じ時間に詳細内容を表示しているセッション・リスト画面でも、latch free待機イベントがトップ待機イベントになっており、その中でもlatch: cache buffers chain待機イベントが多く発生していることが確認されます。

待機イベント発生原因

　latch free待機イベントの発生には様々な原因がありますが、一般的にlatch: cache buffers chain待機イベントが発生した場合には、ホット・ブロックがその原因であることが多いです。ホット・ブロックは全表走査よりは索引レンジ・スキャンで頻繁に発生します。解決策としてはSQLチューニングを実施し、索引を検索する範囲を減らす方法があります。SQLチューニングが不可能な場合にはブロック・サイズを減らしたりPCT FREE値を増加させ、ブロック当たり行数を減らすのも一つの方法です。

セッションおよびSQLの分析

　latch: cache buffers chain待機イベントの発生が多かった21時30分～21時40分で、latch freeを発生させたSQLリストを確認すると、同じパターンを持ったSQLであることが分かりました。

結論

latch: cache buffers chain待機,イベントの多発によるアクティブ・セッションの急増
　　　　　　　　　　　　　　　　↓
非効率的な索引スキャンによる性能低下の発生
　　　　　　　　　　　　　　　　↓
SQLの検索条件のカラムで索引スキャンのみを実行するように索引を再作成し、解決

非効率な索引の選択による待機Ⅰ

　性能問題が発生したインスタンスでアクティブ・セッションの推移を確認します。問題区間のアクティブ・セッションはlatch: cache buffers chain待機イベントで待機しており、アクティブ・セッション・リストのラッチのアドレスは16321267696であることを確認します。

　ラッチ競合が発生したラッチのアドレスをX$bhビューと結合して、どのオブジェクトのブロックなのか、どのブロックがTCHが高いかを確認します。

-- X$bhビューと結合し、ブロックを確認するSQL -- 
select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, 
       b.object_name, b.object_type
from   sys.xm$bh a, dba_objects b
where  (a.obj = b.object_id or a.obj = b.data_object_id)
and    a.hladdr = '00000003CCD2C7F0'
order by 4 desc

　同じラッチ・アドレスで検索した結果、いくつかのオブジェクト結果値が出てくる理由は、一つのラッチがいくつかのチェーンを管理しているためです。問題のインスタンスのCBCラッチ数は1024個で、ハッシュ・バケットの数は、254083個です。すなわち、問題のインスタンスで1個のCBCラッチは約249個のチェーンを管理している状況です。

　ABC$123索引が最もTCH値が高いため、CBCラッチの競合を発生させたブロックであることが推測されます。

　DBA_INDEXESビューを通じて、この索引の表を確認し、表の索引構成を確認します。

select index_name, table_name
from dba_indexes
where index_name = 'ABC$123';

　ABC表はABC_NUM + ABC_TIMで構成された一意索引のABC$PK索引とABC_TYPEで構成されたABC$123索引を保持しています。ラッチ競合で問題になった索引はABC$123索引です。

　競合が発生したセッションの下記のSQLのトレース結果を確認します。

-- SQL --
UPDATE ABC 
SET    version$=version$+1 ,
          type$='I' 
WHERE  ABC_NUM =:b2 
AND    ABC_TIM =:b1 
AND    ABC_TYPE='D'

　SQLのWHERE条件にABC$PKのカラムが含まれているにもかかわらず、範囲が広い索引が選択されたことが分かります。(赤く表示した部分から、不必要に174429ブロックをアクセスしていたことが確認されます。)ここで、ヒントを使用してABC$PK索引経由で実行されるように変更すれば、latch: cache buffers chain競合を解消することができます。

非効率な索引の選択による待機Ⅱ

　システムのCPU使用率のグラフが論理読取りや物理読取りと類似したパターンになっています。SQLの実行回数であるExecute Countは、平均500回程度を維持しており、SQLの実行が多くてCPU使用率が上がったとは考えにくいです。

　この区間の「Active Session」グラフの推移を確認すると、latch free待機イベントグラフの推移グラフと非常に似ていることが分かります。また、latch: cache buffers chain待機イベントを待機するセッションも確認されます。

　latch: cache buffers chain待機イベントはSQL実行時に、広い範囲を処理する時に発生します。また、db file sequencial reads待機イベントの発生推移も似ていることから、SQLが非効率な索引で索引スキャンが行われていたと考えられます。

　この事例は、セッションが実行しているSQLで統計情報が変更され、本来の索引の代わりに、非効率な索引が選択されデータにアクセスした場合です。

　例えば、下記のSQLでは、emp_idカラムにTO_NUMBERという関数を使用したため、該当索引を利用できずemp_nameカラムの索引を利用して実行されたと考えられます。

SELECT emp_id, emp_name, salary, emp_date, dept_id
FROM   emp
WHERE  emp_date >=:1
AND    emp_date <=:2
AND    TO_NUMBER( emp_id ) =:3
AND    emp_name like :4
AND    job_id =:5

　この場合は、次の通りにSQLを変更することを推奨します。

SELECT emp_id, emp_name, salary, emp_date, dept_id
FROM   emp
WHERE  emp_date >=:1
AND    emp_date <=:2
AND    emp_id =to_char(:3)
AND    emp_name like :4
AND    job_id =:5

　このように、latch: cache buffers chain待機イベントが発生した場合は、処理量が多いSQLを抽出し、処理量を減らすためにチューニングをする必要があります。