MTULabo
  • 製品紹介
  • 導入事例
  • FAQ
  • MTULabo
  • ダウンロード
評価版ライセンス申込 有償版利用申込(見積依頼) MTU Advance

大量LOBデータの移行

公開日 2013-07-31
更新日 2014-09-30

概要

(容量的に/個数的に)ボリュームのあるLOBをファイルへ一括出力して移動しようとすると、使うツールによってはとても遅い事があります。本稿はこのようなときに役立つMTUの機能について取り上げます。

MTUは大量のLOBを一括して扱うのに適したOCIの機能(*1)を採用する事で、データベースサーバーから離れた場所にあるコンピュータへ大量のLOBを高速に移動できるよう設計されています。

特に1kB未満の小さいLOBを多数(1000万個)移動するケースでは、オリジナルのエクスポート/インポートを使う場合と比べて約21倍も高速です。それ以外のケースでも、データポンプ・エクスポート/インポートを使う場合と同水準の性能を発揮する事が出来ます。

また、MTUから出力されるデータは同時に出力されるSQL*Loader用の制御ファイルと併せ、コマンド一つで再ロードする事が出来ます。加えてデータ表現がオープンであるため、Oracle Database 以外にもデータを提供できる点はエクスポート・ユーティリティーに無い特色と言えます。

本稿では、パーティショニングされたボリュームのあるLOB表を実際に用意し、LOBをファイルへ抽出する操作と、反対にファイルからLOBへデータを格納する操作(*2)を、MTU、オリジナルのエクスポート/インポート、及びデータポンプ・エクスポート/インポートの三者で行い、比較した結果をレポートします。

*1): LOB配列読取り(OCILobArrayRead 関数)、LOBデータ、長さおよびチャンク・サイズのプリフェッチ(OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE)、それぞれOracle Database 10g Release 2及び11g から登場した新機能です。

*2): すべてのコマンドは実際に使われたもので、読者が手元に用意した環境を使って再現させる事が出来ます。

準備

  1. 2台のコンピュータを用意し、一方にOracle Database 11g Release 2 サーバーを、もう片方にOracle Database 11g Release 2 クライアントをセットアップします。これらのコンピュータを1GbEのスイッチへ繋ぎます。
    項目 Oracle Database クライアント用 Oracle Database サーバー用
    CPU Intel Xeon(R) E5420 (2.50GHz)
    プロセッサ数1(コア数4)
    Intel Xeon(R) X3450 (2.67GHz)
    プロセッサ数1(コア数4)
    RAM 4GB 12GB
    HBA SASアレイコントローラカード Dell SAS 6/iR Adapter Controller
    HDD SAS 300GB/15000rpm×5 SAS 500GB/15000rmp×2
    OS Windows Server 2003
    Stardard x64 Edition
    Windows Server 2008 R2
    Standard x64 Edition
  2. Oracle Database クライアントへ MTU をセットアップします。このコンピュータには必要に応じてMicrosoft Visual C++ 2010 再頒布可能パッケージを導入します。
  3. Oracle Database サーバーへ SYSユーザで接続し、パーティションを格納する為の表領域を作成します。
    create tablespace PAR1 datafile size 256m autoextend on next 32m maxsize unlimited;
    create tablespace PAR2 datafile size 256m autoextend on next 32m maxsize unlimited;
    create tablespace PAR3 datafile size 256m autoextend on next 32m maxsize unlimited;
    create tablespace PAR4 datafile size 256m autoextend on next 32m maxsize unlimited;
  4. データ・ポンプで使用する為のディレクトリオブジェクトを作成してオブジェクト権限をユーザへ与えます。
    create or replace directory LOGICAL_BACKUP_DIR as 'C:\oracle\flash_recovery_area\o112utf8';
    grant WRITE, READ on directory LOGICAL_BACKUP_DIR to SCOTT;
  5. リロード後のデータ同一性をチェックする為、ハッシュ関数を使えるようにします。
    grant execute on DBMS_CRYPTO to public;
  6. LOB表のオーナとして再接続します。
    connect SCOTT/TIGER@o112utf8
  7. LOB表のプライマリー・キーを発番する為の順序オブジェクトを作成します。
    create sequence TEST_PK_SQ minvalue 0 maxvalue 99999999 cycle cache 1000 start with 0;
  8. 1kBのLOBを1000万個格納する為のLOB表を作成します。
    create table CONTAINS_1K_10MLOBS
    ( PKEY      number(8)
    , CLOB_HASH RAW(20)
    , CLOB_DATA CLOB
    , BLOB_HASH RAW(20)
    , BLOB_DATA BLOB
    , constraint PK_CONTAINS_1K_10MLOBS primary key (PKEY)
    )
    lob (CLOB_DATA) store as (chunk 8k CACHE ENABLE STORAGE IN ROW)
    lob (BLOB_DATA) store as (chunk 8k CACHE ENABLE STORAGE IN ROW)
     partition by hash ( PKEY )
     partitions 16 store in (PAR1, PAR2, PAR3, PAR4);
  9. 1MBのLOBを1万個格納する為のLOB表を作成します。
    create table CONTAINS_1M_10KLOBS
    ( PKEY      number(8)
    , CLOB_HASH RAW(20)
    , CLOB_DATA CLOB
    , BLOB_HASH RAW(20)
    , BLOB_DATA BLOB
    , constraint PK_CONTAINS_1M_10KLOBS primary key (PKEY)
    )
    lob (CLOB_DATA) store as (chunk 32k CACHE DISABLE STORAGE IN ROW)
    lob (BLOB_DATA) store as (chunk 32k CACHE DISABLE STORAGE IN ROW)
     partition by hash ( PKEY )
     partitions 16 store in (PAR1, PAR2, PAR3, PAR4);
  10. 無名PL/SQLブロックを実行して CONTAINS_1K_10MLOBS の行を挿入します。5回繰り返して目的の行数にします。(約43時間)
    set timing on serveroutput on size 999999 feedback off
    declare
    	i binary_integer;
    	clob_tmp clob;
    	blob_tmp blob;
    	clob_prm clob;
    	blob_prm blob;
    	v_pkey binary_integer;
    	vc2_buf varchar2(32767);
    	raw_buf raw(32767);
    begin
    	DBMS_RANDOM.INITIALIZE( 255 );
    	DBMS_LOB.CREATETEMPORARY(clob_tmp, TRUE, DBMS_LOB.CALL);
    	DBMS_LOB.CREATETEMPORARY(blob_tmp, TRUE, DBMS_LOB.CALL);
    	for i in 1 .. 1000000 loop
    		-- 1kB 前後のランダムなデータを作成(長さのバラつきは正規分布に従う)
    		for j in 1 .. 16 loop
    			vc2_buf := DBMS_RANDOM.STRING('A', DBMS_RANDOM.NORMAL*3.0 + 62) || chr(13) || chr(10);
    			raw_buf := UTL_RAW.CAST_TO_RAW(vc2_buf);
    			dbms_lob.writeappend(clob_tmp, length(vc2_buf), vc2_buf);
    			dbms_lob.writeappend(blob_tmp, UTL_RAW.LENGTH(raw_buf), raw_buf);
    		end loop;
    		INSERT INTO CONTAINS_1K_10MLOBS (PKEY, CLOB_DATA, BLOB_DATA) 
    			VALUES (TEST_PK_SQ.nextval, EMPTY_CLOB(), EMPTY_BLOB())
    			RETURNING PKEY, CLOB_DATA, BLOB_DATA
    			INTO v_pkey, clob_prm, blob_prm;
    		DBMS_LOB.APPEND (clob_prm, clob_tmp);
    		DBMS_LOB.APPEND (blob_prm, blob_tmp);
    		UPDATE CONTAINS_1K_10MLOBS
    			SET CLOB_HASH = DBMS_CRYPTO.Hash(clob_prm, 1)
    			,   BLOB_HASH = DBMS_CRYPTO.Hash(blob_prm, 1)
    			WHERE PKEY = v_pkey;
    		DBMS_LOB.TRIM(clob_tmp, 0);
    		DBMS_LOB.TRIM(blob_tmp, 0);
    		if mod(i, 10000) = 0 then
    			commit;
    		end if;
    	end loop;
    end;
    /
    commit;
  11. 無名PL/SQLブロックを実行して CONTAINS_1M_10KLOBS の行を挿入します。5回繰り返して目的の行数にします。(約42分間)
    set timing on serveroutput on size 999999 feedback off
    declare
    	i binary_integer;
    	clob_tmp clob;
    	blob_tmp blob;
    	clob_prm clob;
    	blob_prm blob;
    	v_pkey binary_integer;
    	vc2_buf varchar2(32767);
    	raw_buf raw(32767);
    begin
    	DBMS_RANDOM.INITIALIZE( 255 );
    	DBMS_LOB.CREATETEMPORARY(clob_tmp, TRUE, DBMS_LOB.CALL);
    	DBMS_LOB.CREATETEMPORARY(blob_tmp, TRUE, DBMS_LOB.CALL);
    	for i in 1 .. 1000 loop
    		-- 1MB 前後のランダムなデータを作成(長さのバラつきは正規分布に従う)
    		for j in 1 .. 512 loop
    			vc2_buf := DBMS_RANDOM.STRING('A', DBMS_RANDOM.NORMAL*3.0 + 2046) || chr(13) || chr(10);
    			raw_buf := UTL_RAW.CAST_TO_RAW(vc2_buf);
    			dbms_lob.writeappend(clob_tmp, length(vc2_buf), vc2_buf);
    			dbms_lob.writeappend(blob_tmp, UTL_RAW.LENGTH(raw_buf), raw_buf);
    		end loop;
    		INSERT INTO CONTAINS_1M_10KLOBS (PKEY, CLOB_DATA, BLOB_DATA) 
    			VALUES (TEST_PK_SQ.nextval, EMPTY_CLOB(), EMPTY_BLOB())
    			RETURNING PKEY, CLOB_DATA, BLOB_DATA
    			INTO v_pkey, clob_prm, blob_prm;
    		DBMS_LOB.APPEND (clob_prm, clob_tmp);
    		DBMS_LOB.APPEND (blob_prm, blob_tmp);
    		UPDATE CONTAINS_1M_10KLOBS
    			SET CLOB_HASH = DBMS_CRYPTO.Hash(clob_prm, 1)
    			,   BLOB_HASH = DBMS_CRYPTO.Hash(blob_prm, 1)
    			WHERE PKEY = v_pkey;
    		DBMS_LOB.TRIM(clob_tmp, 0);
    		DBMS_LOB.TRIM(blob_tmp, 0);
    		if mod(i, 10) = 0 then
    			commit;
    		end if;
    	end loop;
    end;
    /
    commit;
  12. データ生成後にCBO統計を手動で与えます。
    begin
    	DBMS_STATS.GATHER_TABLE_STATS (USER, 'CONTAINS_1K_10MLOBS', degree=>dbms_stats.auto_degree);
    	DBMS_STATS.GATHER_TABLE_STATS (USER, 'CONTAINS_1M_10KLOBS', degree=>dbms_stats.auto_degree);
    end;
    /

実行

  1. Env.bat というスクリプトを編集して、MTUの環境変数を設定します。MERGE_LOBS_INTO_SDFというパラメータが変更されている点に注意して下さい。(空)のままにしておくと、LOBの個数だけファイルが出力されます。このケースでは1000万個ものファイルが作られることになり、カーネル・メモリが大量に消費されシステム・ダウンを招く場合もあります。LOBの個数が10万個を超えるようなケースでは必ず true を指定して下さい。
    最初にCONTAINS_1K_10MLOBS表をアンロードします。

    環境変数名 変更前 変更後
    USERID SYSTEM/MANAGER 接続先ユーザ名/パスワード@接続文字列
    DST_INFO SCOTT/TIGER SCOTT/TIGER@o112utf8
    LISTTABLE (空) CONTAINS_1K_10MLOBS
    FILESIZE 2000M 0M
    USUALPATH true (空)
    RECOVERABLE true (空)
    MERGE_LOBS_INTO_SDF (空) true
    PARALLELISM 4 8
    PARTITIONING 3 0

    所要時間や出力されたファイルのバイト数を記録します。

  2. Reload.bat を実行し、データをリロードします。所要時間を記録します。
  3. 次の無名PL/SQLブロックを実行して、すべてのデータが正しくロードできた事をチェックします。
    declare
    	cursor c1 is select * from
    		 CONTAINS_1K_10MLOBS/    * 所要時間 4 分 12 秒 */
    		 /*CONTAINS_1M_10KLOBS*/ /* 所要時間 17 分 34 秒 */
    	;
    	clob_unmatched binary_integer;
    	blob_unmatched binary_integer;
    	clob_matched binary_integer;
    	blob_matched binary_integer;
    	clob_is_null binary_integer;
    	blob_is_null binary_integer;
    begin
    	clob_unmatched := 0;
    	blob_unmatched := 0;
    	clob_matched := 0;
    	blob_matched := 0;
    	clob_is_null := 0;
    	blob_is_null := 0;
    	for r1 in c1 loop
    		if r1.CLOB_DATA is not null then
    			if r1.CLOB_HASH <> DBMS_CRYPTO.Hash(r1.CLOB_DATA, 1) then
    				clob_unmatched := clob_unmatched + 1;
    			else
    				clob_matched := clob_matched + 1;
    			end if;
    		else
    			clob_is_null := clob_is_null + 1;
    		end if;
    		if r1.BLOB_DATA is not null then
    			if r1.BLOB_HASH <> DBMS_CRYPTO.Hash(r1.BLOB_DATA, 1)  then
    				blob_unmatched := blob_unmatched + 1;
    			else
    				blob_matched := blob_matched + 1;
    			end if;
    		else
    			blob_is_null := blob_is_null + 1;
    		end if;
    	end loop;
    	dbms_output.put_line(
    	       'clob_unmatched='|| to_char(clob_unmatched,'fm999,999,999')
    	  || ', clob_matched='  || to_char(clob_matched,'fm999,999,999')
    	  || ', clob_is_null='  || to_char(clob_is_null,'fm999,999,999')
    	);
    	dbms_output.put_line(
    	       'blob_unmatched='|| to_char(blob_unmatched,'fm999,999,999')
    	  || ', blob_matched='  || to_char(blob_matched,'fm999,999,999')
    	  || ', blob_is_null='  || to_char(blob_is_null,'fm999,999,999')
    	);
    end;
    /
  4. 続いて、CONTAINS_1M_10KLOBS表をアンロードします。
    環境変数名 変更前 変更後
    LISTTABLE CONTAINS_1K_10MLOBS CONTAINS_1M_10KLOBS

    所要時間や出力されたファイルのバイト数を記録します。

  5. Reload.bat を実行し、データをロードします。所要時間を記録します。
  6. 次の無名PL/SQLブロックを実行して、すべてのデータが正しくロードできた事をチェックします。
    declare
    	cursor c1 is select * from
    		 /*CONTAINS_1K_10MLOBS*//* 所要時間 4 分 12 秒 */
    		 CONTAINS_1M_10KLOBS    /* 所要時間 17 分 34 秒 */
    	;
    	clob_unmatched binary_integer;
    	blob_unmatched binary_integer;
    	clob_matched binary_integer;
    	blob_matched binary_integer;
    	clob_is_null binary_integer;
    	blob_is_null binary_integer;
    begin
    	clob_unmatched := 0;
    	blob_unmatched := 0;
    	clob_matched := 0;
    	blob_matched := 0;
    	clob_is_null := 0;
    	blob_is_null := 0;
    	for r1 in c1 loop
    		if r1.CLOB_DATA is not null then
    			if r1.CLOB_HASH <> DBMS_CRYPTO.Hash(r1.CLOB_DATA, 1) then
    				clob_unmatched := clob_unmatched + 1;
    			else
    				clob_matched := clob_matched + 1;
    			end if;
    		else
    			clob_is_null := clob_is_null + 1;
    		end if;
    		if r1.BLOB_DATA is not null then
    			if r1.BLOB_HASH <> DBMS_CRYPTO.Hash(r1.BLOB_DATA, 1)  then
    				blob_unmatched := blob_unmatched + 1;
    			else
    				blob_matched := blob_matched + 1;
    			end if;
    		else
    			blob_is_null := blob_is_null + 1;
    		end if;
    	end loop;
    	dbms_output.put_line(
    	       'clob_unmatched='|| to_char(clob_unmatched,'fm999,999,999')
    	  || ', clob_matched='  || to_char(clob_matched,'fm999,999,999')
    	  || ', clob_is_null='  || to_char(clob_is_null,'fm999,999,999')
    	);
    	dbms_output.put_line(
    	       'blob_unmatched='|| to_char(blob_unmatched,'fm999,999,999')
    	  || ', blob_matched='  || to_char(blob_matched,'fm999,999,999')
    	  || ', blob_is_null='  || to_char(blob_is_null,'fm999,999,999')
    	);
    end;
    /
  7. オリジナルのエクスポートユーティリティーを使ってCONTAINS_1K_10MLOBS表をエクスポートします。所要時間や出力されたファイルのバイト数を記録します。
  8. CONTAINS_1K_10MLOBS表を削除し、続いてオリジナルのインポートユーティリティーを使ってCONTAINS_1K_10MLOBS表をインポートします。所要時間を記録します。
  9. オリジナルのエクスポートユーティリティーを使ってCONTAINS_1M_10KLOBS表をエクスポートします。所要時間や出力されたファイルのバイト数を記録します。
  10. CONTAINS_1M_10KLOBS表を削除し、続いてオリジナルのインポートユーティリティーを使ってCONTAINS_1M_10KLOBS表をインポートします。所要時間を記録します。
  11. データポンプ・エクスポートユーティリティーを使ってCONTAINS_1K_10MLOBS表をエクスポートします。所要時間や出力されたファイルのバイト数を記録します。
  12. CONTAINS_1K_10MLOBS表を削除し、続いてデータポンプ・インポートユーティリティーを使ってCONTAINS_1K_10MLOBS表をインポートします。所要時間を記録します。
  13. データポンプ・エクスポートユーティリティーを使ってCONTAINS_1M_10KLOBS表をエクスポートします。所要時間や出力されたファイルのバイト数を記録します。
  14. CONTAINS_1M_10KLOBS表を削除し、続いてデータポンプ・インポートユーティリティーを使ってCONTAINS_1M_10KLOBS表をインポートします。所要時間を記録します。

エクスポート・インポートユーティリティーに使用したパラメータファイルは次の通りです。

ユーティリティー CONTAINS_1K_10MLOBS CONTAINS_1M_10KLOBS
オリジナルのエクスポート userid=SCOTT/TIGER@o112utf8
tables=CONTAINS_1K_10MLOBS
file=%OUTPUT%\CONTAINS_1K_10MLOBS
log=%OUTPUT%\CONTAINS_1K_10MLOBS_exp
buffer=5000000
direct=y
userid=SCOTT/TIGER@o112utf8
tables=CONTAINS_1M_10KLOBS
file=%OUTPUT%\CONTAINS_1M_10KLOBS
log=%OUTPUT%\CONTAINS_1M_10KLOBS_exp
buffer=5000000
direct=y
オリジナルのインポート userid=SCOTT/TIGER@o112utf8
tables=CONTAINS_1K_10MLOBS
file=%OUTPUT%\CONTAINS_1K_10MLOBS
log=%OUTPUT%\CONTAINS_1K_10MLOBS_imp
buffer=5000000
commit=y
userid=SCOTT/TIGER@o112utf8
tables=CONTAINS_1M_10KLOBS
file=%OUTPUT%\CONTAINS_1M_10KLOBS
log=%OUTPUT%\CONTAINS_1M_10KLOBS_imp
buffer=5000000
commit=y
データポンプ・エクスポート USERID=SCOTT/TIGER@o112utf8
DIRECTORY=LOGICAL_BACKUP_DIR
ESTIMATE=STATISTICS
TABLES=CONTAINS_1K_10MLOBS
LOGFILE=CONTAINS_1K_10MLOBS_expdp
DUMPFILE=CONTAINS_1K_10MLOBS_%U
PARALLEL=8
USERID=SCOTT/TIGER@o112utf8
DIRECTORY=LOGICAL_BACKUP_DIR
ESTIMATE=STATISTICS
TABLES=CONTAINS_1M_10KLOBS
LOGFILE=CONTAINS_1M_10KLOBS_expdp
DUMPFILE=CONTAINS_1M_10KLOBS_%U
PARALLEL=8
データポンプ・インポート USERID=SCOTT/TIGER@o112utf8
DIRECTORY=LOGICAL_BACKUP_DIR
TABLES=CONTAINS_1K_10MLOBS
LOGFILE=CONTAINS_1K_10MLOBS_impdp
DUMPFILE=CONTAINS_1K_10MLOBS_%U
PARALLEL=8
USERID=SCOTT/TIGER@o112utf8
DIRECTORY=LOGICAL_BACKUP_DIR
TABLES=CONTAINS_1M_10KLOBS
LOGFILE=CONTAINS_1M_10KLOBS_impdp
DUMPFILE=CONTAINS_1M_10KLOBS_%U
PARALLEL=8

まとめ

  1. 実験の結果をグラフ化すると次のようになりました。MTUと表示された部分のインポートは、実際には SQL*Loader がダイレクト・パス・ロードで実行された結果を示しています。

    LOBの移動性能テスト

    LOBの移動性能テスト

  2. オリジナルのエクスポートとインポートは、1kB程度の小さなLOBを多数扱う場合に待機時間が著しく増大する事が分かりました。
  3. MTUとSQL*Loaderを使う組み合わせは、データポンプ・エクスポートとデータポンプ・インポートを使う組み合わせと同じ程度に性能が良い事が分かりました。

by 開発1号