Oracle9iに関する検証 その6

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

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

今回はデータベース・バッファ上にどのようにオブジェクト(テーブル)が載
るか見てみたい。

データベース・バッファ上に載っているオブジェクトは、X$BH表で確認するこ
とができる。
ちなみに、Oracle9iからは複数のサイズのバッファが設定可能なので、X$BHに
もブロックサイズを表す列(BLSIZ)が追加されている。

*************************************************************
# 以下のSQL文でバファ上の各ブロック数をカウントしている。
(各ブロックサイズのバッファ上の全体の数である)

SQL> select
  2  blsiz , count(*) BLOCKS
  3  from x$bh
  4* group by blsiz;

     BLSIZ     BLOCKS
---------- ----------
      2048       3784  → 2K×3784=7568K
      4096       1966  → 4K×1966=7864K
     16384        506  →16K× 506=8096K

BLSIZ:Oracleブロックサイズ
BLOCKS:ブロック数

# 初期化パラメータを確認

SQL> show parameter cache;

NAME                   TYPE        VALUE
---------------------- ----------- -------------------
db_16k_cache_size      big integer 8388608
db_2k_cache_size       big integer 8388608
db_32k_cache_size      big integer 0
db_4k_cache_size       big integer 0
db_8k_cache_size       big integer 0
db_cache_advice        string      ON
db_cache_size          big integer 8388608 → 8192K

各バッファのサイズはそれぞれ8Mのはずだが、バッファ上のブロックサイズ×個数(count)
で確認すると8Mに満たない。これは何らかの管理情報が必要な為であると思わ
れる。ブロックサイズが大きい方が実際に使用できるメモリ上の領域が多いこ
とが分かる。

では、実際にフル検索を行なった後のバッファ上のデータを見てみよう。

まず始めにメモリ上の空き領域を見てみよう。
空き領域はX$BHのstate列が0のものを検索することで確認出来る。

SQL> select blsiz, count(*) blocks, state from x$bh
  2  where state=0
  3  group by blsiz ,state;

     BLSIZ     BLOCKS      STATE
---------- ---------- ----------
      2048       3784          0
      4096        547          0
     16384        506          0

ブロックサイズ4Kの空き領域が少ないのは、デフォルトのブロックサイズが4K
のため、既にSYSのオブジェクトで使用されているからである。

ではブロックサイズ4Kのテーブルに作成したテーブル(BLOCK4)の全件検索を
行なってみる。

# 全件検索を実行

SQL> select * from block4;

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'BLOCK4'

統計
----------------------------------------------------------
         18  db block gets
       8482  consistent gets
       1922  physical reads  ← 1922BLOCKの読み込み発生

# バッファ上のテーブル(BLOCK4)を確認
(DBA_OBJECTからオブジェクト名を取得し、使用しているブロック数を表示)

SQL> select
  2  o.object_name, blsiz , count(*) blocks , lru_flag , tch, state
  3  from x$bh b , dba_objects o
  4  where b.obj = o.object_id
  5  and o.object_name = 'BLOCK4'
  6  group by b.blsiz, o.object_name, lru_flag, tch, state;


OBJECT_NAME        BLSIZ     BLOCKS   LRU_FLAG        TCH      STATE
------------- ---------- ---------- ---------- ---------- ----------
BLOCK4              4096        129          0          0          1
BLOCK4              4096          1          0          2          1
BLOCK4              4096        351          2          0          1
                         ----------
483ブロックがバッファ上にある→ 481

まずは、各列の意味に関して説明しよう。

STATE:
先ほども出てきたが、”0”だとデータベース・バッファ上のブロックが未使用
で、”1”だと使用中である。

LRU_FLAG:
”Oracle9iに関する検証 その1”でLRUリストに関して出てきたが、これはそ
のLRUリスト上での状態を表している。”2”の場合はLRUリストのLRU側(末尾)、
つまりバッファからの追い出し候補に移動されている。
”8”の場合はLRUリストのMRU側に移動されている。つまり、アクセスが頻繁
に行なわれるので、追い出しは発生しないようになっている。
このフラグが”0”の場合は特にフラグがセットされていない状態である。

TCH:
これに関しては後ほど説明しよう。

先ほどの結果で、BLOCK4のLRU_FLAGを見ると”0”のものと”2”のものがある。
これは、今回の検索が全件検索の為、ある量を超えた場合は、LRU側に登録し、
追い出しの対象にするためである。

ここでもう一度同様の全件検索を行なってみよう。

# 全件検索を実行

SQL> select * from block4;

# バッファ上のテーブル(BLOCK4)を確認
  (先ほどと同様の検索)

OBJECT_NAME         BLSIZ     BLOCKS   LRU_FLAG        TCH      STATE
-------------- ---------- ---------- ---------- ---------- ----------
BLOCK4               4096        129          0          0          1
BLOCK4               4096          1          0          5          1
BLOCK4               4096        348          2          0          1

ほとんどBLOCK4のバッファ上のブロック数は変化していない。
次に、ブロックサイズ4Kのテーブルスペースにある、他のテーブルの検索を行
なったところ以下のような結果になった。

# バッファ上のテーブル(BLOCK4)を確認
(先ほどと同様の検索)

OBJECT_NAME         BLSIZ     BLOCKS   LRU_FLAG        TCH      STATE
-------------- ---------- ---------- ---------- ---------- ----------
BLOCK4               4096        129          0          0          1
BLOCK4               4096          1          0          7          1
BLOCK4               4096         59          2          0          1
                                  ↑
                             ここが減ってる!

おっ! LRU側にリストされていたブロックが、348→59に減っている。
つまり、LTU_FLAGが”2”のものから優先(?)されて追い出しが発生しいる
ことが分かる。また、全件検索でバッファ上に載るブロックでも、MRU側に載
るものとLRU側に載るものがあるのが面白い。

最後にTCH列であるが、これはデータベース・バッファに載っているブロック
がアクセスされた回数である。これについては、次回詳しく見ていきたい。

ちなみに、全件検索でも1ブロックだけは、TCHが多くなっているものがあるが、
このブロックはセグメントヘッダである。

おそらく、Oracleが内部的にオブジェクトのセグメントヘッダにアクセスする
確率が高い為、全件検索でも意図的にバッファ上に残るようにしている為であ
ろう。

以下、セグメントヘッダであることを確認している。
(TCH列の値が”7”であるブロックを検索)

# バッファ上のブロックのファイル番号とブロック番号を確認
(TCH > 0 で検索)

SQL> select o.object_name, dbarfil, dbablk
  2  from x$bh b , dba_objects o
  3  where b.obj = o.object_id
  4    and o.object_name = 'BLOCK4'
  5    and b.tch > 0
  6  group by o.object_name, dbarfil, dbablk;

OBJECT_NAME        DBARFIL     DBABLK
--------------- ---------- ----------
BLOCK4                  11     100881

# テーブル(BLOCK4)のセグメントヘッダを確認

SQL> select segment_name, header_file, header_block
  2* from dba_segments where segment_name = 'BLOCK4';

SEGMENT_NAME      HEADER_FILE HEADER_BLOCK
----------------- ----------- ------------
BLOCK4                     11       100881

次回は全件検索ではない場合のバッファ上の様子を見てみたい。

以上、茅ヶ崎にて