読者です 読者をやめる 読者になる 読者になる

小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


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

はじめに

Oracle DBにデータを1件ずつ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

結果、全件まとめてコミットするほうが圧倒的に早いです。
f:id:replication:20141001235040p:plain

解説

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

REDOログに着目

f:id:replication:20140927231148p:plain
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ログ・バッファのチューニングを学ぶ はてなブックマーク - パフォーマンスセラピー / REDOログ・バッファのチューニングを学ぶ

関連記事