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

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

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

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

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

図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のデータ生成機能を使って準備します。(第1回参照)

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

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

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

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

図2:初期化パラメータ

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

レコード長 1回目 2回目 3回目 4回目 5回目 平均
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」以上の実行時間がかかっているようです。
ブロックサイズと同じなのに時間がかかるのは、ブロックヘッダや表ディレクトリの分だけ1ブロックでは読み込めなかったためと考えられます。
また、「6KB」と「7KB」の差はそれほど見られず一定となっています。

追加検証

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

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

レコード長 1回目 2回目 3回目 4回目 5回目 平均
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

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

結論

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

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

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

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

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


RELATED POST関連記事


RECENT POST「DBlab」の最新記事


DBlab

Oracle デッドロックの原因を追いかけてみる

DBlab

Oracle パーティション検証完結編

DBlab

Oracle B-treeインデックスとビットマップインデックス

DBlab

Oracle INDEXを作成したときのパフォーマンスへの効果を探る

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