【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を直接参照できない理由

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上の使用中バッファを、データオブジェクトと表領域へ関連付けています。

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

OWNEROBJECT_NAMESUBOBJECT_NAMEOBJECT_TYPETABLESPACE_NAMECACHED_BLOCKSBLOCK_SIZECACHED_MB
APPSALES_DATAP2026TABLE PARTITIONAPP_DATA1250008192976.56
APPIDX_SALES_01P2026INDEX PARTITIONAPP_INDEX620008192484.38
APPCUSTOMER TABLEAPP_DATA180008192140.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インスタンスリカバリ中
piRACにおける過去イメージ

今回の目的は、現在キャッシュ上で使用されているブロックの集計です。そのため、未使用状態の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$BHV$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のスナップショットと対応付けて評価します。

RDS for Oracle全体の設計ギャップ

今回取り上げたX$BHの制約は、RDS for Oracleで変わる運用前提の一例です。

SYSDBA権限、OSアクセス、ログ取得など、オンプレミスとの設計・運用上の違いと、その代替アプローチについては、以下の記事で整理しています。

関連記事
RDS for Oracleを採用したら、オンプレ運用の前提が通用しなかった

SYSDBA権限、OSアクセス、ログ取得など、RDS for Oracleで変わる設計・運用上の違いと代替アプローチを整理しています。

noteで記事を読む →

まとめ

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

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

ON o.data_object_id = bh.objd

OBJECT_IDではなくDATA_OBJECT_IDを使用する点が重要です。

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

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

ただし、キャッシュ量が多いオブジェクトが、直ちに性能問題の原因とは限りません。

V$BHの結果は、SQL、実行計画、物理読み込み、待機イベント、AWR、V$DB_CACHE_ADVICEなどと組み合わせて評価する必要があります。

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

V$BHはX$BHの完全な代替ではありません。

しかし、

どのオブジェクトのブロックが、バッファキャッシュ上にどの程度存在しているか

を確認する目的であれば、公開された動的パフォーマンスビューであるV$BHを使って調査できます。

重要なのは、オンプレミスと同じSQLをそのまま再現することではありません。

調査目的に応じて、V$BH、RDS_X$BH、AWRなどを使い分け、RDSで実行可能な調査手順へ再設計することです。

コメント

タイトルとURLをコピーしました