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

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

前回までは「単一サーバ」、「単一セッション」の場合に、データベース・
バッファ上でCRブロックがどのように作成されるのかを見てきた。今回から
は「単一サーバ」、「複数セッション」の場合を見ていく。

当然、複数セッションでの検証では以下の状況が予測される。

[Step1]セッションAからTEST表に対してDELETE処理(COMMITを行わない)
   ↓
[Step2]セッションBからTEST表にアクセス
   ↓
[Step3]読取一貫性を保つ為にCRブロックが作成される

今回は、上記のセッションAのDELETE処理(Step1)は固定として、Step2で別
のセッションであるBからSELECT、INSERT、UPDATEの各処理を行う。その際に
、データベース・バッファ上のオブジェクトのステータスの変化(Step3)を
検証する。また、SELECTとUPDATEでは、全件検索で処理を行なった場合とイ
ンデックス検索で行った場合の、両方を見ていく事にする。

環境は前回と同様に以下の通りである。

*************************************************************
◆環境
Linux 2.4.2-2
Oracle9i EE Release 9.2.0.1.0

◆テーブル構成

SQL> desc test
 Name      Type
 --------- ------------------
 ID1       NUMBER
 ID2       NUMBER
 TEXT      VARCHAR2(2000)

※ID1にINDEX(TEST_IDX)を付与

入っているデータは以下の3件

       ID1        ID2 TEXT
---------- ---------- --------------------
         1          1 insight
         2          2 insight
         3          3 insight

以下の順番でデータベース・バッファ上のオブジェクトのステータス変化
を見ていく。

1. 全件検索でのSELECTの場合
2. インデックス検索でのSELECTの場合
3. INSERTの場合
4. インデックスでのUPDATEの場合
5. 全件検索でのUPDATEの場合

まずは全件検索でのSELECTの場合を見てみよう。

[1. 全件検索でのSELECTの場合]
*************************************************************
□セッションAでDELETE処理(ID1が2のデータを削除)

SQL> delete from test where id1=2;

1行が削除されました。
データベース・バッファ上のオブジェクトのステータス
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
OBJECT_NAME          STATE     DBABLK
-------------------- ----- ----------
TEST                 xcur       63793 ←テーブルのセグメント・ヘッダ
TEST                 xcur       63794 ←テーブルのデータ・ブロック
TEST_IDX             xcur      105642 ←インデックスのリーフ・ブロック

□セッションBで全件検索でのSELECTを実行

SQL> select /*+ full(test) */ *  from test where id1=2;

↑【全件検索をヒント句で指定】

□データベース・バッファ上のオブジェクトのステータス

OBJECT_NAME          STATE     DBABLK
-------------------- ----- ----------
TEST                 xcur       63793
TEST                 xcur       63794
TEST                 cr         63794 ←読取一貫性の為に作成
TEST_IDX             xcur      105642

テーブル(TEST)のロックをセッションA保持しているので、そのブロックに
対して、CRブロックが作成されている様子が分かる。

次に、インデックス検索でのSELECTの場合はどうなるであろうか?

[2. インデックス検索でのSELECTの場合]
*************************************************************
□セッションBでインデックス検索でのSELECTを実行
※事前にセッションAでDELETE処理済み

SQL> select * from test where id1=2     ← ID1はインデックスあり

□データベース・バッファ上のオブジェクトのステータス

OBJECT_NAME          STATE     DBABLK
-------------------- ----- ----------
TEST                 cr         18720 ←読取一貫性の為に作成
TEST                 xcur       18720
TEST_IDX             cr         18724 ←読取一貫性の為に作成
TEST_IDX             xcur       18724

おっと!全件検索時には作成されなかったインデックス(TEST_IDX)のCRブ
ロックが作成されている。まずは、インデックスのCRブロックを作成して、
そこからテーブルを見に行く。最後に、テーブルのCRブロックも作成する。
パフォーマンスの観点から見れば非常に効率が悪そうではある。

次はINSERTだが、1行のINSERTを行なってみる。

[3. INSERTの場合]
*************************************************************
□セッションBで1行INSERTを実行
※事前にセッションAでDELETE処理済み

SQL> insert into test values(2,2,'insight');

□データベース・バッファ上のオブジェクトのステータス

OBJECT_NAME          STATE     DBABLK
-------------------- ----- ----------
TEST                 xcur       70913 ←テーブルのセグメント・ヘッダ
TEST                 xcur       70914 ←テーブルのデータ・ブロック
TEST_IDX             xcur       96394 ←DELETE時にアクセス

単純にINSERTが行われるだけなので、CRブロックが作成されることはない。
セグメント・ヘッダ・ブロックにアクセスし、フリーリストみてから空きの
スペースにデータの挿入を行う。テーブルのヘッダ・ブロックとデータ・ブ
ロックの2ブッククにアクセスしている。

どんどん行ってみよう。次は全件検索でのUPDATEである。UPDATEに関しては、
削除した行と同じ行の削除を行うとロックがかかるので、同一ブロック内の
別の行に対してUPDATEを行なう。

[4. インデックス検索でのUPDATEの場合]
*************************************************************
□セッションBでインデックス検索でのUPDATEを実行
※事前にセッションAでDELETE処理済み

SQL> update test set id1=1 where id1=1;

□データベース・バッファ上のオブジェクトのステータス

OBJECT_NAME          STATE     DBABLK BA      
-------------------- ----- ---------- --------
TEST                 xcur       70913 54DA0000
TEST                 xcur       70914 54D96000
TEST_IDX             cr         96394 54D52000 ←読取一貫性の為に作成
TEST_IDX             xcur       96394 54D98000

※更新前後でTEST_IDXのカレント・ブロック(XCUR)のBAに変化はなし

インデックス(TEST_IDX)から更新対象行(ROWID)を特定して、テーブル
(TEST)の更新を行っている。読取一貫性はインデックスのCRブロックを作成
することにより保って、テーブルの更新処理を行なっている。ただし、今回は
DELETEした行と更新した行の値が同一のリーフ・ブロックに格納されている為
にCRブロックが作成されているが、もし、違うブロックに値が格納されている
ような場合、CRブロックは作成されない。正確にはCRブロック作成の有無はリ
ーフ・ブロックのバージョン(SCN)で決定される。

また、ここで注意したいのはテーブルのCRブロックが作成されない点である。
なぜかと言えば、インデックスを検索した段階で更新対象のROWIDが特定出来
ているので、テーブルのCRブロックは作る必要がないのである。パフォーマン
スの観点からはとても良い事である。

[5. 全件検索でのUPDATEの場合]
*************************************************************
□セッションAでDELETE処理実行

SQL> delete from test where id1=2;

□データベース・バッファ上のオブジェクトのステータス

OBJECT_NAME          STATE     DBABLK BA      
-------------------- ----- ---------- --------
TEST                 xcur       70913 54E90000
TEST                 xcur       70914 54E86000 ←更新対象ブロック
TEST_IDX             xcur       96394 54E88000

□セッションBで全件検索でのUPDATEを実行

SQL> update /*+ FULL(test) */ test set id1=1 where id1=1;

↑【全件検索をヒント句で指定】

□データベース・バッファ上のオブジェクトのステータス

OBJECT_NAME          STATE     DBABLK BA      
-------------------- ----- ---------- --------
TEST                 xcur       70913 54E90000
TEST                 cr         70914 54E44000
TEST                 xcur       70914 54E86000 ←BAに変化なし
TEST_IDX             xcur       96394 54E88000

インデックス(TEST_IDX)にはアクセスせずに、テーブル(TEST)を全件舐め
て更新を行っているので、読取一貫性はテーブルのCRブロックを作成すること
により保たれている。

また、全件での更新ではバッファ・アドレス(BA)の変化にも注目して欲しい。

前回の単一セッションのUPDATEでは、更新対象のカレント・ブロックのバッ
ファ・アドレスが更新の前後で変化したが、複数セッションの場合には変化
しない事が分かる。おそらくはパフォーマンス上の理由だと考えられるが詳
細は不明である。

以上が複数セッションの場合の動きである。単一セッションでの動きを理解
していれば、特別変わった変化をしていないことが分かる。

CRが作成される状況は以下の通りである。

CRブロックが作成されるケース(複数セッション)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                         SELECT  UPDATE  INSERT 
------------------------ ------- ------- -------
全件検索         - TABLE ○      ○      ―
                 - INDEX ×      ×      ―
インデックス検索 - TABLE ○      ×      ―
                 - INDEX ○      ○      ―

全件検索でのUPDATEではTABLEのCRブロック、インデックス検索のUPDATEでは
INDEXのCRブロックのみが作成されている。これは以下の基本的な流れを考え
れば理解できる。

インデックス検索の場合
^^^^^^^^^^^^^^^^^^^^^^^
1. インデックスへのアクセスしてROWIDを取得
2. ROWIDから更新対象ブロックの更新行アクセスする。
3. 更新処理

全件検索の場合
^^^^^^^^^^^^^^^
1. ブロックの全データを取得して更新対象行を特定
2. 更新処理

ちなみに、これまでの”インデックス検索”とはインデックスへのアクセスだけ
で、更新対象の行が特定できることを表している。もし、インデックスで条件
を絞り込んだ(レンジスキャン)後に、更にインデックス以外の列の情報で条
件を絞り込む必要がある場合は、全件検索と同様の動きをする。

次回はバファのダンプを取得してCRブロックが作成されるメカニズムを追う。

以上、雷、強風、波浪注意!暴風雨の茅ヶ崎にて