Oracle9iに関する検証 その５
＜Oracle 9iに関する検証 その５＞
ペンネーム ちょびひげ
— ブロックサイズの決定基準 2 -－
前回はブロックサイズの決定基準として、以下の1.）、2.）に関して検証した。
今回は3.）の行連鎖がどれほどパフォーマンスに影響を与えるかを検証してみ
たい。
1.）1つのブロックに格納されるRow数による使用領域とディスクI/O数
2.）1つのブロックに格納されるRow数によるデータベース・バッファ上の
使用領域
3.）行連鎖の防止
4.）ブロックレベルでの競合
検証の対象は全件検索ではなく、インデックス検索の場合とする。
（今回はインデックスを使用したレンジスキャンを考慮していない）まず以下
の環境を作成することにする。
1．ブロックサイズが2Kと4Kと16Kの3つのテーブルスペースを作成する。
2．各テーブルにテーブル名だけが違うまったく同一のテーブルを作成する。
3．それぞれのにテーブルに以下の様な10万件のデータを挿入
（1レコード長の平均サイズは約3204バイト）
4.SUBNOにインデックスを作成する。
# テーブル定義 #
-------------------------------------- Name Null? Type ----------- -------- ---------------- NO NUMBER SUBNO NUMBER MOJIMOJI VARCHAR2(4000) --------------------------------------
データの挿入は以下のSQL文で行なった。
※・[n*3199]はnが3199文字を表す ・列（NO）は1 ～ 100000の連番 ・列（SUBNO）は列（NO）を100で割った余り
begin for x in 1 .. 100000 loop insert into chain[2|4|16] values(x,MOD(x,100),'[n*3199]'); commit; end loop; end;
ちなみに、1レコード長を約3204バイトにしたのは、ブロックサイズ2Kでは行
連鎖が発生し、ブロックサイズ4Kでは行連鎖が発生せず、1ブロックに収まる
容量であるからである。これにより、ブロック内の空き領域の違いによるパ
フォーマンスの差が出ないようにしている。
列（SUBNO）に、列（NO）を100で割った余りを挿入したのは、検索時のwhere
句で条件を指定し、100件おきにデータを取得する為である。
ブロックサイズ2Kではすべての行で連鎖が発生し、ブロックサイズ4K、16Kで
は連鎖が発生していないことは、以下のSQL文でに確認済みである。
# 確認結果 #
SQL> analyze table chain[2|4|16] list chained rows; SQL> select table_name, count(*) 2 from chained_rows group by table_name; TABLE_NAME COUNT(*) ------------------------------ ---------- CHAIN2 100000 ※ブロックサイズが2Kのテーブル（CHAIN2）のみ行連鎖が発生
始めに、10万件のデータの中から1000件のデータを検索して処理時間を計測す
る。
すべてのデータをディスクから読み込む場合（1回目）と、データベース・バ
ッファの上から取得する場合（2回目）で処理時間を計測した。
なお、今回の統計情報はすべて、”SQL_TRACE”を”TRUE”にして取得してい
る。
○SELECT処理
*************************************************************
SQL> select * from chain[2|4|16] where subno=77; #処理時間（秒）※（）内はCPU時間 ブロックサイズ 2K 4k 16K ---------------- -------------- -------------- -------------- 1回目 13.29（0.42） 14.42（0.25） 23.81（3.35） 2回目 0.07（0.05） 0.04（0.04） 0.05（0.03）
1回目（ディスクからの読み込み）： ・ブロックサイズが16Kの処理が著しく遅い。これは不必要に読み込む領域（ データ量）が多い為である。 ・ブロックサイズが16Kの場合、CPUの使用率が著しく高い。 ・行連鎖が発生しているにもかかわらず、ブロックサイズが2Kの場合の処理時 間が最も早い。 2回目（メモリ上からの読み込み）： ・すべてのデータがメモリ上に存在する為、ブロックサイズによる処理時間は ほとんど変わらない。 ・行連鎖が発生しているとCPUの使用率が高い
次にUPDATE処理を行なってみる。
処理としては、列（MOJIMOJI）の値（[n*3199]→[ｚ*3199]）を更新する。
○UPDATE処理
*************************************************************
SQL> update chain[2|4|16] set mojimoji='[n*3199]' where subno=77; # 処理時間（秒） ※（）内はCPU時間 ブロックサイズ 2K 4k 16K ---------------- -------------- -------------- -------------- 1回目 13.1（1.0） 13.47（0.5） 23.6（2.8） 2回目 6.9（0.7） 6.02（0.2） 5.8（0.3）
全体的にselectを行なった結果と同じである。
しかし、2回目（メモリ上にデータがある）検索では、若干ブロック・サイズ
2Kの場合の処理が遅い。
目立つのは、行連鎖が発生（ブロックサイズが2K）した場合のCPU使用時間で
ある。行連鎖が発生していない場合の約3倍である。
最後にINSERT処理を行なってみる。
この処理に関しては実際に10万件のデータを作成した際の処理時間である。
○INSERT処理
*************************************************************
# 処理時間（秒） ※（）内はCPU時間 ブロックサイズ 2K 4k 16K ---------------- -------------- -------------- -------------- 実行時間 1299.30（64.59） 675.05（43.45） 458.02（30.98）
行連鎖が発生していた場合は、他のブロックサイズに比べて明らかに処理時間
が遅く、CPU使用時間が高い事が分かる。
大量のインサート処理のパフォーマンスはブロックサイズ16Kの場合が最も良
い。
○結局？
インデックス検索（OLTP系の処理）では以下のことが言えよう。
・SELECT、UPDATE処理では：
メモリ上にデータが無い場合には、ブロックサイズ16Kの時に処理時間、CPU使
用率から見て最悪である。
ただし、メモリ上にデータが載っている場合ではパフォーマンスは良い。
つまり、メモリの容量に十分余裕のある環境では、ブロックサイズは大きめに
取っても問題ない様に思える。
反対にメモリの容量に余裕の無いシステムではブロックサイズを16Kと大きく
した場合に、不必要なデータのディスクI/Oが行なわれ、パフォーマンスが悪
いことが予想されるので、行連鎖が発生しない程度にブロックサイズは小さく
した方が良いであろう。
ブロックサイズ2Kの場合、つまり、行連鎖が発生している場合であるが、これ
に関しては、予想していたよりも、処理時間は遅くならないという結果であっ
た。ディスクからの読み込みが発生する1回目の処理時間に関しては最も速い
という結果である。
ただし、CPU使用時間は高くシステムに負荷をかけている。
・INSERT処理では：
ブロックサイズが大きい場合の方がパフォーマンスが良く、ブロックサイズ2K
（行連鎖が発生していた場合）で最悪である。
○最後に
4.）のブロックレベルでの競合であるが、別の機会を設けて検証したい。
前回と今回の検証を通して言えることは、一度に大量のデータを処理するDSS
系の処理様のオブジェクト（テーブル）では、ブロックサイズは大きい方が良
く、一度に処理するデータが少なく、そのほとんどがインデックスを使用した
ランダムなアクセスが行なわれるオブジェクトはブロックサイズが小さい方が
良いということである。
Oracle9iからは一度作成したテーブルスペースのブロックサイズを変更するト
ランスポータブル表領域機能もあるので、オブジェクト（テーブルの）性質よ
って最適なブロックサイズを決定するべきであろう。
次回は、データベース・バッファ上にあるデータについて見ていきたい。
以上、夏近し！茅ヶ崎にて