Top > PLSQL プログラミング入門（２） > CSV 出力を１０倍高速にする方法

大容量の CSV 出力を１０倍高速にするには…

SQL*Plus から SPOOL を使用して CSV を出力するよりも１０倍くらい高速できそうな方法を試行してみる。

PL/SQL において以下の手法を使って CSV 出力処理を高速化する。

  • PL/SQL 上でマルチタスクの CSV 出力処理を行なう

    PL/SQL においてマルチタスクを実現するために CSV 作成・出力処理を複数のチャンク（塊り）に分け、それぞれにジョブ・プロセスを生成して並列実行処理を行なう。

  • ローカル・デバイスにファイル出力を行なう

    CSV データをサーバー上のファイルに出力することでネットワークなど中間処理を極力減らす。
    また、UTL_FILE パッケージ を使ったファイル出力は SQL*Plus 上における TRIMSPOOL 付きの SPOOL 処理に比べて効率的である。

  • PL/SQL をマシン語に変換する(Native Compile)

    Oracle 11g から面倒な設定や別途必要だった C コンパイラも要らずに初期化パラメータで PL/SQL コードをネイティブ・コードにして実行することができる。(※1)
    今回の UNLOAD 処理は基本的に SQL の実行とレコードのフェッチ処理しかないのでファイル出力系統（UTL_FILE）のネイティブ化による高速化くらいにしかメリットは無さそう。
    (※1) 組み込み UTL_FILE パッケージを Native 化するには DB全体レベルの再コンパイル作業（アップグレード）が必要。

  • ダイレクト・パス・リードを行なう

    パラレル・クエリーオプションがあれば、パラレルクエリーを行なうことでバッファ・キャッシュを経由しないアクセスが可能となる。
    この方法で処理をパラレル化すると後続タスクにおいてキャッシュの恩恵がなくなる。この場合、チャンクにはパーティショニングを使ったタスク分割が効果的になると思われる。 SELECT ... FROM table_name PARTITION partition_name
    スタンダードでも使用できるように、ダイレクト・パス・リードを制御するヒント句が欲しいところ。

CSV を高速に生成するために必要なプログラム

対象ファイル名漢字コード
PARALLEL_EXECUTE パッケージfileparallel_execute.sql.1Shift JIS
UNLOAD パッケージfileunload.sql.5Shift JIS

PARALLEL_EXECUTE パッケージ・モジュール

ジョブを使用してマルチタスク処理させるためのフロント・モジュール
このモジュールから UNLOAD のプロシージャを複数個、並列化して呼び出す。

仕様と制限

物理 ROWID を使用してデータの格納ブロックによってチャンク分割を行なっている。
論理 ROWID を使用する 索引構成表、外部表、および、ビュー などには使用できないため、別の分割方法を組み込んでカスタマイズする必要がある。NTILE 分析関数などは簡単なバケット分割方法であるがパフォーマンスを上げるひと工夫が必要だろう。
BIGFILE 表領域 に対してもソースの変更作業が必要となる(※2)
DBMS_SCHEDULER 組み込みパッケージを使用しているため、Oracle 10g 以上の環境が必要。
ファイルの結合処理は含まれていない：ファイルを１つに結合したい場合には、呼び出しシェルで行なう、SQL*Plus HOST コマンドを実行する、または、DBMS_SCHEDULER から OS のコマンドを実行し結合する方法などがある。

(※2) BIGFILE 表領域 の場合には DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ⇒ DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID, 'BIGFILE') に変更する。

並列実行がうまく動作しない場合には 120行 あたりに
debug 用の並列化させない＆エラー出力する実行コードが含まれるのでデバッグに使ってください。

-- for debug (シリアルで実行される＆エラー発生時に例外が発生する＆JOBオートドロップはされない）
-- DBMS_SCHEDULER.RUN_JOB(job_names);
-- DROP_TASK(job_names);
-- for release（パラレルで実行される＆エラー発生時してもキャッチできない）
DBMS_SCHEDULER.ENABLE(job_names);
-- 起動したジョブの終了待ち＆出力件数取得（タイムアウト６時間）
FOR i in 1..parallel_level LOOP
   status := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, 21600);
  DBMS_PIPE.PURGE(pipe_name);
  略

UNLOAD パッケージ・モジュール

CSV のデータを生成と書き出しを行なうライブラリ

CSV を生成するための標準機能は用意されていない。パッケージの詳細については 汎用 CSV 出力プロシージャ を参照

実行に必要な権限と作業

ファイルを出力するための ディレクトリ・オブジェクトの作成（例：DATA_UNLOAD_DIR）　
DBMS_PIPE 組み込みパッケージを実行する権限の付与（親プロセスと生成プロセスとの同期に使用）
JOB を作成する CREATE JOB システム権限の付与

-- コマンドを実行するユーザー (rivus)
SQL> conn rivus/passwd
SQL> CREATE DIRECTORY DATA_UNLOAD_DIR AS 'D:\CSV';   
--                                         └─ DBのデータファイルと異なる HDD がよい
SQL> conn / as sysdba
SQL> GRANT EXECUTE ON DBMS_PIPE TO rivus
SQL> GRANT CREATE JOB TO rivus;

GRANT EXECUTE ON DBMS_PIPE TO username するときに他のセッションで DBMS_PIPE を使用し続けているものがいるとハングアップしているように見えることがあるらしいので要注意。

テーブル名 employees を 並列度４で CSV 出力する例

employees テーブル上の 1000万レコードを CSV 出力する例
ファイルはディレクトリ・オブジェクト 'DATA_UNLOAD_DIR' 以下に出力される。

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
SQL> alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ssxff3';
SQL> set serveroutput on
SQL> execute parallel_execute.user_task('employees', 'DATA_UNLOAD_DIR', 4);
--                                         ↑        ↑                ↑
--                                  テーブル名     ディレクトリOBJ名     並列度
…
export_employees_1.csv=2499883
export_employees_2.csv=2499983
export_employees_4.csv=2500027
export_employees_3.csv=2500107
 
PL/SQLプロシージャが正常に完了しました。
 
経過: 00:02:21.40
SQL>

代表的なテーブルでの１千万レコードの所要時間比較

Windows XP + Oracle 11g R2 （32bit / DBCA 汎用：パーティショニング未使用）
CPU: Corei7(Nehalem 世代 4コア) / Mem: 4GB (DB:512MB) / HDD: 7500 rpm / SSD: 250 MB/s (read)

sqlplus + spool にて employees 1000万件の出力 約 21分

parallel_execute.user_task による ４並列にて employees 1000万件の出力 約 2分30秒

(※) SQL*Plus + spool の処理と比べて約１０倍になっているが、サーバーの CPU のスペックおよびデータベース・データファイルのドライブ、出力先のドライブのスペックによって相当変動する。

ドライブによる比較検証（未実施 '-' ）

データファイルのデバイスを SDD、HDD（SATA）、出力デバイスを RAM ディスク、HDD（データファイルの HDD とは違う）の組み合わせにおいての実験。
※ 試験環境が家庭向けデスクトップＰＣなので UNIX 系で RAID構成の DBサーバ専用機では異なる結果が出る可能性があります。

タスク並列度データファイル・メディア to CSV 出力先メディアファイルサイズ
SSD to RAMHDD to RAMSSD to HDDHDD to HDD
SQL*Plus（ローカル接続）
  └   linesize 32767		×1> 6時間> 6時間> 6時間> 6時間933MB
SQL*Plus
  └   linesize 1000		×119分50秒21分00秒19分24秒19分28秒
SQL*Plus
 └   linesize 200		×110分30秒---
SQL*Plus
 └   linesize 200 , arraysize 100		×19分43秒--11分43秒
UNLOAD×19分27秒9分29秒9分40秒11分05秒933MB
UNLOAD×25分50秒5分52秒5分43秒9分11秒466MB×2
UNLOAD×42分33秒2分30秒7分38秒10分03秒233MB×4
UNLOAD×82分44秒2分45秒10分54秒11分06秒115MB×2
116MB×2
117MB×4
UNLOAD （NATIVE コンパイル）
 └ DB 全体も NATIVE で再コンパイル		×19分12秒--9分41秒933MB
UNLOAD （同上）×25分40秒--7分34秒466MB×2
UNLOAD （同上）×42分29秒--9分37秒233MB×4
UNLOAD （同上）×82分42秒--10分26秒115MB×2
116MB×2
117MB×4
UNLOAD （NATIVE コンパイル）
 └ さらにダイレクト・パス・リード(※3)		×2---6分03秒466MB×2
UNLOAD （同上）×41分46秒1分45秒--233MB×4

(※3) 副次的に「ダイレクト・パス・リード」を行なわせるために強制的に「パラレル・クエリー」モードにする。ソースコード 75行目の ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2; を有効にする。（要：エンタープライズ＋パラレルクエリー・オプション）

検証結果

  • SQL*Plus はバージョン 11g R2 でも依然として１レコードのサイズが大きなデータを（TRIM 付きで）取り扱う場合において、効率の良いとは言えないアプリケーションであることに変わりがない。
  • SQL*Plus と SPOOL で効率的に CSV 出力するにはテーブル単位に最大レコード長を調べ、個別にチューニングしなければならない。
  • SQL*Plus のレコードのフェッチに関しては（かなりの昔から比べて）効率は改善されているようで ARRAYSIZE によるチューニング手法はあまり効果がない。
  • 入・出力デバイスが HDD となった場合にそれぞれ単一ドライブのせいか性能の頭打ちが激しい。(SCSI の場合には改善の可能性があるかもしれない）
  • HDD に複製するコストを含めても一度 RAM ディスクに書き出すと、飛躍的にパフォーマンスが向上する。
    高速な RAID コントローラと高速なストレージ群をもった製品を導入することも、かなりパフォーマンスを向上すると期待できる。
  • HDD 書き込みに対しては同期回数を減らすことができそうな RAW によるファイル出力に変更すると改善できるかもしれない。

employees テーブルは HR サンプルスキーマのもの
データは制約をはずしてサンプルスキーマのデータの複製の繰り返し。

CREATE TABLE RIVUS.EMPLOYEES
(
    EMPLOYEE_ID                    NUMBER(6,0),
    FIRST_NAME                     VARCHAR2(20),
    LAST_NAME                      VARCHAR2(25) NOT NULL,
    EMAIL                          VARCHAR2(25) NOT NULL,
    PHONE_NUMBER                   VARCHAR2(20),
    HIRE_DATE                      DATE NOT NULL,
    JOB_ID                         VARCHAR2(10) NOT NULL,
    SALARY                         NUMBER(8,2),
    COMMISSION_PCT                 NUMBER(2,2),
    MANAGER_ID                     NUMBER(6,0),
    DEPARTMENT_ID                  NUMBER(4,0)
)

PARALLEL_EXECUTE パッケージ

ソース抜粋：パッケージ宣言部のみなのでコピーしても動きません。内容が古い場合もあります。

  1. CREATE OR REPLACE PACKAGE PARALLEL_EXECUTE
  2. IS
  3. /*********************************************************************/
  4. -- ファンクション名 USER_TASK
  5. -- パラメータ ：(I  ) export_table      CSV出力するテーブル
  6. --            ：(I  ) dir_name          ディレクトリ・オブジェクト名
  7. --            ：(I  ) parallel_level    並列度
  8. -- 戻り値     ：なし
  9. -- Copyright  ：rivus.jp
  10. /*********************************************************************/
  11. -- constatnt
  12. --
  13. --
  14. PROCEDURE USER_TASK(
  15.   export_table  IN VARCHAR2,
  16.   dir_name  IN VARCHAR2 := 'DATA_PUMP_DIR',
  17.   parallel_level  IN POSITIVEN := 4
  18. );
  19. PROCEDURE USER_TASK_CHILD(
  20.   child_no  IN NUMBER,
  21.   parallel_level  IN NUMBER,
  22.   table_name  IN VARCHAR2,
  23.   dir_name  IN VARCHAR2,
  24.   pipe_name  IN VARCHAR2 := NULL
  25. );
  26. END;
  27. /
 

関連事項

Table of Contents

CSV 出力を１０倍高速にする方法の関連トピックス

  1. PL/SQL プログラミング入門（２）
  2. 汎用 CSV 出力プロシージャ

SHIFT the Oracle 最近のトピックス

  1. 各国語対応可能な祝日、休日一覧を作成する
  2. STANDARD_HASH
  3. CSV 出力を１０倍高速にする方法
  4. V$SESSION（詳細）
  5. DBMS_CRYPTO 使用方法 〜暗号化〜
  6. DBMS_CRYPTO 使用方法 〜ハッシュ〜
日本オラクル
■ 日本オラクル 株式会社
Oracle Japan
■ オラクルマスター資格 （オラクルマスターとは）
Oracle University
■ オラクルサポートセンター
Oracle セキュリティアラート情報