buffer busy waits

特定ブロックにアクセスする際プロセスは該当ブロックに対してバッファ・ロックを獲得します。特定ブロックを変更する際も、該当ブロックに対してバッファ・ロックを排他モードで獲得します。また、特定ブロックに対しては読取時バッファ・ロックを共有ロックモードで獲得します。

もしプロセスAがブロックXに対してバッファ・ロックを獲得している状態で、プロセスBが同一のブロックに対してアクセスをした場合バッファ・ロックを獲得できない為待機します。その時、発生する待機イベントがbuffer busy waits待機イベントです。

buffer busy waits待機イベントが最も多く確認されるのは、同時に複数のプロセスが同一のブロックに対してインサートやアップデートをする場合です。インサートやアップデートの作業は該当ブロックに対してバッファ・ロックを排他モードで獲得することを要求します。複数のプロセスが同時に同一のブロックに対してバッファ・ロックを排他モードで獲得する場合には、バッファ・ロックの競合が発生し、buffer busy waits待機イベントによる待機が起こります。

buffer busy waits待機イベントの待機パラメータは以下の通りです。

• P1 ： ファイル番号
• P2 ： ブロック番号
• P3 ： Oracle10gではブロック・クラスを意味し、Oracle9iでは原因コードを意味します。

標準の待機時間は１秒です。排他モードでバッファ・ロックを獲得するため、buffer busy waits待機イベントにて１秒待機した後に前プロセスがまだ待機中の場合、次の待機時間は３秒になります。特定ブロックを読もうとするセッションは必ずバッファ・ロックを獲得しなければなりません。

ブロック・クラスによるbuffer busy waits待機イベントの発生原因は以下の通りです

ユーザーのアプリケーションにおいて、同時に複数のセッションが同一のブロックを変更するよう動作する場合、このようなブロックをホット・ブロックと言います。ホット・ブロックを解消する方法には、ユーザーアプリケーションの修正があります。アプリケーション修正を通じて同時に複数のプロセスが同一のブロックを変更しないようにします。

• PCTFREE値の調整：PCTFREEの値を大きくしてオブジェクトを生成すると一個のブロックに入る行の数が減り、その分ブロックの競合が減ります。
• 偏った索引の変更：索引のキーが右に偏っていると索引リーフノードに対する競合が発生します。シーケンスの値を利用して生成したキーの値を索引に使う場合が代表的です。逆キー索引を使ってキーの値を分散すればブロックの競合を減らすことができます。
• FLMを使用する：FLM(空きリストマネージメント)を使用する場合には、オブジェクトの ストレージの空きリスト属性の値を十分に大きくします。空きリストの値が1なら同時に複数のプロセスが同一のブロックにインサートを実行した際に、ブロックの競合が起きます。空きリスト属性の値は同時にインサートを実行するプロセスの最大数程度を推奨します。
• パーティションニング：セグメントパーティションニングを通じてブロックを任意に分散できます。たとえばハッシュパーティションと同じ方法で一つに集まっていたブロックを物理的に分散することでブロックの競合を減らす効果が得られます。

FLMを使用する場合、ストレージの空きリスト属性の値を十分大きくします。もしOPSや RAC環境なら空きリスト・グループ属性の値をインスタンスの数と同じにします。空きリストグループ属性の値を指定するとセグメントヘッダー・ブロックと別途に空きリスト・ブロックを使うので、その分セグメントヘッダー・ブロックの競合が減ります。

ASSM(自動セグメント領域管理)を使う場合は3段階のビットマップ・ブロックから競合が発生します。ビットマップ・ブロックから発生するブロックの競合はチューニングを通じて改善できないので、アプリケーション修正を通じて過渡な同時DMLを減らします。

 

大規模の同時DMLの場合、ASSMはFLMに比べて若干のオーバーヘッドを持っています。しかし、ほとんどの場合でこのようなオーバーヘッドは無視できる程度です。ASSMはFLMに比べて様々なメリットを提供するだけでなく、RAC性能の最適化にも貢献します。

AUM(自動UNDO管理)を使用する場合にはUNDOヘッダーからのブロック競合は滅多に発生しません。手動モードのロールバックセグメントを使用する場合はロールバックセグメントの数を十分大きくすることによってUNDOヘッダーブロックに対する競合を減らすことができます。

 

次の図はbuffer busy waits待機現象が過渡に発生する状況をMaxGaugeを使ってモニタリングした結果です。

 

buffer busy waits待機現象の発生理由を分析した結果、様々なプロセスが同時に同一のブロックを変更する過程でブロック競合が発生
したことが確認されました。つまり、次の図のように複数のセッションから特定の表に対する更新が行われたのが原因でした。

 

ブロックをランダムに分散させるために次のようにハッシュパーティションを適用しました。

-- t_buffer_busy_waits表
-- ハッシュパーティションを適用して行を任意のブロックに分散                   
CREATE TABLE t_buffer_busy_waits                                                
(                                                                               
	id		NUMBER,                                                             
	name		VARCHAR2(10)                                                    
)                                                                               
PARTITION BY HASH(id) PARTITIONS 100;

次の図はハッシュパーティションを適用した後、同一アプリケーションをMaxGaugeを使ってモニタリングした結果です。buffer busy waits待機イベントの発生が減少したことが確認できます。

V$SYSTEM_EVENTを通じて性能改善の前後のbuffer busy waits待機イベントの待機時間を計算した結果、buffer busy waits待機イベントの待機時間が4,120秒から 1,214秒に減少したことを確認しました。

buffer busy waits(id 130)待機イベントは 10g以後でイベント名がread by other sessionに変更されました。

分析対象のインスタンスは、アクティブセッション数が平均20～30個に維持できていますが、特定時点で300個以上まで急増する現象が発生しました。

アクティブセッションの 推移グラフは下図の通りです。アクティブセッションが増えた時点の待機イベントのリストを見ると、アイドルイベント(=SQL*Net Message…)を除いて、buffer busy waits待機イベントの待機が一番長く(1199秒)発生することが分かります。

 

アクティブセッションとbuffer busy waits待機イベントの相関関係を調べるために２つのグラフを確認すると、増減の推移が一致することが分かります。

 

つまり、アクティブセッションの急増現象はbuffer busy waits待機イベントの発生が原因だと推測できます。

 

該当時点のアクティブセッションリストを確認してみると、buffer busy waits待機イベントを待機するセッションはSUBSCRIBEという表にすべてインサートしており、buffer busy waits待機イベントのP3値のidは220番です。ID 220番は同一ブロックの変更時に、発生するバッファロック競合現象です。

インサートによるbuffer busy waits待機イベントの競合現象はたいてい不適切な空きリストの値によって発生します。複数のセッションが同時に同じ表にインサートする場合、セグメントの領域が急に拡張され、その時セグメントに対してインサートを実行するプロセスの数に比べてプロセスの空きリストの数が小さすぎる場合は、buffer busy waits待機イベントが発生します。

実際にbuffer busy waits待機イベントを待機するセッションの発生リストで確認して見ましょう。

 

buffer busy waits待機イベントで待機中に、ある瞬間HWエンキューを待機することが確認できます。

buffer busy waits待機イベントとHWエンキューイベントは空きリストと関連がありますので、インサートをする対象表のSUBSCRIBEの空きリスト、空きリストグループ属性の値を確認してみます。

 

上記のようにSUBSCRIBE表は空きリストの値が1に設定されています。

FLM(空きリスト管理)でセグメントを管理する場合、Oracleは基本的に一つのマスター空きリストを割り当てますが、もしセグメントを生成する時、空きリストの値を1にすると、マスター空きリストがプロセス空きリストに活用されます。一つのマスター空きリストだけを生成するため、同時に様々なプロセスが同一のブロックを使うことになり、これによってbuffer busy waits待機イベントが発生します。

また、マスター空きリストには新規に確保したフリーブロックが優先的に保存されますが、個別のプロセスがフリーブロックを必要とする時点でマスター空きリストから必要とする数のフリーブロックを取得します。ただ、マスター空きリストにフリーブロックがない場合は、HWロックを獲得し、HWMを利用してフリーブロックを確保します。この過程でHWエンキューが発生します。

つまり、複数のセッションが同時にインサートを実行する表の空きリスト属性値が1だったのでバッファロック競合が発生し、これによってアクティブセッションが急増しました。この場合、問題になる表の空きリストの値を同時に実行されるプロセスの数を考えて複数の空きリストを使うように表を再生成するか、ASSMの使い方を考慮して解決することができます。