トランザクションエントリに関する検証　その６

＜トランザクションエントリに関する検証　その６＞
ペンネーム　しゅらん

今週も引き続きテーブルやインデックスを作成する際に設定するinitrans、
maxtransについて検証をします。

検証環境
OS:Windows2000 Server
Oracle:8.1.7
DB_BLOCK_SIZE:8K

先週、先々週は読者からの質問に関する検証を行いました。

ちょっと戻りますが、第1回～第3回は同時複数sessionからテーブルへのインサ
ートを行ったり、maxtransを1にしてどうなるかの検証を行いました。

そして、トランザクションエントリを使い切ってしまうと、ブロックにいくら
空き領域が残っていても、トランザクションを待たせないために、新たなブロ
ックを確保してしまい、インサートが出来なかったブロックをフリーリストか
ら外してしまった。そのため場合によっては非常に格納効率が悪くなってしま
った事があった。

今週はインデックスについて同様の検証を行いたいと思います。
インデックスも同じような動きをするのか？それとも・・・・？

第1回で使用したテーブルtbl_shu1を使用して検証を行います。

CREATE TABLE TBL_SHU1 (ID   DATE DEFAULT SYSDATE,
                       TEXT CHAR(10))
                       INITRANS 1 MAXTRANS 255 PCTFREE 10
                       STORAGE ( INITIAL     20M
                                 NEXT        20M
                                 PCTINCREASE  0
                                 MAXEXTENTS  UNLIMITED);

このテーブルのIDカラムにINDEXを作成してみます。

CREATE INDEX IDX_TBL_SHU1 ON TBL_SHU1(ID)
             INITRANS 2 MAXTRANS 255 PCTFREE 10
             STORAGE ( INITIAL     20M
                       NEXT        20M
                       PCTINCREASE  0
                       MAXEXTENTS  UNLIMITED);

これで準備完了

このテーブルに1sessionから20万件のインサートを行った場合、
また200sessionからそれぞれ1000件、合計20万件のインサートを行った場合、
INDEXの領域の使用方法にどういう違いが出るのかを検証します。

—1sessionから20万件のインサート—

まずは1sessionからインサートを行い、基礎情報を取得します。
テストプログラムは、おなじみの弊社開発言語POPSQLで作成しています。

LOOP(i=0; i&gt;200000; i++)
	SQL insert into tbl_shu1 (TEXT) values ('PPPPPPPPPP');
	COMMIT
ENDLOOP

ANALYZE INDEX IDX_TBL_SHU1 COMPUTE STATISTICS;

SELECT INDEX_NAME,TABLE_NAME,NUM_ROWS,LEAF_BLOCKS FROM DBA_INDEXES
WHERE INDEX_NAME='IDX_TBL_SHU';

INDEX_NAME    TABLE_NAME    NUM_ROWS LEAF_BLOCKS
------------  ----------- ---------- -----------
IDX_TBL_SHU1  TBL_SHU1        200000         476

リーフブロックとして、476ブロック（約3.71MB）使用している。

—200同時sessionからそれぞれ1000件のインサート—

次にTBL_SHU1をトランケート後、
200sessionからそれぞれ1000件、合計20万件のインサートを行う。

ANALYZE INDEX IDX_TBL_SHU1 COMPUTE STATISTICS;

SELECT INDEX_NAME,TABLE_NAME,NUM_ROWS,LEAF_BLOCKS FROM DBA_INDEXES
WHERE INDEX_NAME='IDX_TBL_SHU';

INDEX_NAME    TABLE_NAME    NUM_ROWS LEAF_BLOCKS
------------  ----------- ---------- -----------
IDX_TBL_SHU1  TBL_SHU1        200000        1345

1345ブロック（約10.51MB）、つまり約2.8倍の領域が使用されている。
これはやはりトランザクションエントリによるものであろうか？

20万件で 476ブロック → 1ブロック平均420行
20万件で1345ブロック → 1ブロック平均149行

—ダンプの確認—

ではダンプを取って確かめてみよう。ダンプの取り方については
＜トランザクションエントリに関する検証　その3＞を参照。

1ブロック目はセグメントヘッダー、2ブロック目はブランチなので
3ブロック目から見てみよう。

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   xid:  0x0010.046.00000098    uba: 0x00801e23.0008.01  CBU-    0  scn 0x0000.000e86cf
0x02   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   xid:  0x000d.021.00000098    uba: 0x008019b7.0006.06  C-U-    0  scn 0x0000.000e8651
0x04   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0  fsc 0x0000.00000000

Leaf block dump
===============
header address 114984100=0x6da84a4
kdxcolev 0
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 417  ←  レコード数

トランザクションエントリは5個、レコード数は417である。1sessionから
インサートをした場合(平均420行)とあまり変わっていない。

では100ブロックづつスキップして見てみる。

ブロックID    トランザクションエントリ数  レコード数
       100                 166               151
       200                 168               110
       300                 169               140

やはりトランザクションエントリの領域がデータ領域を圧迫し、使用率を悪く
している事が分かる。最初のブロックはトランザクションの動き出すタイミン
グにより、200sessionが同時に動いていなかったためであろうと思われる。

そして以下のように、フリーリストからも外れている事も分かった。

（flg: -）
seg/obj: 0xdbf  csc: 0x00.bf064  itc: 169  flg: -  typ: 2 - INDEX

しかし前回の検証で分かったように、同時にインサートされるということは、
同時に更新される可能性も示唆しているため、これは正しいのかも知れない。
でも普通はINDEXカラムを更新する、特にPKEYなどを更新するのは、論理的に
考えてもよくない場合が多い。

テーブルと同じ扱いで良いのだろうか？という疑問も残る。

次回も引き続きトランザクションエントリに関する検証を行います。

以上、最近ラーメン屋の増えてきた茅ヶ崎にて

トランザクションエントリに関する検証 その６

トランザクションエントリに関する検証　その６