【ITニュース解説】Buffer Busy Wait and Read by Other Session in Oracle

Oracleデータベースを扱うシステムエンジニアにとって、データベースのパフォーマンスは非常に重要な課題の一つである。データベースがなぜ遅くなるのか、その原因を特定し解決することは、安定したシステム運用に直結する。ここでは、Oracleデータベースでよく発生するパフォーマンスのボトルネックとして、「Buffer Busy Wait（バッファビジーウェイト）」と「Read by Other Session（リードバイアザーセッション）」という二つの待機イベントについて、初心者にも分かりやすく解説する。

まず、Oracleデータベースの心臓部とも言える「バッファキャッシュ」の役割から説明しよう。バッファキャッシュは、データベースがディスクから読み込んだデータを一時的にメモリ上に保存しておく領域である。一度読み込んだデータはここに置かれるため、同じデータへの二度目以降のアクセスはディスクから読み直す必要がなくなり、高速に処理できるようになる。

Buffer Busy Waitは、このバッファキャッシュ内で発生する競合待機イベントである。具体的には、あるセッション（データベースへの接続と操作を行う単位）が、バッファキャッシュ内にある特定のデータブロックにアクセスし、そのブロックを「ピン留め」している間に、別のセッションが同じブロックにアクセスしようとすると発生する。ブロックがピン留めされている間は、他のセッションはそのブロックを操作できないため、処理が停止し待機することになる。たとえ同じブロック内の異なる行にアクセスしようとしていても、ブロック単位での競合が発生するため、Buffer Busy Waitとして計上される。

Buffer Busy Waitが発生するブロックの種類は多岐にわたる。主に以下の四つのカテゴリに分けられる。

一つ目は「データブロック」である。これは、ユーザーが作成したテーブルやインデックスなどの実際のデータが格納されているブロックを指す。データブロックでのBuffer Busy Waitは、いくつかの原因で発生する。一つは、クエリが適切にチューニングされていない場合である。不必要なブロックを大量に読み込むようなクエリは、それだけ競合の可能性を高める。対策としては、実行するSQLクエリを見直し、読み込むブロック数を最小限に抑えることが重要だ。

もう一つの原因は「ホットブロック」である。これは、非常に頻繁にアクセスされる特定のブロックを指す。多くのセッションが同時にこのホットブロックにアクセスしようとするため、ボトルネックとなりやすい。このようなブロックを持つテーブルは、常にバッファキャッシュに保持されるように設定することが有効な対策となる。

さらに、ホットブロックがバッファキャッシュに存在しない状況で複数のセッションが同時にアクセスしようとすると、そのブロックをメモリに読み込む最初のセッションが完了するまで、他のセッションは待機する必要がある。Oracle 10.1より前はこの状態もBuffer Busy Waitに含まれていたが、現在では「Read by Other Session」として区別されるようになった。これについては後述する。

ブロックサイズが大きい場合もBuffer Busy Waitの原因になりうる。大きなブロックはより多くの行を格納できるため、一つのブロックに対する同時アクセスが起こりやすくなる。対策としては、テーブルを再構築して、ブロック内の空き領域を増やす（PCTFREEの値を大きくする）ことで、新しい行が別のブロックに格納されるようにするか、もしくはより小さいブロックサイズを持つテーブルスペースにオブジェクトを移動させ、一つのブロックが保持する行数を減らす方法がある。

インデックスの競合もデータブロックのBuffer Busy Waitの原因となる。例えば、シーケンス番号のように単調増加する値を主キーに持つインデックスでは、新しい行が常にインデックスの最後のブロックに追加されるため、このブロックがホットブロックになりやすい。このような場合には、ハッシュパーティショニングを利用してインデックスデータを複数のパーティションに分散させるか、「リバースキーインデックス」を使用する方法が有効である。リバースキーインデックスは、インデックスキーのバイト順を逆転させて格納することで、データがブロックに均等に分散されるようにし、ブロックレベルの競合を減らす効果がある。

どのテーブルやインデックスでBuffer Busy Waitが多く発生しているかを確認するには、v$segment_statisticsというビューを利用するSQLクエリが有効である。このクエリを実行することで、どのオブジェクトが最もBuffer Busy Waitを引き起こしているかを特定し、具体的な対策を立てるための手がかりを得られる。例えば、インデックスが原因だと特定できた場合、ALTER INDEX [インデックス名] REBUILD REVERSE; のようなコマンドでインデックスをリバース形式に再構築できる。

二つ目は「データセグメントヘッダ」である。各テーブルやインデックスなどのセグメントには、その管理情報を格納するためのヘッダブロックが存在する。このヘッダブロックには、データがどこまで書き込まれたかを示す高水位標や、空きブロックを管理するフリーリストといった情報が含まれる。新しいデータが挿入されて高水位標が更新される際や、データが削除されてフリーリストが更新される際、また新たなデータ領域（エクステント）が割り当てられる際などに、このヘッダブロックへのアクセスが集中し競合が発生することがある。対策としては、セグメントをパーティション化して複数のヘッダブロックを持たせることで競合を分散させる、より大きなエクステントを使用することでエクステント割り当ての頻度を減らす、そして手動でフリーリストを管理する代わりにASSM（Automatic Segment Space Management）テーブルスペースを使用することが推奨される。ASSMはOracleが自動的にブロックの空き領域を管理してくれるため、フリーリストの競合を大幅に軽減できる。

三つ目は「UNDOヘッダ」と「UNDOブロック」である。データベースでは、トランザクションの変更を取り消すための情報（UNDO情報）がUNDOセグメントに記録される。UNDOヘッダはUNDOセグメントの管理情報を持ち、UNDOブロックは実際のUNDOデータを保持する。同時実行されるトランザクション数が多く、UNDOセグメントが少ない場合、UNDOヘッダへのアクセスが集中し競合が発生する可能性がある。また、複数のセッションが同じデータブロックを同時に変更しようとし、その結果同じUNDOブロックにUNDO情報が書き込まれる際にも、UNDOブロックでの競合が発生することがある。これらの問題への対策としては、UNDOテーブルスペースのサイズを大きくしてUNDOセグメントの数を増やし、競合を分散させることや、アプリケーション側で過剰なUNDO情報を生成するような処理を見直し、最適化することが考えられる。

どのファイルとブロックでBuffer Busy Waitが発生しているかを知りたい場合は、v$session_waitビューからイベント情報を取得できる。取得したファイル番号とブロック番号を使って、dba_extentsビューからそのブロックを所有するセグメントを特定できる。これにより、具体的な原因箇所を特定し、適切な対策を講じることが可能となる。

次に「Read by Other Session」について説明する。これはOracle 10.1以降で導入された待機イベントで、前述の通り、バッファキャッシュに存在しないブロックを複数のセッションが同時に要求した際に発生する。最初のセッションがディスクからそのブロックを読み込み、バッファキャッシュに格納するまでの間、他のセッションは待機することになる。以前はこれもBuffer Busy Waitとして報告されていたが、ディスクI/Oが原因の待機と、バッファキャッシュ内の競合が原因の待機とを明確に区別するために、新しいイベントとして定義された。クエリが最適化されておらず、必要なブロックが頻繁にバッファキャッシュから追い出される場合や、ディスクI/Oそのものが遅い場合に、この待機イベントがデータベースの応答時間に大きな影響を与える可能性がある。対策としては、クエリを最適化して不必要なディスクI/Oを減らすことや、ディスクサブシステムの性能を向上させることが考えられる。

これらの待機イベントの状況は、AWR（Automatic Workload Repository）レポートなどのデータベース診断ツールで確認できる。AWRレポートの「Segments by Buffer Busy Waits」セクションでは、どのセグメントがBuffer Busy Waitを多く発生させているかを確認でき、パフォーマンス問題のボトルネックを特定するのに役立つ。

まとめると、Buffer Busy Waitは、バッファキャッシュ内に既に存在するブロックに対する複数のセッションによる競合が原因で発生する待機イベントである。一方、Read by Other Sessionは、ディスクからバッファキャッシュへのブロック読み込みを他のセッションが待っている状態を指す。これらのパフォーマンス問題に対処するためには、適切なブロックサイズの設定、テーブルやインデックスの設計、そして最も重要なのがSQLクエリの最適化である。システムエンジニアは、これらの待機イベントを理解し、適切なツールを使ってボトルネックを特定し、効果的な対策を講じる能力が求められる。これらはデータベースの安定稼働とパフォーマンス向上に不可欠な知識である。