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

kagamihogeの日記

kagamihogeの日記です。

OracleからのCSV出力を考える

軽くぐぐっただけなので間違っているかもしれないが、OracleにはCSV出力の機能なりAPIなりは備わっていない。ただし、SQL*PlusのコマンドであるとかPL/SQLを使うとかでCSV出力は実現出来る。今回のエントリでは、自分が調べられた範囲内でCSV出力の方法を色々試してみようと思う。

試す方法の要約は下記の通り。

  • PL/SQLでUTL_FILEを使用してCSV出力(ネイティブコンパイル有効)
  • Oracleとは別マシンから、JDBD経由でCSV出力
  • Oracleとは別マシンから、SQL*PlusでSPOOLを使用してCSV出力
  • Oracleとは別マシンから、SQL Developerのエクスポート機能を使用してCSV出力

以下では、計測対象のテーブル構成に続き、それぞれの方法のプログラム等を示す。そのあと、実行時間の比較と感想を述べる。

計測対象のテーブル構成と実行時間の計測方法

サンプルスキーマ(hr)に入っているEMPLOYEESテーブルを使用する。

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

レコード数をおおむね100万件(107 * 1000 * 10)に増やす。

CREATE TABLE employees
AS
SELECT hr.employees.*
FROM
  hr.employees,
  (SELECT 1 FROM all_catalog WHERE ROWNUM <= 1000),
  (SELECT 1 FROM all_catalog WHERE ROWNUM <= 10);

速度計測はこのテーブルの全行CSV出力にかかる時間を計測する。実際に実行するSQLとしては、下記となる。また、吐き出されるCSVファイルはおおむね90MBとなる。

SELECT * FROM employees;

また、計測用プログラムを走らせる前に下記コマンドでバッファキャッシュをクリアする。

ALTER SYSTEM FLUSH BUFFER_CACHE;

計測用プログラム

Java
public class OutputCSV {

    public static void main(String[] args) throws Exception {
        long start = System.currentTimeMillis();

        final String br = System.getProperty("line.separator");
        final String comma = ",";
        
        final String sqlStr = "SELECT * FROM employees";
        try (Connection connection = DriverManager.getConnection(
            "jdbc:oracle:thin:@192.168.0.20:1521:XE", "kagamihoge", "xxxx");
            PreparedStatement sql = connection.prepareStatement(sqlStr);) {
            sql.setFetchSize(100);

            try (ResultSet r = sql.executeQuery();
                BufferedWriter writer = Files.newBufferedWriter(
                    Paths.get("employees.csv"), Charset.defaultCharset())) {
                
                while (r.next()) {
                    StringBuilder b = new StringBuilder();
                    b.append(r.getString("employee_id")).append(comma)
                        .append(r.getString("first_name")).append(comma)
                        .append(r.getString("last_name")).append(comma)
                        .append(r.getString("email")).append(comma)
                        .append(r.getString("phone_number")).append(comma)
                        .append(r.getString("hire_date")).append(comma)
                        .append(r.getString("job_id")).append(comma)
                        .append(r.getString("salary")).append(comma)
                        .append(r.getString("commission_pct")).append(comma)
                        .append(r.getString("manager_id")).append(comma)
                        .append(r.getString("department_id"))
                        .append(br);
                    writer.write(b.toString());
                }
            }
        }

        long end = System.currentTimeMillis();
        System.out.println(end - start);
    }
}

setFetchSize(100)をしている以外、特にこれといった工夫はしていない。なお、上記のプログラムはOracleが動いているマシンとは物理的に異なるマシンで実行する。

PL/SQL(UTL_FILE)

Oracle Database PL/SQLパッケージおよびタイプ・リファレンス11g リリース2(11.2) 224 UTL_FILEを使用してCSV出力を行う。

また、Oracle11gのPL/SQLのネイティブコンパイル - kagamihogeの日記の手順でネイティブコンパイルを有効にした。

create or replace PROCEDURE OUTPUT_CSV AS 
  outputCSVFileHandle UTL_FILE.FILE_TYPE;
  CURSOR employeesCursor IS SELECT * FROM employees;
  employeesRow employeesCursor%ROWTYPE;
BEGIN
  outputCSVFileHandle := UTL_FILE.FOPEN('CSV_OUTPUT_DIR','employees.csv','w',100);

  OPEN employeesCursor;
  LOOP
    FETCH employeesCursor INTO employeesRow;
    EXIT WHEN employeesCursor%NOTFOUND;
    UTL_FILE.PUT_LINE(outputCSVFileHandle,
      employeesRow.employee_id
      ||','|| employeesRow.first_name
      ||','|| employeesRow.last_name
      ||','|| employeesRow.email
      ||','|| employeesRow.phone_number
      ||','|| employeesRow.hire_date
      ||','|| employeesRow.job_id
      ||','|| employeesRow.salary
      ||','|| employeesRow.commission_pct
      ||','|| employeesRow.manager_id
      ||','|| employeesRow.department_id);
  END LOOP;
  CLOSE employeesCursor;
  
END OUTPUT_CSV;

また、比較のためにバルクフェッチを使用したバージョンもついでに作成した。

create or replace PROCEDURE OUTPUT_CSV_WITH_BULK AS 
  BULK_SIZE CONSTANT PLS_INTEGER := 100;
  outputCSVFileHandle UTL_FILE.FILE_TYPE;
  CURSOR employeesCursor IS SELECT * FROM employees;
  TYPE typeEmployees IS TABLE OF employeesCursor%ROWTYPE INDEX BY BINARY_INTEGER;
  
  employeesRows typeEmployees;
BEGIN
  outputCSVFileHandle := UTL_FILE.FOPEN('CSV_OUTPUT_DIR','employees.csv','w',100);

  OPEN employeesCursor;
  LOOP
    FETCH employeesCursor BULK COLLECT INTO employeesRows LIMIT BULK_SIZE;
    EXIT WHEN employeesRows.COUNT = 0;
    FOR i IN 1 .. employeesRows.COUNT
    LOOP
    UTL_FILE.PUT_LINE(outputCSVFileHandle,
      employeesRows(i).employee_id
      ||','|| employeesRows(i).first_name
      ||','|| employeesRows(i).last_name
      ||','|| employeesRows(i).email
      ||','|| employeesRows(i).phone_number
      ||','|| employeesRows(i).hire_date
      ||','|| employeesRows(i).job_id
      ||','|| employeesRows(i).salary
      ||','|| employeesRows(i).commission_pct
      ||','|| employeesRows(i).manager_id
      ||','|| employeesRows(i).department_id);
    END LOOP;
  END LOOP;
  CLOSE employeesCursor;
  
END OUTPUT_CSV_WITH_BULK;

SQL*Plus(SPOOL)

ORACLE/オラクルSQLリファレンス(SQLPLUS)などを参考にし、SQL*Plusユーザーズ・ガイドおよびリファレンスリリース11.2 8 SQL*Plusのチューニング SQL*Plusスクリプトのチューニングを参考にしてチューニングを施したSQL*Plus用のスクリプトCSV出力を行う。

下記のうち、SET TIMING ONは実行時間計測のために入れているので、実際には不要である。また、実行時間の計測は2種類おこなう。2種類とは、LINESIZE 100(百)とLINESIZE 1000(千)を意味する。

SET ARRAYSIZE 100
SET FLUSH OFF
SET LINESIZE 100
SET PAGESIZE 0
SET SERVEROUTPUT OFF
SET SQLPROMPT OF
SET FEEDBACK OFF
SET TIMING ON
SET TERMOUT OFF
SET TRIMSPOOL ON
SPOOL C:\sqlplustest\employees.csv
SELECT
  e.employee_id
  ||','|| e.first_name
  ||','|| e.last_name
  ||','|| e.email
  ||','|| e.phone_number
  ||','|| e.hire_date
  ||','|| e.job_id
  ||','|| e.salary
  ||','|| e.commission_pct
  ||','|| e.manager_id
  ||','|| e.department_id
FROM  employees e;
SPOOL OFF
QUIT

なお、上記のスクリプトOracleが動いているマシンとは物理的に異なるマシンで実行する。

SQL Developerのエクスポート

SQL Developerのエクスポート機能を使用してCSV出力を行う。
f:id:kagamihoge:20140227202318p:plain
f:id:kagamihoge:20140227202328p:plain
f:id:kagamihoge:20140227202334p:plain

実行時間

それぞれの方法を、一部の例外を除き、三回ずつ行った結果が以下の通り。

sql*plus LINESIZE 1000(千)は一度しか試行していないが、めんどくさくなって省略した。ARRAYSIZEは100と、明記してない場合はデフォルトの15で実行している。SQL Developerのエクスポート機能は、実行時間の表示は行ってくれない。そのため、実行時間の確認は目視で時計の秒針を数える方法で行った。

種類 1 2 3
Java 19.953 19.985 19.985
UTL_FILE 77.219 77.157 77.39
UTL_FILE(バルクフェッチ) 48.672 48.453 48.781
sql*plus LINESIZE 100 ARRAYSIZE 100 33.53 33.60 33.56
sql*plus LINESIZE 100 55.98 56.04 55.20
sql*plus LINESIZE 1000 01:27.64 なし なし
SQL Developerエクスポート 01:22.00 01:20.00 01:20.00

Javaが圧倒的とかSQL*PlusのテキトーなスクリプトPL/SQL上回るとかどういう……

感想とか

まず、Javaが異様に速いという結果に困惑した。事前の予想としては、ネットワーク通信を介さないPL/SQLが最速だろう、と考えていた。同一HDDでのディスクtoディスクの処理は不利ではあろうが、ネットワーク通信が間に入るよりかはマシだろう、と。しかし現実には、バルクフェッチにした分だけ速くなりはしたものの、Javaの速度には程遠い結果となった。

こうなる要因とは何か。ハードウェアの性能差が大きいのでは? と考えている。ハードのスペックいちいち書くの面倒なので省略するけど、ウチのOracleが動いてるマシンはまぁぶっちゃけかなりショボいスペックである。対して、Javaを実行するクライアント機は、CPUにしろディスクにしろ遥かに性能が高い。よって、仮説ではあるが、ネットワーク通信してもなお、Oracleのマシンより遥かに性能がマシなクライアントマシンでCSV出力をやらせる方が速くなるのでは? と。

今回の計測結果からその裏づけが取れないか、を考える。まず、バルクフェッチのコードからCSV出力を外してみる。このSQLおよびフェッチの実行時間は約4秒程度でしかないため、CSV出力には引き算すると48-4=約44秒程度かかっている目算になる。そこで、同様にJavaCSV出力の部分を外して実行時間を見ると約9秒程度となった。そのため、SQL実行およびフェッチの部分だけを見ると、ネットワーク通信の生じないPL/SQLがやはり有利である。

しかし、JavaCSV出力にかかる時間は引き算すると19-9=約10秒程度でしかない。Oracle上でCSV出力は約44秒なのを考えると、SQL実行およびフェッチの約5秒差程度の速度優位は埋れてしまうことになる。以上のことから、ネットワーク通信の不利さを覆してしまう程度には、クライアント機とのマシンスペック差が響いている、と言えるのでは、と考えられる。SQL*Plusもクライアント側で実行しているので、事情は同様と考えられる。

ただまぁ……本番環境でOracleサーバよりもクライアント機のほうがスペックが格段上、というのはちょっと考えづらい*1。なので、今回のエントリの実行時間比較は、前提条件の置き方などのやり方がちょっとまずかったかもなぁ、と反省している。

Oracleが稼動しているマシンスペックが充分なものであれば、おそらく違う結果になるハズである。

とはいえこの点から学べることとは何があるだろうか。まず、ある環境である方法が速いかどうかは、計測してみるまで分からない、点が上げられる。今回はPL/SQLが速いだろうと踏んだわけだが、実際には異なる結果となった。環境に依存するからこの計測結果に余り意味は無いとしても、環境というのはそれこそ千差万別である。現実世界の業務では、その千差万別の環境を実際には相手するわけで、まず計測ありき、と昔から言われるのはその通りだと感じた。

また、CSV出力の実行時間は大きく二種類に分けられる。それは、SQL実行およびフェッチとファイル出力である。どっちもディスクI/Oが絡むとはいえ、ビミョーに色合いの違う処理である。前者はOracle固有だが、後者はそうでは無い。たとえば、Oracleサーバが常に高い負荷がかかっていたとき、ファイル出力は別マシンにやらせた方が速くなるかもしれない。まぁ計測してみないと分からないけども。とはいえ別ブロックに分けられると認識出来れば、手の内も拡がる。なお、速度アップは最終的にはCSV 出力を10倍高速にする方法 - オラクル・Oracle PL/SQL 入門に行き着くのでは、と思われる。

SQL*Plusは、まずLINESIZEに実行時間が左右される。行が途切れるのは論外としても、あまりデカい幅を取るとただそれだけで実行時間が悪化する。ARRAYSIZEは、リファレンスでは「SQL*PlusおよびOracle Databaseの最近のバージョンでは、ARRAYSIZEによる効果はほとんどありません。」と書いてあるものの、この環境では100くらいまでは実行時間の改善が見られた。300くらいまでは僅かずつ改善があったが、1000にするとほぼ変わらない結果となった。

CSV出力をクライアント側でやる、という点ではJavaSQL*Plusも同様である。ただ、実行時間はJava優位。そのため、SQL*Plusは大量のデータをさばく用途のツールではないのだろう、と考えられる。

実行時間についてアレコレ書いてきたが、現実の業務では手軽さも重要な点である。SQL*Plus経由の方法はカンタンだし伝統もあるのでネットでの情報も多い。LINESIZEにさえ気を付けてやればそこその速度は出る。ただ、さすがに数千数億行とかよほど大規模なデータを扱うには力不足ではあるだろうけれど。

お手軽さで言えば、SQL Developerのエクスポート機能もある。速度的にはこの中で一番ビミョーなのだが、GUIでぽちぽちっとするだけでOKなのは利点と言える。PL/SQLにしろJavaにしろ作り込むには心構えが必要だが、それに比べるとSQL*Plusやツールの機能を使うのはお手軽である。

*1:Javaなどのアプリケーションサーバとなると話はまた別とは思うが