【Oracle】1件ずつコミットとまとめてコミットはどちらが早いか？

はじめに

Oracle DBにデータを１件ずつinsertしていく際、1件ずつコミットするのと、全部まとめてコミットするのとでどちらが早いでしょうか。
サンプルプログラムを作って計測してみました。

環境

• Windows Server 2008 R2
• Oracle Database 11g Release 11.2.0.1.0
• Visual Studio 2008(Pro*c)

テスト用のテーブル

以下のようなテーブルを用意します。

CREATE TABLE TABLE01 (
	"ID" NUMBER, 
	"NAME" VARCHAR2(50 BYTE), 
	"FURIGANA" VARCHAR2(50 BYTE), 
	"SEIBETSU" VARCHAR2(3 BYTE)
)

10万回insertするプログラム

#pragma warning( disable: 4996 )
#include <stdio.h>
#include <stdlib.h>
#include "sqlcpr.h"

EXEC SQL INCLUDE sqlca;
EXEC SQL BEGIN DECLARE SECTION;
  char uid[64];
  char pwd[64];
  char sid[64];
  char name[50 * 3 + 1];
  int id;
  char furigana[50 * 3 + 1];
  char sex[2 + 1];
  int i = 0;
EXEC SQL END DECLARE SECTION;

void sql_error(char* msg)
{
  char err_msg[128];
  size_t buf_len, msg_len;
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("\n%s\n", msg);
  buf_len = sizeof (err_msg);
  sqlglm(err_msg, &buf_len, &msg_len);
  printf("%.*s\n", msg_len, err_msg);
  EXEC SQL ROLLBACK RELEASE;
  exit(EXIT_FAILURE);
}

int InsertData()
{

  // ユーザ名
  sprintf(uid, "hoge");
  // パスワード
  sprintf(pwd, "hoge_pwd");
  // SID
  sprintf(sid, "orcl");

  EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");

  // データベースへの接続
  EXEC SQL CONNECT :uid IDENTIFIED BY :pwd;

  // 10万回ループ
  for (i = 1; i < 100000; i++)
  {
    id = i;
    sprintf(name, "%s", "テスト　太郎");
    sprintf(furigana, "%s", "てすと　たろう");
    sprintf(sex, "%s", "男");
    // insert文の発効
    EXEC SQL INSERT INTO table01 (ID, NAME, FURIGANA, SEIBETSU)
      VALUES (:id,:name,:furigana,:sex);

    // 1件ずつコミット
    //EXEC SQL COMMIT;
  }

  // 全件まとめてコミット
  EXEC SQL COMMIT;

  return 0;

}

測定結果

	1回目	2回目
10万回コミット	65984msec	56328msec
1回だけコミット	8732msec	9750msec

結果、全件まとめてコミットするほうが圧倒的に早いです。

解説

1件ずつコミットするとなぜ遅いのか。理由については、こちらのサイトがわかりやすいです。

REDOログに着目

redo buffer allocation retries（REDOログ・バッファへの書込み待機）の数を比較すると、全件コミットのほうが5回と多くなっています。
これは、メモリ上のREDOログをREDOログファイルに書き込んでいる間に、REDOログ・バッファが満杯になったことを示しています。

REDOログ・バッファのサイズは、以下のコマンドで調べることができます。ぼくの環境では、4898816(4.8MB)でした。

show parameters log_buffer

全件コミットの場合、Top 5 Timed Eventsにlog buffer spaceが登場しています。
log buffer spaceは、REDOログバッファがいっぱいで書込み待ちが発生していることを示しています。REDOログバッファサイズを調整することで、高速化できるかもしれません。

redo buffer allocation retriesの推奨値は、redo entries（発生したREDOエントリ数）の1％以下が良いそうです。

待機回数の目安としては、redo buffer allocation retriesの値がredo entries（発生したREDOエントリ数）の1％を超えないようにします。
redo buffer allocation retries < redo entries/100
パフォーマンスセラピー / REDOログ・バッファのチューニングを学ぶ

関連記事