Oracle9iに関する検証 その６
＜Oracle 9iに関する検証 その６＞
ペンネーム ちょびひげ
— データベース・バッファ上のデータ -－
今回はデータベース・バッファ上にどのようにオブジェクト（テーブル）が載
るか見てみたい。
データベース・バッファ上に載っているオブジェクトは、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
次回は全件検索ではない場合のバッファ上の様子を見てみたい。
以上、茅ヶ崎にて