Oracle9iに関する検証 その7

<Oracle 9iに関する検証 その7>
ペンネーム ちょびひげ

— データベース・バッファ上のデータ 2 --

前回は全件検索時のデータベース・バッファ(以下バッファ)の状態を見た。
今回は、インデックス検索時のバッファの様子を見る予定であったが、もう少
し全件検索についてみて行きたい。

前回の検証で、全件検索の一部のブロックはすぐにLRUの末尾側にリストされ
た。つまり、すぐに追い出しがかかる状態である。その状態を見るのが、X$BH
表のLRU_FLAG列であった。

それとは別にX$BHにはFLAGSという名の列がある。
今回はこれに注目する。

では、実際にどのような値が入っているか見てみよう。
ちなみに、X$BH表はSYSユーザでなければ見れないので注意して頂きたい。

SQL> select flag, count(*) from x$bh group by flag;

           FLAG   COUNT(*)
     ---------- ----------
1行目         0       1734
2行目         1         21
3行目      8192         57
4行目    524288         15 
5行目    524289         27
6行目  33554433          1
7行目  33562625        109
8行目  35659777          2

これだけ見ても、数字が入っているだけで何のことかぜんぜん分からない。
この数字は10進数だが、これを2進数に変換してビットを見ることによって、
FLAGSの意味がわかるようになっている。ためしに、上から4行目のデータに注
目してみたい。

値は”524288”なので2進数に変換すると

”10000000000000000000”になる。
  ↑右から20ビット目

5行目のデータ”524289”も2進数に変換して見てみよう。

”10000000000000000001”
  ↑右から20ビット目 ↑右から1ビット目

次は1ビット目と20ビット目にビットが立っている。

20ビット目にビットが立っているかどうかをチェックするのが以下のSQL文に
なる。

SQL> select bitand(flag,power(2,19)) flag, count(*) from x$bh
  2  group by bitand(flag,power(2,19));

      FLAG   COUNT(*)
---------- ----------
         0       1933
    524288         33

bitand():ビット論理積を返す
power():べき乗を返す

先ほどの”524288”と”524289”とのカウント値の合計(15+27=33)が求め
られているのが分かる。

では、20ビット目にビットが立っているのは何なのかというと、
”only_sequential_access”(後述)が行なわれたことを表している。

各ビットに対して、他にも以下のような状態がある。

# 9i では無くOracle8.1.5のものです。

ビット                        ビット
0      buffer_dirty           14      stale
1      notify_after_change    15      deferred_ping
2      mod_started            16      direct_access
3      block_has_been_logged  17      hash_chain_dump
4      temp_data              18      ignore_redo
5      being_written          19      only_sequential_access
6      waiting_for_write      20      prefetched_block
7      multiple_waiters       21      block_written_once
8      recovery_reading       22      logically_flushed
9      unlink_from_lock       23      resilvered_already
10     down_grade_lock        25      redo_since_read
11     clone_being_written    29      plugged_from_foreign_db
12     reading_as_CR          30      flush_after_writing
13     gotten_in_current_mode                            ..etc

実はこれらのビットの意味はOracleのバージョンによって多少違っており、ど
のビットが何を表しているかは、バッファのダンプを取って調べることが出来
る。バッファのダンプに関しは、別の回に詳しく見てみる予定である。

今回はダンプファイルのブロック情報をちょっとだけ見て頂きたい。

# 以下のコマンドでダンプの取得
# ダンプはuser_dump_destで指定したディレクトリに出力される

SQL> alter session set events 'immediate trace name buffers level 4';

セッションが変更されました。

# フラグ(flag)の2進数の値が”10000000000000000000”のブロック

CHAIN: 9 LOC: 0x82f45b4c HEAD: [843f1400,843f1400]
    BH (0x843f1400) file#: 1 rdba: 0x00400421 (1/1057) class 1 ba: 0x84280000
      set: 3, dbwrid: 0               【ブロックを特定する為に使用】↑
      hash: [82f45b4c,82f45b4c], lru: [843f1398,843f14e8]
      LRU flags: moved_to_tail
      ckptq: [NULL] fileq: [NULL]
      st: XCURRENT, md: NULL, rsop: 0x0, tch: 0
      flags: only_sequential_access     ←------【ここに注目!】
      L:[0x0.0.0] H:[0x0.0.0] R:[0x0.0.0]
      buffer tsn: 0 rdba: 0x00400421 (1/1057)
      scn: 0x0000.000019b8 seq: 0x02 flg: 0x04 tail: 0x19b80602
      frmt: 0x02 chkval: 0x71a6 type: 0x06=trans data

※X$BH表のBA列と上記のbaからブロックを特定

上記が1ブロックのダンプの内容であるが、FLAGS列を見ると、
”only_sequential_access”になっているのが分かる。
ちなみにFLAGS列が”10000000000000000001″のブロックを見ると、FLAGSに
”buffer_dirty”が追加されているのが分かる。

# フラグ(flag)の2進数の値が”10000000000000000001”のブロック

      flags: buffer_dirty only_sequential_access

このように詳しく調べていくと、何ビット目にビットが立っているかと、その
ビットが意味する内容を対応付けることが出来る。

また、ビットが複数立つことから、そのブロックがどのようなブロックかを詳
細に知ることが出来る。ただし、”only_sequential_access”は全件検索など
でシーケンシャルなアクセスを行なったブロックと思われるが、一度このビッ
トが立つと、このビットは立ったままになるので、バッファ上に載っている間
にインデックス検索でアクセスしても、ビットは立ったままである。

本当のリアルタイムのブロックの状態を知ろうと思うとFLAGS列の他にも、
LRU_FLAG列の値なども同時に見る必要があると言えよう。

以下のようなSQL文でリアルに全件検索でバッファ上に載ったオブジェクトを
検索出来る。

# テーブル(BLOCK4)全件検索時の実行結果

SQL> select o.object_name , count(*) cnt
  2  from dba_objects o, x$bh x
  3  where x.obj=o.object_id and x.lru_flag=2
  4  and bitand(x.flag,524288)>0
  5  and owner'SYS'
  6  group by o.object_name;

OBJECT_NAME         CNT
------------ ----------
BLOCK4              557

以上、茅ヶ崎にて