Oracle SEでできるパラレルupdate処理
ご存知パラレルSQLはEEしか使用できないが、SEでもPL/SQLを使用すれば簡単にパラレルで処理することができる。
それがDBMS_PARALLEL_EXECUTE
11gR2以降から使用できるみたい。
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス
このパッケージを使用すると、ユーザーは高レベルな2つの手順を実行して、表データをパラレルで増分更新できるようになります。 表内の行のセットをさらに小さいサイズのチャンクにグループ分けします。 ユーザーが指定した文をこれらのチャンクに対してパラレルで実行し、各チャンクの処理の終了時にコミットします DBMS_PARALLEL_EXECUTE
とあるようにupdateだけパラレル処理できるみたいだ。
あとはROWIDや値でチャンクに分けて、それごとに並列で処理するみたい。
というわけでマニュアルを元に試してみた。
権限を付与。
grant CREATE JOB to TEST;
60000件のテーブルをROWIDごとに10000件づつチャンク分けして3パラレルでupdateする。
SQL>DECLARE sql_stmt VARCHAR2(100); BEGIN DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask'); --タスク作成 DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'TEST', 'TEST_TABLE', true, 10000); --ユーザ名(TEST)、テーブル(TEST_TABLE)とチャンク単位(10000件)を指定 sql_stmt := 'update TEST_TABLE set A1 = 111 WHERE rowid BETWEEN :start_id AND :end_id'; --実行するUPDATE文を記述 DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', sql_stmt, DBMS_SQL.NATIVE,parallel_level => 3); --パラレル度指定(parallel_level:0はシリアル実行) DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask'); --タスク削除 END; / PL/SQLプロシージャが正常に完了しました。
どういう動作になってるか確かめたところ
最初にチャンクサイズの指定した行数分の処理をパラレルで指定した分に分けて実行され、各チャンクの処理が終了後コミットされる。
パラレル1:update TEST_TABLE set A1 = 111 WHERE rowid BETWEEN 1 AND 10000; commit; パラレル2:update TEST_TABLE set A1 = 111 WHERE rowid BETWEEN 10001 AND 20000; commit; パラレル3:update TEST_TABLE set A1 = 111 WHERE rowid BETWEEN 20001 AND 30000; commit; パラレル1:update TEST_TABLE set A1 = 111 WHERE rowid BETWEEN 30001 AND 40000; commit; パラレル2:update TEST_TABLE set A1 = 111 WHERE rowid BETWEEN 40001 AND 50000; commit; パラレル3:update TEST_TABLE set A1 = 111 WHERE rowid BETWEEN 50001 AND 60000; commit;
次に値単位でのチャンク分けで実行する。
以下の値を使用する
SQL> SELECT distinct ID, ID FROM TEST_TABLE; ID ID ----------- ----------- 6 6 12 12 13 13 5 5 9 9 10 10 11 11 4 4 16 16 18 18 14 14 8 8 0 0 1 1 2 2 3 3 15 15 17 17 19 19 999 999
実行
SQL>DECLARE sql_stmt VARCHAR2(100); l_chunk_sql VARCHAR2(1000); BEGIN DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask'); --タスク作成 l_chunk_sql := 'SELECT distinct ID, ID FROM TEST_TABLE'; DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL('mytask', l_chunk_sql, false); --チャンク単位を指定するカラムを取得 sql_stmt := 'update TEST_TABLE set A1 = 9999 WHERE ID BETWEEN :start_id AND :end_id'; --実行するクエリを記述 DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', sql_stmt, DBMS_SQL.NATIVE,parallel_level => 10); --パラレル度指定(parallel_level:0はシリアル実行) DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask'); --タスク削除 END; / PL/SQLプロシージャが正常に完了しました。
実行途中の状態を見てみる
USER_PARALLEL_EXECUTE_CHUNKSテーブルは作成したタスクの状態を確認できる。
STATUSの項目は
1. PROCESSED・・処理完了
2. ASSIGNED・・実行中
3. UNASSIGNED・・実行待ち状態
START_IDとEND_IDはチャック分けを行った値を表示。
ROWID単位のチャック分けを行った場合はSTART_ROWIDとEND_ROWIDに表示される。
JOB_NAMEは処理しているジョブ名でチャンクのジョブ終了後にまだ処理が残っていたら再利用されているのが作業終了後の結果からわかる。
SQL> select * from USER_PARALLEL_EXECUTE_CHUNKS; CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID START_ID END_ID JOB_NAME START_TS END_TS ---------- ---------- ---------- ------------- ----------- ---------- ---------- ------------- ------------------------- --------------------------- 13321 mytask UNASSIGNED 8 8 13322 mytask UNASSIGNED 0 0 13323 mytask UNASSIGNED 1 1 13324 mytask UNASSIGNED 2 2 13325 mytask UNASSIGNED 3 3 13326 mytask UNASSIGNED 15 15 13327 mytask UNASSIGNED 17 17 13328 mytask UNASSIGNED 19 19 13329 mytask UNASSIGNED 999 999 13330 mytask UNASSIGNED 7 7 13309 mytask ASSIGNED 6 6 TASK$_332_1 15-04-08 15:34:25.445502 13310 mytask ASSIGNED 12 12 TASK$_332_2 15-04-08 15:34:25.550813 13311 mytask ASSIGNED 13 13 TASK$_332_10 15-04-08 15:34:25.569973 13313 mytask ASSIGNED 5 5 TASK$_332_3 15-04-08 15:34:25.587212 13314 mytask ASSIGNED 9 9 TASK$_332_6 15-04-08 15:34:25.603652 13315 mytask ASSIGNED 10 10 TASK$_332_4 15-04-08 15:34:25.624593 13316 mytask ASSIGNED 11 11 TASK$_332_5 15-04-08 15:34:25.642230 13317 mytask ASSIGNED 4 4 TASK$_332_7 15-04-08 15:34:25.660648 13318 mytask ASSIGNED 16 16 TASK$_332_8 15-04-08 15:34:25.680740 13319 mytask ASSIGNED 18 18 TASK$_332_9 15-04-08 15:34:25.701271 13312 mytask PROCESSED 14 14 TASK$_332_3 15-04-08 15:34:25.584706 15-04-08 15:34:25.586214
作業が終了するとこうなる
SQL> select * from USER_PARALLEL_EXECUTE_CHUNKS; CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID START_ID END_ID JOB_NAME START_TS END_TS ---------- ---------- ---------- ------------ ----------- ---------- ---------- ------------- -------------------------- --------------------------- 13309 mytask PROCESSED 6 6 TASK$_332_1 15-04-08 15:34:25.445502 15-04-08 15:34:29.957046 13310 mytask PROCESSED 12 12 TASK$_332_2 15-04-08 15:34:25.550813 15-04-08 15:34:30.396650 13311 mytask PROCESSED 13 13 TASK$_332_10 15-04-08 15:34:25.569973 15-04-08 15:34:28.728823 13312 mytask PROCESSED 14 14 TASK$_332_3 15-04-08 15:34:25.584706 15-04-08 15:34:25.586214 13313 mytask PROCESSED 5 5 TASK$_332_3 15-04-08 15:34:25.587212 15-04-08 15:34:29.732339 13314 mytask PROCESSED 9 9 TASK$_332_6 15-04-08 15:34:25.603652 15-04-08 15:34:29.784597 13315 mytask PROCESSED 10 10 TASK$_332_4 15-04-08 15:34:25.624593 15-04-08 15:34:28.998609 13316 mytask PROCESSED 11 11 TASK$_332_5 15-04-08 15:34:25.642230 15-04-08 15:34:29.333351 13317 mytask PROCESSED 4 4 TASK$_332_7 15-04-08 15:34:25.660648 15-04-08 15:34:28.848737 13318 mytask PROCESSED 16 16 TASK$_332_8 15-04-08 15:34:25.680740 15-04-08 15:34:35.733688 13319 mytask PROCESSED 18 18 TASK$_332_9 15-04-08 15:34:25.701271 15-04-08 15:34:29.970953 13321 mytask PROCESSED 8 8 TASK$_332_7 15-04-08 15:34:28.873859 15-04-08 15:34:36.061433 13322 mytask PROCESSED 0 0 TASK$_332_4 15-04-08 15:34:29.033623 15-04-08 15:34:51.887963 13323 mytask PROCESSED 1 1 TASK$_332_5 15-04-08 15:34:29.387983 15-04-08 15:34:36.872956 13324 mytask PROCESSED 2 2 TASK$_332_3 15-04-08 15:34:29.813280 15-04-08 15:34:36.564196 13325 mytask PROCESSED 3 3 TASK$_332_6 15-04-08 15:34:29.862198 15-04-08 15:34:37.100879 13326 mytask PROCESSED 15 15 TASK$_332_9 15-04-08 15:34:30.051288 15-04-08 15:34:37.851584 13327 mytask PROCESSED 17 17 TASK$_332_1 15-04-08 15:34:30.053431 15-04-08 15:34:37.933220 13328 mytask PROCESSED 19 19 TASK$_332_2 15-04-08 15:34:30.507248 15-04-08 15:34:37.851578 13329 mytask PROCESSED 999 999 TASK$_332_10 15-04-08 15:34:35.011270 15-04-08 15:34:56.079726 13330 mytask PROCESSED 7 7 TASK$_332_8 15-04-08 15:34:36.045540 15-04-08 15:34:44.847642
使用機会は限定されてしますかもしれないが、割と簡単にパラレル処理がstandard editionで使用できるのがうれしい。