DBMS_PARALLEL_EXECUTE

Oracle（PL/SQL）のDBMS_PARALLEL_EXECUTEは、SQLやプロシージャーを並列実行するパッケージ。

概要

DBMS_PARALLEL_EXECUTEは、SQLやプロシージャーを並列実行するパッケージ。

「update hoge set h_value=h_value+1 where h_id between :start_id and :end_id」といったSQLを並列実行できる。
並列実行するそれぞれのデータの塊（分割されたデータ）をチャンクと呼ぶ。
「:start_id」「:end_id」はDBMS_PARALLEL_EXECUTEで決められている名前で、データをチャンクに分ける為の値であり、この値をDBMS_PARALLEL_EXECUTEが自動的に生成してくれる。

チャンクに分ける為のキー項目の指定方法には以下のようなものがある。

DBMS_PARALLEL_EXECUTEの使い方は以下の手順になる。

1. タスク名を決める。
   自分で付けてもいいし、GENERATE_TASK_NAMEで一意の名前を生成することも出来る。
   以降のDBMS_PARALLEL_EXECUTEの各処理の呼び出しでは、このタスク名を渡すことになる。
2. タスクを作成する。（CREATE_TASK）
3. チャンクを作成する。（CREATE_CHUNKS_BY_NUMBER_COL等）
4. タスクを並列実行する。（RUN_TASK）
5. エラーチェック（TASK_STATUS）およびリトライ（RESUME_TASK）
6. タスクを削除する。（DROP_TASK）

DBMS_PARALLEL_EXECUTEはDBMS_SCHEDULERパッケージを利用しているらしい。
したがって、CREATE JOBシステム権限が必要。

grant create job to ユーザー;

CREATE_CHUNKS_BY_NUMBER_COLの例

hoge_tableのIDを1つずつ並列処理する例。

declare
  task_name varchar2(100);
  sql_stmt  clob;
begin
  -- タスク名の生成
  task_name := dbms_parallel_execute.generate_task_name();

  -- タスクの作成
  dbms_parallel_execute.create_task(task_name);

  -- チャンクの作成
  -- スキーマ(ユーザー)名・テーブル名・カラム名を指定する。
  -- 末尾の引数は、チャンクの値の範囲
  dbms_parallel_execute.create_chunks_by_number_col(task_name, 'HISHIDAMA', 'HOGE_TABLE', 'HOGE_ID', 1);

  -- 並列実行する
  sql_stmt := 'begin
    dbms_output.put_line(:start_id || ''-'' || :end_id);
    end;';
  dbms_parallel_execute.run_task(task_name, sql_stmt, DBMS_SQL.NATIVE, parallel_level => 10);

  -- 終了ステータス確認（ドキュメントではFINISHEDは6となっているが、ドキュメントが間違っている可能性あり！）
  dbms_output.put_line('end ' || dbms_parallel_execute.task_status(task_name));

  -- タスクを削除する
  dbms_parallel_execute.drop_task(task_name);
end;
/

create_chunks_by_number_colは、テーブルのカラムを指定してチャンクの範囲を決める。
テーブル名と、そのテーブルのスキーマ名およびカラム名を渡す。
これらの名称は大文字にしないといけない。（通常は、Oracleは名称を大文字で保持している為。SQLで名称を小文字を指定した場合は大文字に変換されて実行されている）
第5引数（chunk_size）が、チャンク1個当たりのデータ件数。

run_taskで実行を開始する。
第2引数で並列実行するSQLやPL/SQLブロック（begin〜end）を指定する。
第3引数はOracleのバージョンを指定するものらしいが、NATIVEでいいだろう。
parallel_levelは同時実行数（並列数）。0を指定または省略すると直列実行になるらしい。NULLだと環境依存の並列数。
各チャンクの処理が終わるとチャンク毎にコミットされるらしい。
run_taskは全チャンクの処理が終わるまで戻ってこない。

実行するSQLの中には、必ず「:start_id」「:end_id」の両方が含まれていなければならない。
どちらかしか無い、あるいは両方無い場合はrun_taskがエラーステータス（FINISHED_WITH_ERROR等）で終了する。
（2つ以上書くのは問題ないようだ）
「:start_id」が数値型の場合、文字列型として扱いたくて「'':start_id''」のように書くと、値に置換されず「:start_id」が含まれていない扱いになるので注意。（文字列に変換したければ「to_char(:start_id)」とする）

なお、実行するPL/SQLの中からdbms_outputでログ出力している場合、2並列以上で実行されるとSQL*Plusのコンソールに表示されないようだ。
parallel_levelが0（直列実行）か1（並列数1）の場合はコンソールに出力される。

drop_taskでタスクを削除する。
タスクの情報はuser_parallel_execute_tasksビューで見られるが、drop_taskするとこのビューから表示されなくなるので、何か調査したい場合はdrop_taskを実行しないようにする。
逆に、drop_taskしないと（正常終了しようがエラー終了だろうが）いつまでも残り続ける。
→全タスクを削除する例

スキーマ名・テーブル名を小文字で指定すると、以下のようなエラーになる。

ORA-00942: 表またはビューが存在しません。
ORA-06512: "SYS.DBMS_PARALLEL_EXECUTE_INTERNAL", 行859
ORA-06512: "SYS.DBMS_SYS_SQL", 行1412
ORA-06512: "SYS.DBMS_PARALLEL_EXECUTE_INTERNAL", 行813
ORA-06512: "SYS.DBMS_PARALLEL_EXECUTE", 行147

カラム名を小文字で指定すると、以下のようなエラーになる。

ORA-00904: "hoge_id": 無効な識別子です。
ORA-06512: "SYS.DBMS_PARALLEL_EXECUTE_INTERNAL", 行859
ORA-06512: "SYS.DBMS_SYS_SQL", 行1412
ORA-06512: "SYS.DBMS_PARALLEL_EXECUTE_INTERNAL", 行813
ORA-06512: "SYS.DBMS_PARALLEL_EXECUTE", 行147

スケジューラーに対する権限が無いと、以下のようなエラーになる。

ORA-27486: 権限が不足しています
ORA-06512: "SYS.DBMS_ISCHED", 行175
ORA-06512: "SYS.DBMS_SCHEDULER", 行288
ORA-06512: "SYS.DBMS_PARALLEL_EXECUTE", 行357
ORA-06512: "SYS.DBMS_PARALLEL_EXECUTE", 行418

ジョブを作成する権限を付ける。

grant create job to ユーザー;

タスクの確認方法

タスクはuser_parallel_execute_tasks、チャンクはuser_parallel_execute_chunksで確認できる。

select task_name, sql_stmt, status from user_parallel_execute_tasks;

select dbms_parallel_execute.task_status('TASK$_1531') from dual;

task_statusでもステータスを確認できるが、数値なので分かりづらい。（しかもドキュメントに載っている値と違うようだ！）
PL/SQLで使うならdbms_parallel_execute.FINISHED等の定数と比較するようにすべき。

目で見るならuser_parallel_execute_tasksビューのstatusの方が文字列なので分かりやすい。
（drop_taskするとビューから消えてしまうので、確認できないが…）

全タスクを削除する例

drop_taskを実行しないと、user_parallel_execute_tasksビューにタスクが残り続ける。
（タスクを削除すると、user_parallel_execute_chunksビューからも関連するチャンクが消える）

begin
  for row in (select task_name from user_parallel_execute_tasks) loop
    dbms_parallel_execute.drop_task(row.task_name);
  end loop;
end;
/