RDS for OracleでV$BHを使う方法｜バッファキャッシュ調査とX$BHの代替

はじめに

RDS for Oracleでは、オンプレミスOracleと同じ方法でSYS.X$BHを直接参照できません。

対応する環境では、X$BHを基にしたSYS.RDS_X$BHビューを作成できます。ただし、X$固定表はOracle内部の非公開オブジェクトであり、本番環境で利用する場合は、非本番環境での検証やエンジンアップグレード後の確認が必要です。

一方、調査の目的が、

DBバッファキャッシュ上に、どのテーブルやインデックスのブロックがどの程度存在しているか確認する

ことであれば、Oracleが公開している動的パフォーマンスビューV$BHを利用できます。

V$BHはX$BHの完全な代替ではありません。LRU_FLAGやTCHなど、V$BHでは取得できない情報もあります。

しかし、オブジェクトごとのキャッシュブロック数や概算容量を把握する目的であれば、V$BHから必要な情報を取得できます。

本記事では、RDS for OracleでV$BHを使用してDBバッファキャッシュの内訳を調査するSQLと、結果を評価する際の注意点を解説します。

SYS.X$BHを直接参照できない理由と、SYS.RDS_X$BHを利用する際の条件については、以下の記事で整理しています。

【RDS for Oracle】オンプレの調査SQLが動かない｜X$BHを直接参照できない理由

RDS for OracleでSYS.X$BHを直接参照できない理由を解説。SYSDBA権限の制約、RDS_X$BHの作成方法、V$BHとの使い分けや運用上の注意点を整理します。

V$BHで確認できる情報
OBJECT_IDではなくDATA_OBJECT_IDで結合する
Oracle公式の基本SQL
オブジェクト別のキャッシュ容量を確認するSQL
SQLの各項目
LEFT JOINを使用する理由
STATUSがfreeのバッファを除外する
キャッシュ量が多いオブジェクトは問題なのか
V$BHと一緒に確認する情報
V$BHの検索負荷に注意する
非マスターユーザーから参照する場合
X$BHとV$BHの違い
V$BHは調査の入口
まとめ

V$BHで確認できる情報

V$BHは、SGA内に存在する各バッファの状態を表示する動的パフォーマンスビューです。

Oracle公式リファレンスでは、バッファの状態、データファイル番号、ブロック番号、データオブジェクト番号、表領域番号などを確認できるビューとして説明されています。

V$BHの列定義｜Oracle Databaseリファレンス

オブジェクト別のキャッシュ量を調査する際、主に利用する列は次のとおりです。

列	内容
`FILE#`	データファイル番号
`BLOCK#`	データファイル内のブロック番号
`STATUS`	バッファの状態
`OBJD`	ブロックに対応するデータオブジェクト番号
`TS#`	表領域番号
`DIRTY`	変更済みブロックかどうか
`CON_ID`	マルチテナント環境のコンテナID

このうち、オブジェクトとの結合で中心になるのがOBJDです。

V$BH.OBJDをDBA_OBJECTS.DATA_OBJECT_IDと結合することで、バッファキャッシュ上のブロックが、どのテーブル、インデックス、パーティションに属しているかを確認できます。

OBJECT_IDではなくDATA_OBJECT_IDで結合する

V$BHとDBA_OBJECTSを結合する際、注意したいのが結合列です。

次のように、OBJECT_IDと結合したくなるかもしれません。

ON o.object_id = bh.objd

しかし、ここではOBJECT_IDではなく、DATA_OBJECT_IDを使用します。

ON o.data_object_id = bh.objd

OBJECT_IDは、データディクショナリ上でオブジェクトを識別する番号です。

一方、DATA_OBJECT_IDは、そのオブジェクトのデータを格納するセグメントに対応する番号です。

V$BH.OBJDが保持しているのは、バッファ内のブロックに対応するデータオブジェクト番号です。そのため、DBA_OBJECTS.DATA_OBJECT_IDと結合します。

Oracle公式のバッファキャッシュ調査SQLでも、次の条件が使われています。

o.data_object_id = bh.objd

この違いは、パーティション表や、TRUNCATE、MOVEなどによってセグメントが再作成されたオブジェクトを扱う場合に重要です。

オブジェクトの論理的な識別子であるOBJECT_IDが変わらなくても、物理的なセグメントに対応するDATA_OBJECT_IDが変化する場合があります。

バッファキャッシュ上のブロックを、実際のセグメントへ正しく関連付けるには、DATA_OBJECT_IDを使用する必要があります。

Oracle公式の基本SQL

OracleのPerformance Tuning Guideでは、バッファキャッシュ上に存在するオブジェクト別のブロック数を、次のようなSQLで確認しています。

SELECT
    o.object_name,
    COUNT(*) AS number_of_blocks
FROM
    dba_objects o,
    v$bh bh
WHERE
    o.data_object_id = bh.objd
    AND o.owner <> 'SYS'
GROUP BY
    o.object_name
ORDER BY
    COUNT(*);

このSQLは、V$BH.OBJDとDBA_OBJECTS.DATA_OBJECT_IDを結合し、オブジェクトごとのブロック数を集計するものです。

基本SQLと、バッファキャッシュの利用状況を調査する手順は、Oracle公式のチューニングガイドで確認できます。

Tuning the Database Buffer Cache｜Oracle Database Performance Tuning Guide

基本的な目的は、このSQLで達成できます。

ただし、実務で結果を確認する場合は、次の情報も表示したほうが分かりやすくなります。

オブジェクトの所有者
パーティション名
オブジェクト種別
表領域名
ブロックサイズ
キャッシュ容量の概算値

これらを追加したSQLが次の例です。

オブジェクト別のキャッシュ容量を確認するSQL

SELECT
    o.owner,
    o.object_name,
    o.subobject_name,
    o.object_type,
    t.name AS tablespace_name,
    COUNT(*) AS cached_blocks,
    d.block_size,
    ROUND(
        COUNT(*) * d.block_size / 1024 / 1024,
        2
    ) AS cached_mb
FROM
    v$bh bh
    LEFT JOIN dba_objects o
        ON o.data_object_id = bh.objd
    LEFT JOIN v$tablespace t
        ON t.ts# = bh.ts#
    LEFT JOIN dba_tablespaces d
        ON d.tablespace_name = t.name
WHERE
    bh.status <> 'free'
GROUP BY
    o.owner,
    o.object_name,
    o.subobject_name,
    o.object_type,
    t.name,
    d.block_size
ORDER BY
    cached_mb DESC;

このSQLでは、V$BH上の使用中バッファを、データオブジェクトと表領域へ関連付けています。

結果は、概ね次のような形式になります。

OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	TABLESPACE_NAME	CACHED_BLOCKS	BLOCK_SIZE	CACHED_MB
APP	SALES_DATA	P2026	TABLE PARTITION	APP_DATA	125000	8192	976.56
APP	IDX_SALES_01	P2026	INDEX PARTITION	APP_INDEX	62000	8192	484.38
APP	CUSTOMER		TABLE	APP_DATA	18000	8192	140.63

この結果から、どのオブジェクトのブロックがバッファキャッシュ上で大きな割合を占めているかを確認できます。

SQLの各項目

OWNER

オブジェクトの所有者です。

異なるスキーマに同じオブジェクト名が存在する場合があるため、OBJECT_NAMEだけではなく、OWNERも表示します。

OBJECT_NAME

テーブルやインデックスなどのオブジェクト名です。

パーティション表やパーティションインデックスでは、同じOBJECT_NAMEに対して複数のセグメントが存在します。

SUBOBJECT_NAME

パーティション名またはサブパーティション名です。

これを表示しないと、どのパーティションのブロックがキャッシュされているか分からなくなります。

OBJECT_TYPE

オブジェクトの種類です。

主に次のような値が表示されます。

TABLE
INDEX
TABLE PARTITION
INDEX PARTITION
TABLE SUBPARTITION
INDEX SUBPARTITION

TABLESPACE_NAME

オブジェクトのブロックが属している表領域名です。

V$BH.TS#とV$TABLESPACE.TS#を結合して取得します。

CACHED_BLOCKS

バッファキャッシュ上に存在するブロック数です。

V$BHは基本的にバッファ単位で行を返すため、COUNT(*)によってオブジェクトごとのブロック数を集計します。

BLOCK_SIZE

表領域のブロックサイズです。

通常はデータベース標準のブロックサイズが使われますが、非標準ブロックサイズの表領域が存在する可能性もあります。

そのため、固定値として8192を掛けるのではなく、DBA_TABLESPACES.BLOCK_SIZEを使用します。

CACHED_MB

キャッシュされている概算容量です。

計算式は次のとおりです。

キャッシュブロック数 × ブロックサイズ

SQLでは、この値を1024で2回割り、MiB相当へ換算しています。

ただし、この値はオブジェクトの物理サイズではありません。

SQLを実行した時点で、バッファキャッシュ上に存在するブロック数を容量換算した値です。

LEFT JOINを使用する理由

V$BHとDBA_OBJECTS、表領域関連ビューの結合にはLEFT JOINを使用しています。

INNER JOINを使用すると、対応するオブジェクトや表領域情報を取得できなかったバッファが、結果から除外されます。

通常のユーザーテーブルやインデックスだけを確認する場合は、INNER JOINでも大部分の結果を取得できます。

しかし、バッファキャッシュには次のようなブロックが含まれる可能性があります。

一時セグメント
Undo関連ブロック
内部管理用ブロック
オブジェクトへ単純に関連付けられないバッファ
取得タイミングによってディクショナリと一致しない情報

そのため、まずはV$BH側の行を残し、オブジェクトや表領域へ関連付けられなかった行も確認できるようにしています。

OWNER、OBJECT_NAME、TABLESPACE_NAMEなどがNULLになった行が多い場合は、不要と判断して除外するのではなく、FILE#、BLOCK#、TS#、STATUSなどを追加して内容を確認します。

ユーザーオブジェクトだけを簡潔に確認したい場合は、INNER JOINへ変更しても構いません。

STATUSがfreeのバッファを除外する

今回のSQLでは、次の条件を指定しています。

WHERE bh.status <> 'free'

V$BH.STATUSには、バッファの状態が表示されます。

代表的な値は次のとおりです。

STATUS	概要
`free`	現在使用されていない
`xcur`	排他カレント
`scur`	共有カレント
`cr`	読み取り一貫性用のブロック
`read`	ディスクから読み込み中
`mrec`	メディアリカバリ中
`irec`	インスタンスリカバリ中
`pi`	RACにおける過去イメージ

今回の目的は、現在キャッシュ上で使用されているブロックの集計です。そのため、未使用状態のfreeを除外しています。

障害解析やRAC関連の調査など、状態ごとの内訳が必要な場合は、STATUSをSELECT句とGROUP BY句へ追加して確認します。

キャッシュ量が多いオブジェクトは問題なのか

V$BHの結果を見ると、特定のテーブルやインデックスが大量のブロックを占めていることがあります。

しかし、

キャッシュ量が多い
＝不要なデータがキャッシュされている
＝チューニングが必要

とは限りません。

業務で継続的に参照されるテーブルであれば、多くのブロックがキャッシュされていることは自然です。

必要なブロックがバッファキャッシュに残っていれば、その後のSQLはストレージからの物理読み込みを減らせます。

問題になる可能性があるのは、例えば次のようなケースです。

利用頻度の低い大規模表が大量に読み込まれている
大規模バッチの後にオンライン処理の物理読み込みが増える
フルスキャンが繰り返されている
想定していたインデックスが使用されていない
同じ大規模オブジェクトへ不要なアクセスが繰り返されている
必要なブロックが短時間でキャッシュから追い出されている

したがって、V$BHの結果だけで良否を判断してはいけません。

V$BHと一緒に確認する情報

SQLと実行計画

キャッシュ上で大きな割合を占めるオブジェクトを特定したら、そのオブジェクトへアクセスしているSQLを確認します。

フルテーブルスキャンになっていないか
パーティションプルーニングが機能しているか
想定以上の行やブロックを読み込んでいないか
同じ処理を不必要に繰り返していないか

キャッシュ量が多いという結果だけで、SQLに問題があるとは判断できません。実行計画と実行統計を組み合わせて評価します。

物理読み込みと待機イベント

AWRやStatspack、V$SYSSTATなどから、処理量に対して物理読み込みが増えていないかを確認します。

また、次のようなI/Oやバッファ関連の待機イベントも確認します。

db file sequential read
db file scattered read
direct path read
free buffer waits
buffer busy waits
read by other session

ただし、待機イベントが発生しているだけで、バッファキャッシュを増やすべきとは判断できません。

SQL、ストレージ性能、DBWRの書き出し、同時実行数など、複数の要因を確認する必要があります。

V$DB_CACHE_ADVICE

バッファキャッシュのサイズを変更した場合に、物理読み込みがどの程度変化すると推定されるかを確認するには、V$DB_CACHE_ADVICEを利用できます。

Oracle公式ドキュメントでは、複数の仮想的なキャッシュサイズに対する物理読み込み数やミス率の予測値を表示するビューとして説明されています。

ただし、これは実測値ではなく予測値です。

代表的な業務負荷が流れている状態で取得し、AWRや実際のレスポンスタイムと合わせて評価します。

V$BHの検索負荷に注意する

V$BHは、バッファキャッシュ内の各バッファに対応する行を保持しています。

バッファキャッシュが大きい環境では、V$BHの行数も多くなります。

そこへDBA_OBJECTSや表領域関連ビューを結合し、全件をGROUP BYすると、SQL自体がCPUやソート領域を使用します。

Oracle公式ドキュメントでも、全セグメントを対象にした集計は、バッファキャッシュのサイズによって多くのソート領域を必要とする場合があると説明されています。

本番環境で実行する場合は、次の点に注意します。

非本番環境で実行時間と負荷を確認する
業務ピークを避ける
必要に応じて対象スキーマを絞る
定期監視として短い間隔で実行しない
実行計画とTEMP使用量を確認する

参照SQLだから安全とは限りません。

データを更新しないSELECTであっても、大量の行を走査して集計するSQLは、データベースへ負荷を与えます。

非マスターユーザーから参照する場合

非マスターユーザーへV$BHの参照権限を付与する場合は、RDSの管理プロシージャgrant_sys_objectを使用します。

権限付与時には、シノニム名のV$BHではなく、基になるSYSオブジェクト名V_$BHを指定します。

BEGIN
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name  => 'V_$BH',
        p_grantee   => 'MONITOR_USER',
        p_privilege => 'SELECT'
    );
END;
/

ただし、付与できるのは、マスターユーザー自身が保持している権限の範囲内です。

実行前に、対象環境でマスターユーザーからV$BHを参照できることを確認してください。

また、監視ユーザーへ権限を付与する場合は、必要なビューだけに限定し、広いカタログ参照権限を安易に付与しないほうが安全です。

X$BHとV$BHの違い

V$BHでオブジェクトごとのキャッシュ量を確認できますが、X$BHのすべての情報を取得できるわけではありません。

項目	X$BH／RDS_X$BH	V$BH
オブジェクト番号	確認可能	`OBJD`で確認可能
ファイル・ブロック番号	確認可能	確認可能
バッファ状態	確認可能	`STATUS`で確認可能
Dirtyブロック	確認可能	`DIRTY`で確認可能
`LRU_FLAG`	確認できる環境がある	確認不可
`TCH`	確認できる環境がある	確認不可
公開仕様	Oracle内部実装に依存	Oracle公式リファレンスに記載
RDSでの利用	RDS_X$ビューの作成が必要	必要な参照権限があれば利用可能

オブジェクト別のキャッシュブロック数や概算容量を確認したいのであれば、まずV$BHを検討できます。

一方、LRU_FLAGやTCHなどX$BH固有の情報が必要な場合や、Oracle Supportから固定表を使った調査を指示された場合は、対応環境でRDS_X$BHを作成する選択肢があります。

V$BHは調査の入口

V$BHが示すのは、SQLを実行した時点でバッファキャッシュ上に存在するブロックの状態です。直前に大規模バッチが実行されていれば、その処理で読み込まれたブロックが多く表示される可能性があります。

頻繁に利用されるオブジェクトであっても、DBインスタンスの再起動直後や業務開始前であれば、キャッシュブロック数は少なく表示されます。結果を評価する際は、まず取得タイミングを確認します。

代表的な業務負荷が実行されている時間帯か
バッチ実行前か実行後か
DBインスタンス再起動から十分な時間が経過しているか

必要に応じて複数の時間帯で結果を取得し、業務処理の実行状況やSQL統計、物理読み込み、待機イベントと対応付けて評価します。利用可能な機能やライセンス条件に応じて、AWR、Statspack、CloudWatch Database Insightsなども組み合わせます。

特定のオブジェクトが大量のバッファを使用しているという結果だけでは、キャッシュサイズの変更やSQLチューニングが必要とは判断できません。

調査では、既存SQLをそのまま再現することよりも、そのSQLで何を確認しようとしていたのかを整理することが重要です。オンプレミスで使用していたSYS.X$BHをRDS for Oracleから直接参照できない場合でも、目的がオブジェクト別のキャッシュブロック数を確認することであれば、公開された動的パフォーマンスビューであるV$BHを利用できます。

LRU_FLAGやTCHなど、X$BH固有の情報が必要な場合は、使用しているエンジンリリースと作成可能なビューを確認したうえで、SYS.RDS_X$BHの利用を検討します。X$表はOracle内部のシステムオブジェクトであるため、非本番環境で検証し、本番環境への導入はOracle Supportのガイダンスを踏まえて判断します。

V$BHは、性能問題の全体を説明するビューではありません。どのオブジェクトのブロックがバッファキャッシュ上に存在しているかを確認し、その後のSQL、実行計画、I/O、待機イベントの調査につなげる入口として位置づけます。

今回取り上げたX$BHの制約は、RDS for Oracleで変わる運用前提の一例です。SYSDBA権限、OSアクセス、物理ファイル操作、ログ取得、ストレージ管理についても、オンプレミスと同じ手順をそのまま使用できるとは限りません。

オンプレミスの操作を一対一で置き換えるのではなく、調査や運用の目的を整理し、RDSで提供される機能を使って手順を再構成する必要があります。

RDS for Oracleで変わる設計・運用上の前提と代替アプローチについては、以下の記事で整理しています。

RDS for Oracleを採用したら、オンプレ運用の前提が通用しなかった｜開発中に見えた設計ギャップと代替アプローチ

RDS for Oracleではオンプレ運用の前提が通用しない。Storage-Full、アーカイブログ、SSH不可など、開発中に見えた9つの設計ギャップと代替アプローチを整理します。

まとめ

RDS for Oracleで、DBバッファキャッシュ上のオブジェクト別ブロック数を確認する場合は、V$BHを利用できます。

V$BH.OBJDは、DBA_OBJECTS.OBJECT_IDではなく、DATA_OBJECT_IDと結合します。

ON o.data_object_id = bh.objd

オブジェクト別のキャッシュ容量は、キャッシュブロック数と表領域のブロックサイズから概算できます。

キャッシュブロック数 × ブロックサイズ

V$BHはX$BHの完全な代替ではありませんが、どのオブジェクトのブロックがバッファキャッシュ上にどの程度存在しているかを確認する目的には利用できます。

キャッシュ量が多いオブジェクトが、直ちに性能問題の原因とは限りません。SQL、実行計画、物理読み込み、待機イベントなどの情報と対応付け、取得した時間帯の業務負荷も踏まえて評価する必要があります。

また、大規模なバッファキャッシュを全件集計するSQLは、相応のCPUやソート領域を使用する可能性があります。本番環境で実行する前に負荷を確認し、必要に応じて対象スキーマや表領域を絞ります。

オンプレミスの調査SQLをそのまま再現するのではなく、調査目的を起点に、RDSで利用可能な手段を組み合わせることが重要です。

【RDS for Oracle】V$BHでバッファキャッシュの内訳を調査する｜X$BHの代替方法