kagamihogeの日記

kagamihogeの日記です。

Oracleのシーケンス・採番テーブル・MAX+1

20130426 追記 実行時間の計測方法がおかしかったので書き直し

あるテーブルの列に連番が欲しいとき、Oracleではシーケンス(順序とかSEQUENCEとか)を使うのが定番。採番テーブルでも同様に連番を作れるが、シーケンスを使うのが定石と言われている。その理由は、同時アクセスが増えると採番テーブルはロックが多発しパフォーマンスが落ちるから、と言われている。

というわけでどの程度遅くなるもんなのかを実際にやってみる。なお、このエントリでは最初から最後までの実行時間だけを計測するのと、ごくごくシンプルなプログラムによる比較のため、より複雑な事情の絡む本番環境でも同様の結果を生むかどうかの裏付けにはならないと思うことを先に書いておきます。

テーブル準備

採番テーブル

SQL Developerのテーブル作成機能でポチポチと作って生成されたDDLそのまんまコピペ。

CREATE TABLE SAIBAM_TABLE 
(
  SEQ_KEY VARCHAR2(20) NOT NULL 
, SEQ_NO NUMBER(10, 0) NOT NULL 
, CONSTRAINT SAIBAM_TABLE_PK PRIMARY KEY 
  (
    SEQ_KEY 
  )
  ENABLE 
);

データを2件入れておく。使うのはkey1だけだけど。

INSERT INTO "KAGAMIHOGE"."SAIBAM_TABLE" (SEQ_KEY, SEQ_NO) VALUES ('key1', '0');
INSERT INTO "KAGAMIHOGE"."SAIBAM_TABLE" (SEQ_KEY, SEQ_NO) VALUES ('key2', '0');

採番した値を突っ込んでいく方のテキトーなテーブル。

CREATE TABLE TARGET_TABLE 
(
  SEQ_NO_KEY NUMBER(10, 0) NOT NULL 
, RANDOM_VALUE VARCHAR2(16) NOT NULL 
, CONSTRAINT TARGET_TABLE_PK PRIMARY KEY 
  (
    SEQ_NO_KEY 
  )
  ENABLE 
);

実際に使うときはこんな感じのSQLトランザクションの中で発行する。

UPDATE SAIBAM_TABLE SET SEQ_NO = SEQ_NO+1 WHERE SEQ_KEY = 'key1'
INSERT INTO TARGET_TABLE(SEQ_NO_KEY, RANDOM_VALUE) VALUES((SELECT SEQ_NO FROM SAIBAM_TABLE WHERE SEQ_KEY = 'key1'), 'random7890123456')
MAX + 1

MAX + 1で連番を増やしていくパターンのSQL。データ入れるテーブルのDDLは採番テーブルと同様なので省略。ちなみにこのSQL色々問題があるんですがそれは後で触れる予定。

INSERT INTO TARGET_3_TABLE(SEQ_NO_KEY, RANDOM_VALUE) VALUES ((SELECT NVL(MAX(SEQ_NO_KEY),0)+1 FROM TARGET_3_TABLE), 'random7890123456');
シーケンス

SQL Developerのテーブル作成機能のオプションで列シーケンスを選んで作られたDDLをそのままコピペ。

CREATE SEQUENCE  "KAGAMIHOGE"."TARGET_2_TABLE_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

トリガも自動的に作ってくれるので今回はこれを使う。

create or replace 
TRIGGER TARGET_2_TABLE_TRG BEFORE INSERT ON TARGET_2_TABLE 
FOR EACH ROW 
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF :NEW.SEQ_NO_KEY IS NULL THEN
      SELECT TARGET_2_TABLE_SEQ.NEXTVAL INTO :NEW.SEQ_NO_KEY FROM DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;

実際にデータ入れるときのSQLはこんな感じ。シーケンスから値取ってくるのはトリガ任せで。

INSERT INTO TARGET_2_TABLE(RANDOM_VALUE) VALUES('random7890123456');

計測用のプログラム

採番テーブル
public class SaibanTable implements Callable<Integer> {
    @Override
    public Integer call() throws Exception {
        try (Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@192.168.0.12:1521:XE", "kagamihoge", "xxxx");
                PreparedStatement updateSeq = connection
                        .prepareStatement("UPDATE SAIBAM_TABLE SET SEQ_NO = SEQ_NO+1 WHERE SEQ_KEY = 'key1'");
                PreparedStatement insertData = connection
                        .prepareStatement("INSERT INTO TARGET_TABLE(SEQ_NO_KEY, RANDOM_VALUE) VALUES((SELECT SEQ_NO FROM SAIBAM_TABLE WHERE SEQ_KEY = 'key1'), ?)");) {
            connection.setAutoCommit(false);

            for (int i = 0; i < 1000; i++) {
                updateSeq.executeUpdate();
                insertData.setString(1,
                        RandomStringUtils.randomAlphanumeric(16));
                insertData.executeUpdate();
                TimeUnit.MILLISECONDS.sleep(5);

                connection.commit();
            }
        } catch (SQLException | InterruptedException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

JDBC直接叩きの関係上ゴチャゴチャやってますが、基本的にはSQL二つ実行してるだけです。
それと、一応オンライン処理を想定ということで、そのSQL実行し終わったらトランザクション終了してます。コネクションはコネクションプーリング想定ってことで使いまわし(本音はイチイチ接続し直すの面倒だったからだけど) より重要なのは1スレッド1Oracleセッション貼ることなんで良しとします。それだとPreparedStatement使うのもアレなんだけど、まぁコレも良しとします。スリープは、オンライン処理における1トランザクションの処理時間のつもりです。5msに深い意味はありません。テキトーに決めました。
ループが1000なのも特に意味は無いです。まぁ最低そんくらい回せば差が出るかな〜くらいな感じ。

MAX + 1
public class MAXPlus1 implements Callable<Integer> {
    @Override
    public Integer call() throws Exception {
        try (Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@192.168.0.12:1521:XE", "kagamihoge", "xxxx");
                PreparedStatement insert = connection
                        .prepareStatement("INSERT INTO TARGET_3_TABLE(SEQ_NO_KEY, RANDOM_VALUE) VALUES ((SELECT NVL(MAX(SEQ_NO_KEY),0)+1 FROM TARGET_3_TABLE), ?)");) {
            connection.setAutoCommit(false);
            for (int i = 0; i < 1000; i++) {
                insert.setString(1, RandomStringUtils.randomAlphanumeric(16));
                try {
                    insert.executeUpdate();
                } catch (SQLIntegrityConstraintViolationException e) {
                    i--;
                }
                TimeUnit.MILLISECONDS.sleep(5);
                connection.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

基本的な作りは採番テーブルと同じ。
実はMAX+1で連番振ってくやり方、このエントリ書くにあたってはじめて使ったんですが。2セッション以上でやると一意制約違反になるんですよね……知らなかった。もちろんそれぞれのトランザクションがぶつからなければ発生しないけど。それじゃあ分離レベルをSERIALIZABLEにすれば大丈夫かっていうと、そうでもない。その理由はこのエントリの主旨から外れていくこともあり省略。参考:トランザクション・分離レベル - オラクル・Oracleをマスターするための基本と仕組み
そういうわけで、一意制約違反で例外が起きたらリトライさせている。ループカウンタをマイナスしてるのは、良識ある開発者ならマネしないと思うので目をつぶって下さい。
何の工夫もなく再実行させてるので、スレッド数増やすとそれはそれは例外発生しまくりなんですが、とりあえず無視します。

シーケンス
public class Sequence implements Callable<Integer> {
    @Override
    public Integer call() throws Exception {
        try (Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@192.168.0.12:1521:XE", "kagamihoge", "xxxx");
                PreparedStatement insert = connection
                        .prepareStatement("INSERT INTO TARGET_2_TABLE(RANDOM_VALUE) VALUES(?)");) {
            connection.setAutoCommit(false);

            for (int i = 0; i < 1000; i++) {
                insert.setString(1, RandomStringUtils.randomAlphanumeric(16));
                insert.executeUpdate();
                TimeUnit.MILLISECONDS.sleep(5);
                connection.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

これは特にコメント無し。

集計
public class Main {
    public static void main(String[] args) {
        long s = System.currentTimeMillis();
        
        final int THREAD_SIZE = 5;
        List<Future<Integer>> results = new ArrayList<>();
        ExecutorService threadPool = Executors.newFixedThreadPool(THREAD_SIZE);
        for (int i=0; i<THREAD_SIZE; i++) {
            results.add(threadPool.submit(new SaibanTable()));
        }
        for (Future<Integer> result : results) {
            try {
                result.get();
            } catch (InterruptedException | ExecutionException e) {
                e.printStackTrace();
            }
        }
        threadPool.shutdown();
        
        long e = System.currentTimeMillis();
        System.out.println(e - s);
    }

}

THREAD_SIZEの分だけスレッド起動して、全スレッドが終了するまでの時間を出力する。

結果と感想

計測方法は、それぞれのタイプ&同一スレッド数について、三回連続して実行&その平均値(小数点以下省略)を取る、というものです。というわけで各タイプの実行時間の一覧。

スレッド数(n) 1 3 5
採番テーブル 8406 21046 34573
MAX + 1 7609 22198 37906
シーケンス 7833 7917 8333

でまぁ、結果についてなんですが。やる前から分かってことではありますが、シーケンス圧勝なんですね。スレッド(=Oracleのセッション数とほぼ同義)10くらいまで増やさないと差は出ないかな〜とか思ってたけど、3の時点でかなり、5で歴然。というか、1トランザクションごとにかかる時間自体がほとんど増えないんですよね。

対して、採番・MAX+1ともにシーケンスと比べるとスレッド数が増えると幾何級数的に実行時間が増えていく。一つしかない資源の奪い合いなので、競争相手が増えれば増えるほど遅くなるのは当然なんですが。同時実行のパフォーマンスを上げたければ、こういう状況をなるべく回避しましょう、という教科書的知識が良くわかります。実際、シーケンスはロックが発生しないから高速なわけだし、同時実行数増やしても単位処理にかかる時間が変わらないのも同様な理由によるものだし。

あと、MAX+1のときに発生する一意制約違反を回避する方法はってんで、ぐぐってみると、FOR UPDATEするとかロックテーブル使うだとかが出てくる。どちらにせよロックがかかるので、結局は採番テーブルと変わらないくらいのパフォーマンスになるんじゃないのかなぁ……とか思う(根拠ゼロ) でも連番ごときにそんなややこしい仕組みを作りこみたくないというのが本音。

おわりに

そんなわけで、シーケンスが使えるところでは素直にシーケンス使っておきましょう、ということでした。

おまけ


SQL Developerの過去1時間の待機のレポート。レポート -> すべてのレポート -> データ・ディクショナリ・レポート -> データベース管理 -> 待機とイベント -> 過去1時間の待機、から見れる。検証は、シーケンス -> MAX+1 -> 採番テーブルにやりました。シーケンスの待機は大したことなく、MAX+1の待機は大半がコミット関連で、採番テーブルの待機はアプリケーション側の事情で発生してる、ことが読み取れます。