Oracle ブロックサイズとレコードサイズとパフォーマンス

2015.12.01 株式会社システムインテグレータ

ブロックサイズとレコードサイズの関係

ブロックサイズによって一回で読み込めるデータの量が変わるため、パフォーマンスに影響が出ます。
また、レコードサイズによっては読み込む回数も異なるため、ブロックサイズとの関連性はDB設計においてとても重要になります。
今回は「Oracleデータブロックのサイズ」ブロックサイズを対象に、レコードサイズとブロックサイズの差でどれくらいパフォーマンスに影響が出るかを検証します。

Oracle ブロックサイズとレコードサイズとパフォーマンス 1

図１：検証イメージ

ブロックサイズに関して

Oracleデータベースのブロックサイズは初期化パラメータ「DB_BLOCK_SIZE」で指定しますが、データベース作成後は変更できません。(表領域毎のブロックサイズは変更できます)

また、データブロックは「ブロックヘッダ」「表ディレクトリ」「行ディレクトリ」「行データ」で構成されます。実際のデータが読み込まれるのは「行データ」になりますが、ブロックサイズで指定するのは合計値になるため、それも考慮しなければいけません。

そしてパフォーマンスに影響を与えるブロックサイズは、他にも様々な影響をデータベースにもたらします。
＜ブロックサイズとデータベースへの影響＞

	サイズが小さいと・・・	サイズが大きいと・・・
格納レコード数	少	多
キャッシュヒット率	高	低
I/Oコスト	低	高
トランザクション競合の可能性	小	大
行連鎖の可能性	大	小
セグメント圧縮の効果	小	大

そのため、利用する業務にあったサイズを十分に検討して設定しなければいけません。
といっても見積もりとはなかなか難しいもので、ある程度の指標はあるようです。
＜ブロックサイズとメリット・デメリット＞

ブロックサイズ	メリット	デメリット
2～4KB	・ブロックの競合、バッファの消費量を削減できる・小レコードでインデックススキャンする場合に向いている	・ブロック内に格納できるレコード数が減少する・大量ブロックをスキャンする必要がでてくる・行連鎖の発生可能性大
8KB	・2～4KBで格納できないレコードを格納できる・行連鎖の発生を抑えることができる	・小レコードにインデックススキャンを行っている場合、バッファ領域が消費される
16～32KB	・1ブロックに大レコードを格納できる・大テーブルのフルスキャンに向いている	・ブロック競合の可能性大・インデックススキャンには向かない

検証内容

今回はブロックサイズを標準的な「8KB」とし、格納するデータのレコードサイズを変更しながら、ブロックサイズとレコードサイズとの関連を検証していきます。

[RELATED_POSTS]

検証環境

今回の検証環境は以下の通りです。

マシン	OS	Windows7
	CPU	Intel Core i5-2430M 2.40GHz
	メモリ	4GB
	その他	特に無し
DB ｹｰｽ1	対象RDBMS	Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
	テーブル名	TEST_BLOCK
	テーブル項目	COL1～COL**　VARCHAR(1000) ※検証によって変動(後述)
	その他	特に無し

検証詳細

・ブロックサイズは「8KB」(DB_BLOCK_SIZE=’8192’)
・サンプルテーブルに100万件のデータを作成
・データのレコードサイズは「6KB」「7KB」「8KB」「9KB」「10KB」
・SELECT文の実行時間を計測

select count(*) from TEST_BLOCK

・それぞれ5回実施し、平均時間を採用する
・一回の検証ごとにテーブルデータを削除し、DBバッファキャッシュをクリア
・データ生成には OBのデータ生成機能を使用

検証準備

①テーブル項目に関して
検証によってテーブル項目を変更します。
具体的にはレコードサイズを調整するため、項目数を変更していきながら検証します

レコードサイズ	項目数	テーブル項目
6KB (6,144 byte)	7	COL1～COL6 VARCHAR(1000) COL7 VARCHAR(144)
7KB (7,168 byte)	8	COL1～COL7 VARCHAR(1000) COL8 VARCHAR(168)
8KB (8,192 byte)	9	COL1～COL8 VARCHAR(1000) COL9 VARCHAR(192)
9KB (9,216 byte)	10	COL1～COL9 VARCHAR(1000) COL10 VARCHAR(216)
10KB (10,240 byte)	11	COL1～COL10 VARCHAR(1000) COL11 VARCHAR(240)

②データ生成に関して
今回生成するデータの内容は以下の通りです。
いつものようにSI Object Browserのデータ生成機能を使って準備します。(第１回参照)

カラム名	データ生成方法
COL1～COL**	乱数値(文字)　※英字のみ、全桁埋め

③読み込みブロック数に関して
初期化パラメータ「db_file_multiblock_read_count」を変更することで、フルスキャン時にI/O１回に対する最大の読み込みブロック数を指定することが出来ます。
今回はブロックサイズとレコードサイズの関連を検証したいので、読み込みブロック数は「1」にしておきます。

初期化パラメータ「db_file_multiblock_read_count」の変更はSI Object Browser で可能です。
＞「データベース情報」→「初期化パラメータ」タブ

Oracle ブロックサイズとレコードサイズとパフォーマンス 2

図２：初期化パラメータ

検証結果は以下の通りとなりました。

レコード長	１回目	２回目	３回目	４回目	５回目	平均
6KB	00:15.024	00:17.206	00:18.144	00:16.552	00:17.599	00:16.905
7KB	00:14.729	00:18.986	00:16.179	00:16.724	00:17.329	00:16.789
8KB	00:23.000	00:24.055	00:24.055	00:25.630	00:25.335	00:24.415
9KB	00:27.980	00:27.880	00:27.770	00:26.334	00:26.798	00:27.352
10KB	00:27.630	00:27.550	00:27.430	00:28.633	00:27.965	00:27.842

※単位：秒

平均値での実行時間の対比は以下の通りです。

Oracle ブロックサイズとレコードサイズとパフォーマンス 3

「8KB」以上の実行時間がかかっているようです。
ブロックサイズと同じなのに時間がかかるのは、ブロックヘッダや表ディレクトリの分だけ１ブロックでは読み込めなかったためと考えられます。
また、「6KB」と「7KB」の差はそれほど見られず一定となっています。

追加検証

少し脇道にそれて読み込みブロック数に着眼してみます。
初期化パラメータを変更してみましょう。
db_file_multiblock_read_count=256;

検証結果は以下の通りとなりました。

レコード長	１回目	２回目	３回目	４回目	５回目	平均
6KB	00:11.342	00:11.763	00:11.279	00:12.869	00:10.409	00:11.532
7KB	00:12.195	00:12.231	00:12.543	00:12.963	00:12.391	00:12.465
8KB	00:12.340	00:12.554	00:12.798	00:12.635	00:12.458	00:12.557
9KB	00:12.633	00:12.786	00:12.693	00:12.768	00:12.936	00:12.763
10KB	00:13.960	00:12.639	00:12.459	00:12.938	00:12.364	00:12.872

※単位：秒
平均値での実行時間の対比は以下の通りです。

Oracle ブロックサイズとレコードサイズとパフォーマンス 4

すいぶんとおとなしくなっちゃいましたね。
レコード長に左右されず、実行時間は概ね同じ結果となりました。

結論

今回の検証結果では、以下のようになりました。
・ブロックサイズよりレコード長が大きい場合はパフォーマンスが劣化する
・ブロックサイズよりレコード長が小さい場合のパフォーマンスは一定になる
・読み込みブロック数でパフォーマンスの劣化は軽減される

今回の検証では、ブロックサイズとレコードサイズによってパフォーマンスに違いが出ることが確認できました。
データの内容、件数、処理内容などによっては結果が異なるかと思われますが、今回の検証結果ではブロックサイズに収まりきらないレコードサイズの場合は、パフォーマンスに劣化が見られたので、収まりきるようなブロックサイズに設定すべきということがわかりました。

また、読み込みブロック数という設定も重要であることがわかりました。
データベースの設計の際には、ブロックサイズの設定と共に読み込みブロック数も考慮するべき点になります。

ブロックサイズの設定は実データに即した設定が推奨されますが、誤った設定をするとこんなことになってしまう、という参考になれば幸いです。

今回の検証結果は以上となります。