kagamihogeの日記

kagamihogeの日記です。

APPEND_VALUESの間違った使い方をしてみる

Oracleにはダイレクト・パス・インサートというINSERTを高速に行う機能がある。それを指示するためのオプティマイザ用のヒントは2種類あり、APPENDとAPPEND_VALUESとがある。文法上の違いだけを見ると、APPENDはINSERT 〜 SELECTに使うもので、APPEND_VALUESはINSERT 〜 VALUESに使用する。しかしながら、ダイレクトパスインサートの動作的に、APPENDは使う機会はあってもAPPEND_VALUESはほとんど無いと思われる。

このエントリでは、理屈の上では無駄(むしろ邪悪)な結果になることをあえて試す。具体的には、下記のようなINSERT文を10万回ほど発行したとき、何が起きるのかを見る。

INSERT /*+ APPEND_VALUES */ INTO A(COLUMN1) VALUES('hoge');

準備

適当なテーブルを準備する。インデックスとか制約とかはなんもなし。計測用のプログラムを一度実行するたびに、テーブルをDROPしてCREATEしなおす。

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

計測用のプログラム

まず、ダイレクトパスインサートのヒント無しでただ単にINSERT一回ごとにCOMMIT一回を10万回するプログラムを準備する。

long start = System.currentTimeMillis();
try (Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.12:1521:XE", "kagamihoge", "xxxx");
    PreparedStatement sql =connection.prepareStatement("INSERT INTO A(COLUMN1) VALUES(?)");) {
    connection.setAutoCommit(false);

    for (int i = 0; i < 100000; i++) {
        sql.setString(1, RandomStringUtils.randomAlphanumeric(16));
        sql.executeUpdate();
        connection.commit();
    }

} catch (SQLException e) {
    e.printStackTrace();
}
long end = System.currentTimeMillis();
System.out.println("end" + (end - start));

ダイレクトパスインサートするときは、PreparedStatementの行を下記に変更する。

PreparedStatement sql = connection.prepareStatement("INSERT /*+ APPEND_VALUES */ INTO A(COLUMN1) VALUES(?)");) {

APPEND_VALUESのまともな使い方の一つであるPL/SQLのFORALLループ。PL/SQL良く知らないので割とテキトー。

CREATE OR REPLACE PROCEDURE DIRECT_PATH_LOAD AS 
  TYPE STRLIST IS TABLE OF CHAR(16) INDEX BY BINARY_INTEGER;
  RANDOMSTRLIST STRLIST;
  V_START TIMESTAMP;
  V_END TIMESTAMP;
BEGIN

SELECT SYSTIMESTAMP INTO V_START FROM DUAL;

FOR J IN 1..100000 LOOP
  randomStrList(J) := DBMS_RANDOM.STRING('a',16);
END LOOP;

FORALL I IN 1..100000
  INSERT /*+ append_values */ INTO A VALUES (randomStrList(I));

COMMIT;

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

結果

  1 2 3
通常INSERT 171157 141609 140593
ダイレクトパス 302109 233297 231735
PL/SQL FORALL 5703 5688 5688

というわけで、こういうAPPEND_VALUESの使い方をするとありえないくらい遅くなることが分かりました。

感想とか

なぜここまで速度に差が出るのか? 原因は色々あるんですが、ここでは各検証用プログラム動作後のテーブルの使用ブロック数を見ることにする。

select BLOCKS from user_tables where TABLE_NAME = 'A';

で、使用ブロック数を見てみると下記のようになる。

  BLOCKS
通常INSERT 370
ダイレクトパス 100498
PL/SQL FORALL 319

とまぁ、使用ブロック数が桁違いに多い。それだけのブロックを確保しなければならないのだから、かなり遅くなるのは当然と言える。ダイレクトパスインサートの説明として、よく「HWMの後ろにデータを入れる」という書かれ方がある。この検証用プログラムの動作は、INSERT一回ごとにHWMの後ろにデータを入れていってしまうことになるため、相当無駄なことをやっているに等しい。

APPEND_VALUESヒントの使いどころとしては、Oracleのリファレンスにあるように下記の場合のような限定状況下だけと思われる。

APPEND_VALUESヒントを使用すると、パフォーマンスを大幅に向上できます。次に使用方法の例を示します。

Oracle Call Interface(OCI)を使用するプログラムで、大規模な配列バインドまたは行コールバックを伴う配列バインドを使用する場合

PL/SQLで、VALUES句とともにINSERT文を使用するFORALLループを伴う多くの行をロードする場合

Oracle Database SQL言語リファレンス 11gリリース2(11.2) APPEND_VALUESヒント より抜粋

「INSERTにAPPEND_VALUESヒントとやらを付けると早くなるらしい」というあやふやな理解でダイレクトパスインサートを使用してはいけない(戒め)