続 X$BH に関する検証 その1

<続 X$BH に関する検証 ~その1~>
ペンネーム ちょびひげ

過去の “Oracle 9i 関する検証” で 主にデータベース・バッファの構造を理
解するために X$BH 表を詳しく見ていった。今回は前回とは違った観点でX$BH
に焦点を当てて検証を行って行きたいと思う。

最初に、単一インスタンスの環境でオブジェクトの検索時や更新時のデータベ
ース・バッファ上のオブジェクトのステータス変化を見て行く。次にRACの場
合には、単一インスタンスと比較して、どのような変化をするのかを見て行
きたい。最後に10gの場合のSGA管理にも触れることが出来ればと思う。

それでは、まずX$BH表に関しておさらいしておきたい。

X$BH とは?
Oracleの動的パフォーマンス・ビューの元表であり、SYSユーザでアクセスが
可能である。この表を見ることによって、データベース・バッファにどんな
オブジェクトが存在するか?そのオブジェクトがどんな状態であるか?どんな
状態であったのか?などが分かる。

以下のSQL文を実行することによりどのようなオブジェクトがデータベース・
バッファ上に載っているのか一目瞭然である。

select
  o.object_name, blsiz , count(*) blocks
from x$bh b , dba_objects o
where b.obj = o.data_object_id
  and b.ts# > 0
group by o.object_name, blsiz
order by blocks desc

OBJECT_NAME                         BLSIZ     BLOCKS
------------------------------ ---------- ----------
CUSTOMER                             8192        920
CUSTOMER_BAD_IDX                     8192         23
ITEM                                 8192         11
STOCK                                8192         11
STOCK_PKEY                           8192         11
ITEM_PKEY                            8192          9
NEW_ORDER_PKEY                       8192          6
ORDER_LINE_PKEY                      8192          5
ORDER_PKEY                           8192          4
・
・
・

例えば、上記の場合はCUSTOMER表がデータベース・バッファの大部分を占めて
おりCUSTOMER表を参照しているSQLがチューニング可能な事を示唆している。

なぜなら、一つのテーブルだけでデータベース・バッファの大部分も占めて
いるということは、効率的なインデックスが行われず、全件検索、もしくは
非効率なレンジスキャンが実行されている事が予測される為である。ただし
、CUSTOMER表のサイズが他のテーブルと比較して非常に大きな環境であれば
問題ない。

それでは次に一つのオブジェクトに絞って、STATE といるカラムについて見て
行こう。

以下のSQL文で各オブジェクトのステータスを取得することが出来る。
(以下の例では”TEST”テーブルで条件を絞っている)

select
  o.object_name
  ,decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec'
  ,6,'irec',7,'write',8,'pi') state
  , blsiz , count(*) blocks
from x$bh b , dba_objects o
where b.obj = o.data_object_id
  and b.ts# > 0
  and o.object_name = 'TEST'
group by o.object_name, state, blsiz
order by blocks desc

OBJECT_NAME                    STATE      BLSIZ     BLOCKS
------------------------------ ----- ---------- ----------
TEST                           xcur        8192         23
TEST                           cr          8192          5

STETE列の意味は以下の通りである。

FREE : 現在使用されていない
XCUR : 排他
SCUR : 共有カレント
CR   : CR ブロック
READ : ディスクから読込み中
MREC : メディア・リカバリ・モード
IREC : インスタンス・リカバリ・モード
WRITE: ディスクへ書き込み中
PI   : RACでのCache Fusion によるブロック転送元の過去のブロックイメージ

各項目の詳細に関しては今後の検証で説明を行っていきたい。次回からはデ
ータベース・バッファ上でのオブジェクトのステータス変化に焦点を当てて
もう少し詳しく見ていきたい。

以上、天高くつけまい肥ゆる茅ヶ崎にて