kagamihogeの日記

kagamihogeの日記です。

NOLOGGING有無によるダイレクトパスインサートの速度の差

DB Oracle

Oracleは、NOLOGGINGを指定することでREDOログを抑制する結果としてINSERT等の速度を改善することが出来る……らしい。今回はその辺を実際にやってみることにする。

参考URL：
Oracle Database管理者ガイド 11g リリース2 (11.2) REDOレコードの書込みの制御
Oracle Database VLDBおよびパーティショニング・ガイド11g リリース2 (11.2) パラレル実行のチューニングのその他のヒント
パフォーマンスセラピー / NOLOGGINGとは

環境

準備

入力側のテーブルを作成する。インデックスとか制約とかはなんもなし。

CREATE TABLE SRC (COLUMN1 VARCHAR2(16));

とりあえず100万件ほどデータを入れておく。

create or replace 
PROCEDURE INSERT_DATA AS 
BEGIN
  FOR I IN 1..1000000 LOOP
    INSERT INTO SRC(COLUMN1) VALUES((DBMS_RANDOM.STRING('a',16)));
  END LOOP;
  commit;
END INSERT_DATA;

出力側のテーブルを作成する。こちらもインデックスとか制約とかはなんもなし。計測用のプログラムを一度実行するたびに、テーブルをDROPしてCREATEしなおす。

CREATE TABLE DST (COLUMN1 VARCHAR2(16));
DROP TABLE DST PURGE;

テーブルをNOLOGGINGにするときは下記のクエリを実行する。

ALTER TABLE DST NOLOGGING;

計測用のプログラム

プログラムは2種類用意する。一つは、APPENDヒントを使用したもの。もう一つは、APPEND_VALUESヒントを使用したもの。それぞれを、NOLOGGINGとそうでない時とで実行する。よって、都合4種類のパターンを実行する。

APPENDヒントの方は、至極単純なINSERT SELECTだけ。実行時間の計測を後述のAPPEND_VALUESとあわせたかったので、PL/SQLにしている。結果の実行時間は開始と終了のSYSTIMESTAMPを引き算した数値。

create or replace 
PROCEDURE HOGE AS 
  V_START TIMESTAMP;
  V_END TIMESTAMP;
BEGIN
  SELECT SYSTIMESTAMP INTO V_START FROM DUAL;
  
  INSERT /*+ append */ INTO DST SELECT * FROM SRC;
  COMMIT;
  
  SELECT SYSTIMESTAMP INTO V_END FROM DUAL;
  DBMS_OUTPUT.PUT_LINE(V_END - V_START);
END HOGE;

APPEND_VALUESヒントの方は、バルクインサートにする。コミット間隔を10000にしているが、この数値にこれといって特に意味は無いです。テキトーに決めました。

CREATE OR REPLACE PROCEDURE DIRECT_PATH_APPEND_VALUES AS 
  CURSOR C_SRC IS SELECT COLUMN1 FROM SRC;
  TYPE TIDNAMES IS TABLE OF SRC%ROWTYPE INDEX BY BINARY_INTEGER;
  VIDNAME tIDNAMES;

  V_START TIMESTAMP;
  V_END TIMESTAMP;
BEGIN

SELECT SYSTIMESTAMP INTO V_START FROM DUAL;

  OPEN C_SRC;
  LOOP
    FETCH C_SRC BULK COLLECT INTO VIDNAME LIMIT 10000;
    EXIT WHEN VIDNAME.COUNT = 0;
    BEGIN
      FORALL I IN 1..VIDNAME.COUNT
        INSERT /*+ append_values */ INTO DST VALUES VIDNAME(I);
      COMMIT;
    END;
  END LOOP;
  CLOSE C_SRC;

SELECT SYSTIMESTAMP INTO V_END FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_END - V_START);

END DIRECT_PATH_APPEND_VALUES;

結果

　 1 2 3
APPEND 1.672000000 2.218000000 1.531000000
APPEND (NOLOGGING) 1.171000000 1.203000000 1.188000000
APPEND_VALUES 2.719000000 2.750000000 2.984000000
APPEND_VALUES (NOLOGGING) 2.344000000 2.172000000 2.547000000

というわけで、REDOログ抑制した分だけ速くなることが分かりました。といっても、ダイレクトパスインサートが元々はやいんで、大した差にはならなかったのだけど。データ量がもっと多ければまた違ってくるんですかね。