PL/SQL処理結果出力方法とかかる時間の目安

PL/SQLの処理結果を出力する方法となると、DBMS_OUTPUTで出力したり、UTL_FILEで出力したり、結果テーブルを作成してINSERTしたりとあるような無いような。結果がCSV形式で必要なら、TABLEにINSERTするかUTL_FILEを使う事になると思う。それぞれのやり方でどの程度時間に差が出るかをざっくり計測してみた。

• 10,000件処理
  • spool
    • DBMS_OUTPUT.PUT_LINE
      • 0.031秒
  • TABLE
    • (INSERT * 10,000) + COMMIT
      • 0.375秒
    • (INSERT + COMMIT) * 10,000
      • 0.890秒
  • UTL_FILE
    • (FOPEN + PUT_LINE + FCLOSE) * 10,000
      • 10.906秒
    • FOPEN + (PUT_LINE * 10,000) + FCLOSE
      • 0.125秒
    • FOPEN + (PUT_LINE + FFLUSH * 10,000) + FCLOSE
      • 446.422秒
• 1,000件処理
  • UTL_FILE
    • FOPEN + (PUT_LINE + FFLUSH * 1,000) + FCLOSE
      • 17.109秒
    • (FOPEN + PUT_LINE + FFLUSH + FCLOSE) * 1,000
      • 17.515秒

予想外なのか、そうでもないのか、spoolが断トツ。とはいえ、大量件数の複数のファイルを出力したり、画面には別のものを表示したいとか色々と制約があると思うし、とりあえずは横に避けておく。現実的に考えると、(INSERT * 10,000) + COMMITかOPEN + (PUT_LINE * 10,000) + FCLOSEになるだろうか。
FFLUSHをやったのは、現在性能改善要求が出ているプログラムで入っていたから。当然それの作りは、(FOPEN + PUT_LINE + FFLUSH + FCLOSE) * nな訳ですが。

計測に使ったやつ。細かい突っ込みは勘弁。

--DBMS_OUTPUT.PUT_LINE（spoolやtrimspoolは別途）
BEGIN
    DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
    FOR i IN 1 .. 10000 LOOP
        DBMS_OUTPUT.PUT_LINE(i || ',' ||  'A' || TO_CHAR(i, 'FM00000'));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
END;
/
--INSERT
BEGIN
    DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
    FOR i IN 1 .. 10000 LOOP
        INSERT INTO DATA_TABLE VALUES (i, 'B' || TO_CHAR(i, 'FM00000'));
        --COMMIT;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
END;
/
--UTL_FILE.PUT_LINE
DECLARE
    VHANDLE UTL_FILE.FILE_TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
    VHANDLE := UTL_FILE.FOPEN('WORK_DIR', 'output.txt', 'A', 32767);
    FOR i IN 1 .. 10000 LOOP
        --VHANDLE := UTL_FILE.FOPEN('WORK_DIR', 'output.txt', 'A', 32767);
        UTL_FILE.PUT_LINE(VHANDLE, i || ',' || 'C' || TO_CHAR(i, 'FM00000'));
        --UTL_FILE.FFLUSH(VHANDLE);
        --UTL_FILE.FCLOSE(VHANDLE);
    END LOOP;
    UTL_FILE.FCLOSE(VHANDLE);
    DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
END;