kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

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で使用できるのがうれしい。