1. はじめに
少し前置きが長いですが、ご容赦を。
10年以上前に開かれたOracle Database 10gのセミナーで、Oracleのパラレルクエリについて以下のような話を聞いた記憶があるのですが、この内容がずっと気になっていました。
- まず、処理対象データをDOPの数で分割してスレーブプロセスに割り当てる。（以下の図はDOP=2の場合）
- さらに、分割されたデータを、それぞれ1/2、1/3、1/6に分割する。
- それぞれのスレーブプロセスは1/2⇒1/3⇒1/6の順番で処理する。
- もし、あるスレーブプロセスが割り当てられた全データを処理完了した時点で、他のスレーブプロセスに未処理のデータが残っている場合、未処理のデータを処理が完了したスレーブプロセスに割り振り直す。この対応により、特定のスレーブプロセスの処理遅れを回避し、全体の処理完了が遅れることを軽減する。
並列処理についてあまり詳しくなかった当時は「Oracleすごいなぁ」という感想を持っていました。しかし、いろいろ経験を積んでから改めて考えると、効率の良い方法ではないことも分かり、本当にそういう風に動くのか疑問を持つようになりました。
また、この挙動について明示的に説明された資料を見つけることもできませんでした。以下の資料のp19にはそれらしい図はあるのですが。
そこで、今回はOracleパラレルクエリにおけるデータの分担の様子について観察してみた結果を、ブログに記録しておこうと思います。
ちなみに、サポート範囲外の機能の使い方をしているので、厳密に正しいかは保証できない点はご了承ください。
2. 環境準備
今回は以下の環境で動作を確認しています。仮想マシンには2CPU、2GBメモリを割り当てています。
- OS環境：Virtual Box 6.0.2上のCentOS 7.6.1810 (x86_64)
- DB：Oracle Database 18c 18.3.0.0 (Linux x86-64)
今回の作業を実施するDBユーザは以下のように作成しています。
CREATE USER test IDENTIFIED BY ****** QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO test;
このDBユーザに以下のようなテーブル、データを作成します。
-- 12個のパーティションを持つテーブル「ptab」を作成
CREATE TABLE ptab (
part_id NUMBER(2)
)
PARTITION BY RANGE (part_id) (
PARTITION p01 VALUES LESS THAN (2),
PARTITION p02 VALUES LESS THAN (3),
PARTITION p03 VALUES LESS THAN (4),
PARTITION p04 VALUES LESS THAN (5),
PARTITION p05 VALUES LESS THAN (6),
PARTITION p06 VALUES LESS THAN (7),
PARTITION p07 VALUES LESS THAN (8),
PARTITION p08 VALUES LESS THAN (9),
PARTITION p09 VALUES LESS THAN (10),
PARTITION p10 VALUES LESS THAN (11),
PARTITION p11 VALUES LESS THAN (12),
PARTITION p12 VALUES LESS THAN (13)
);
-- 1パーティション毎に100件、全体で1,200件のデータを挿入
BEGIN
FOR v_part_id IN 1..12
LOOP
FOR v_i IN 1..100
LOOP
INSERT INTO ptab VALUES (v_part_id);
END LOOP;
END LOOP;
COMMIT;
END;
/
-- 統計情報収集
exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'PTAB');
-- 並列度を2に設定
ALTER TABLE ptab PARALLEL 2;
また、どのデータいつ、どのスレーブプロセスに処理されたかを観測するために、以下のような関数も準備します。
-- 時刻を取得する関数
CREATE OR REPLACE FUNCTION get_time
RETURN VARCHAR2
IS
BEGIN
RETURN to_char(current_timestamp, 'HH24:MI:SS.FF3');
END;
/
-- セッションIDを取得する関数
-- ただし、p_part_id = waited_idの場合は約10倍時間が掛かるようにする
CREATE OR REPLACE FUNCTION get_sid(
p_part_id NUMBER,
waited_id NUMBER
)
RETURN NUMBER
IS
v_dummy NUMBER := 0;
v_loop_cnt NUMBER := 0;
BEGIN
IF p_part_id = waited_id THEN
v_loop_cnt := power(10, 6);
ELSE
v_loop_cnt := power(10, 5);
END IF;
FOR v_i IN 1..v_loop_cnt
LOOP
v_dummy := mod(v_dummy + 1, 100);
END LOOP;
RETURN sys_context('USERENV', 'SID');
END;
/
3. 動作確認
Oracleパラレルクエリにおけるパーティション分割はブロックベースの場合とパーティションベースの場合があります。今回は前者1パターン、後者2パターンの合計3ケースを見てみたいと思います。
3.1. ブロックベースの場合
ブロックベースのデータ分割を行う1例として次のようなSQL文を実行してみます。
SQL> set linesize 100
SQL> set pagesize 100
SQL> column start_time format a15
SQL> column end_time format a15
SQL> set time on
20:58:06 SQL> set timing on
20:58:08 SQL> SELECT
20:58:35 2 part_id,
20:58:35 3 sid,
20:58:35 4 min(read_time) start_time,
20:58:35 5 max(read_time) end_time
20:58:35 6 FROM
20:58:35 7 (
20:58:35 8 SELECT /*+ NO_MERGE */
20:58:35 9 part_id,
20:58:35 10 get_time read_time,
20:58:35 11 get_sid(part_id, 0) sid
20:58:35 12 FROM
20:58:36 13 ptab
20:58:36 14 )
20:58:36 15 GROUP BY
20:58:36 16 part_id,
20:58:36 17 sid
20:58:36 18 ORDER BY 3;
PART_ID SID START_TIME END_TIME
---------- ---------- --------------- ---------------
3 278 20:58:37.109 20:58:38.429
5 40 20:58:37.110 20:58:38.428
11 40 20:58:38.441 20:58:39.728
2 278 20:58:38.443 20:58:39.694
9 278 20:58:39.707 20:58:40.965
7 40 20:58:39.742 20:58:41.020
10 278 20:58:40.978 20:58:42.232
8 40 20:58:41.033 20:58:42.307
1 278 20:58:42.246 20:58:43.504
12 40 20:58:42.320 20:58:43.606
6 278 20:58:43.517 20:58:44.759
4 40 20:58:43.620 20:58:44.900
12行が選択されました。
経過: 00:00:07.83
20:58:44 SQL>
何をしているかというと、各行ごとにget_sid関数とget_time関数を呼び出し、それがどのスレーブプロセス（セッションID）でいつ読み込まれたかを確認しています。最後に集計とソートをして見やすくしています。
NO_MERGEヒントはブロックベースのデータ分割にするためにつけています。
また、今回はget_sid関数の第2引数が「0」のため、基本どのデータも同じだけ処理時間が掛かるはずです。
これがブロックベースのデータ分割されていることは、実行計画を確認すれば分かります。（PX BLOCK ITERATORという操作が出てきます。今回は省略します。）
SQL文の結果を見ると、12個のパーティションがきれいに2つのスレーブプロセスに分割されて実行されていることが分かります。図示すると以下のような感じです。処理されているパーティションの順番はバラバラですが、ブロックベースのデータ分割がされているので仕方ないですね。
次に、パーティション5のデータの処理が遅いケースを試してみます。
20:58:44 SQL> SELECT
21:14:57 2 part_id,
21:14:57 3 sid,
21:14:57 4 min(read_time) start_time,
21:14:57 5 max(read_time) end_time
21:14:57 6 FROM
21:14:57 7 (
21:14:57 8 SELECT /*+ NO_MERGE */
21:14:57 9 part_id,
21:14:57 10 get_time read_time,
21:14:57 11 get_sid(part_id, 5) sid
21:14:57 12 FROM
21:14:57 13 ptab
21:14:57 14 )
21:14:57 15 GROUP BY
21:14:57 16 part_id,
21:14:57 17 sid
21:14:57 18 ORDER BY 3;
PART_ID SID START_TIME END_TIME
---------- ---------- --------------- ---------------
5 17 21:14:58.773 21:15:11.527
3 278 21:14:58.774 21:15:00.075
11 278 21:15:00.089 21:15:01.351
2 278 21:15:01.363 21:15:02.614
9 278 21:15:02.627 21:15:03.899
7 278 21:15:03.912 21:15:05.166
10 278 21:15:05.180 21:15:06.428
8 278 21:15:06.441 21:15:07.700
1 278 21:15:07.713 21:15:08.968
12 278 21:15:08.981 21:15:10.233
6 278 21:15:10.246 21:15:11.498
4 278 21:15:11.511 21:15:12.759
12行が選択されました。
経過: 00:00:14.02
21:15:12 SQL>
想定通り、パーティション5だけ処理に13秒程度掛かっています。セッションID=17のスレーブプロセスがパーティション5を処理している間、セッションID=278のスレーブプロセスは残りのパーティションをすべて処理しています。図示すると以下の通りです。
これを見る限り、1/2、1/3、1/6にデータを分割するというのは正しくなく、もっと細かい単位に分割されているように見えますね。
3.2. パーティションベースの場合
次に、データ分割がパーティション単位で行われた場合を見てみます。まずは、各パーティションの処理に掛かる時間が均等な場合です。
21:15:12 SQL> SELECT
21:30:50 2 part_id,
21:30:50 3 get_sid(part_id, 0) sid,
21:30:50 4 min(get_time()) start_time,
21:30:50 5 max(get_time()) end_time
21:30:50 6 FROM
21:30:50 7 ptab
21:30:50 8 GROUP BY
21:30:50 9 part_id,
21:30:50 10 get_sid(part_id, 0);
PART_ID SID START_TIME END_TIME
---------- ---------- --------------- ---------------
11 291 21:30:52.656 21:30:53.909
10 291 21:30:53.935 21:30:55.189
8 291 21:30:55.208 21:30:56.475
6 291 21:30:56.495 21:30:57.768
4 291 21:30:57.802 21:30:59.097
2 291 21:30:59.115 21:31:00.395
12 40 21:30:52.656 21:30:53.967
9 40 21:30:53.988 21:30:55.258
7 40 21:30:55.279 21:30:56.555
5 40 21:30:56.579 21:30:57.871
3 40 21:30:57.894 21:30:59.197
1 40 21:30:59.217 21:31:00.508
12行が選択されました。
経過: 00:00:08.01
今回、都合があって結果をソートしていないので分かり辛いですが（ソートを入れた場合、挙動が異なる。3.3.で後述。）、図示すると以下の通りになります。概ね想定通りですね。今回は3.1.であったブロックベースの場合とは異なり、処理順番も分かり易いです。
次に、3.1.と同様、最初に処理されるパーティション12が他のパーティションより時間が掛かるようにしてみましょう。
21:38:44 SQL> SELECT
21:38:59 2 part_id,
21:38:59 3 get_sid(part_id, 12) sid,
21:38:59 4 min(get_time()) start_time,
21:38:59 5 max(get_time()) end_time
21:38:59 6 FROM
21:38:59 7 ptab
21:38:59 8 GROUP BY
21:38:59 9 part_id,
21:38:59 10 get_sid(part_id, 12);
PART_ID SID START_TIME END_TIME
---------- ---------- --------------- ---------------
12 40 21:39:01.332 21:39:14.026
11 278 21:39:01.219 21:39:02.465
10 278 21:39:02.478 21:39:03.726
9 278 21:39:03.739 21:39:04.999
8 278 21:39:05.013 21:39:06.271
7 278 21:39:06.284 21:39:07.541
6 278 21:39:07.554 21:39:08.807
5 278 21:39:08.820 21:39:10.079
4 278 21:39:10.091 21:39:11.350
3 278 21:39:11.363 21:39:12.622
2 278 21:39:12.635 21:39:13.888
1 278 21:39:13.901 21:39:15.146
12行が選択されました。
経過: 00:00:13.96
21:39:15 SQL>
今回も図示すると以下のようになります。こちらも想定通りですね。
3.3. ケース3.2.にソートを加えた場合
最後に、ケース3.2.のパーティション分割の場合にソートを加えた場合を見てみます。各パーティションに均等に時間が掛かる場合はスキップして（想定通りの動きのため）、パーティション12にだけ時間が掛かる場合をまずみてみます。
21:39:15 SQL> SELECT
21:48:38 2 part_id,
21:48:38 3 get_sid(part_id, 12) sid,
21:48:38 4 min(get_time()) start_time,
21:48:38 5 max(get_time()) end_time
21:48:38 6 FROM
21:48:38 7 ptab
21:48:38 8 GROUP BY
21:48:38 9 part_id,
21:48:38 10 get_sid(part_id, 12)
21:48:38 11 ORDER BY 3;
PART_ID SID START_TIME END_TIME
---------- ---------- --------------- ---------------
11 304 21:48:40.182 21:48:41.415
12 51 21:48:40.296 21:48:52.693
10 51 21:48:52.707 21:48:53.965
9 304 21:48:52.707 21:48:53.972
8 51 21:48:53.978 21:48:55.863
7 304 21:48:54.024 21:48:56.156
6 51 21:48:55.896 21:48:58.342
5 304 21:48:56.190 21:48:58.769
4 51 21:48:58.382 21:49:00.926
3 304 21:48:58.798 21:49:01.327
2 51 21:49:00.951 21:49:03.485
1 304 21:49:01.346 21:49:03.677
12行が選択されました。
経過: 00:00:23.56
21:49:03 SQL>
注目すべきなのは、セッションID=51がパーティション12を処理し終わるまで、セッションID=304は先に進んでいません。結果として、全体としての処理時間も3.1.と3.2.のケースにおける14秒程度から23秒程度まで伸びてしまっています。図示すると通りです。
これは、想像なのですが、ソートをパラレル処理する場合、ソート前のデータをサンプリングしてデータをどのように再分割するか決めることが原因なのではないかと思います。ソート前のサンプリングするために、最初の2パーティションの結果を待って処理を先に進めるのではないでしょうか。
ちなみに、スレーブプロセスに最初に処理されるパーティション12ではなく、2番目に処理されるパーティション10に時間が掛かる場合は、以下の通りです。
21:49:03 SQL> SELECT
23:35:13 2 part_id,
23:35:13 3 get_sid(part_id, 12) sid,
23:35:13 4 min(get_time()) start_time,
23:35:13 5 max(get_time()) end_time
23:35:13 6 FROM
23:35:13 7 ptab
23:35:13 8 GROUP BY
23:35:13 9 part_id,
23:35:13 10 get_sid(part_id, 10)
23:35:13 11 ORDER BY 3;
PART_ID SID START_TIME END_TIME
---------- ---------- --------------- ---------------
11 279 23:35:13.753 23:35:15.041
12 45 23:35:13.753 23:35:15.032
10 279 23:35:15.331 23:35:38.658
9 45 23:35:15.493 23:35:18.270
8 45 23:35:18.321 23:35:20.816
7 45 23:35:20.868 23:35:23.363
6 45 23:35:23.414 23:35:25.917
5 45 23:35:25.969 23:35:28.479
4 45 23:35:28.531 23:35:31.029
3 45 23:35:31.079 23:35:33.581
2 45 23:35:33.631 23:35:36.136
1 45 23:35:36.187 23:35:38.694
12行が選択されました。
経過: 00:00:25.10
23:35:38 SQL>
こちらは、以下の図の通りセッションID=279がパーティション10を処理している間、セッションID=45は処理を継続するという理想的な動作になっています。
4. まとめ
今回、パラレル処理がデータをどう分担するのかの様子を見てみました。
以前に聞いた、1/2、1/3、1/6に分割するという動作は確認できませんでした。ただ、今回確認できた動作の方が効率が良いので、特に問題ではありませんが。
また、3.3.の1番目のケースはスレーブプロセスがまだ処理データが残っているのに処理待ちになってしまうという点では、非常に興味深いものがあります。Oracleのパラレルクエリは奥が深いですね。
0 件のコメント:
コメントを投稿