はじめに
Oracle Database Enterprise Edition では、CPU を効率的に利用するためのパラレルクエリー機能があります。Standard Edition では、1個の SQL クエリーを 1個の CPU で処理をします。他の CPU は そのSQL クエリーに関わる仕事はしていません。他の SQL クエリーや、Oracle Database 以外の仕事をしています。1個の SQL クエリーを複数の CPU で処理するための機能が、パラレルクエリーです。
大量のデータ処理を行う、データウェアハウス用途で大きな効果を発揮します。逆に、オンライントランザクションのような、少数のデータを扱う用途では、パラレルクエリーはそれほど効果的ではない場合があります。複数の CPU を管理して動かすオーバーヘッドの方が時間が掛かってしまうことがあります。
パラレルクエリーの概要図があったので、抜粋します。
パラレルクエリーの実行方法を検証したため、備忘録として残しておきます。すべてを網羅的に書いているわけではないので、Document も合わせて確認するのがおすすめです。
https://docs.oracle.com/cd/F19136_01/vldbg/using-parallel.html#GUID-3E2AE088-2505-465E-A8B2-AC38813EA355
検証で使った環境
- OCI DBCS Enterprise Edition 19c 以上
Oracle Database で出来るパラレルクエリー
Oracle Database では、様々な処理でパラレルクエリーが利用できます。
- SELECT のパラレルクエリー
- DML(INSERT, UPDATE, DELETE) のパラレルクエリー
- DDL(Index作成など) のパラレルクエリー
- パックアップ(Datapump) のパラレルクエリー
- 統計情報取得のパラレルクエリー
- など
今回の Qiita 記事では、SELECT と DML(UPDATE) のパラレルクエリー手順を検証していきます。他の内容については、適宜 Document を参照しながらご確認ください。
SELECT のパラレルクエリー
SELECT 句でパラレルクエリーをするときには、次の 4種類の方法があります。
- 自動設定 (parallel_degree_policy)
- セッションに対して設定する
- SQL クエリーにヒント句を埋め込む
- Table や Index に対して設定する
基本的には 1番目の自動設定が楽だと思います。SQL クエリーに合わせて、自動的に効率的なパラレルクエリーを実行してくれます。パラレルクエリーと通常実行を比較して、パラレルクエリーの方が速そうなら、自動的にパラレルクエリーをしてくれます。また、パラレルクエリーの並列度も合わせて自動的に行ってくれます。
それでは、実際に SELECT でパラレルクエリーをしてみましょう。
パラレルクエリーなし
まずは、パラレルクエリーを使わない SQL で実行計画を見てみましょう。パラレルクエリーの有無で、どのように実行計画が変化するのかを確認していきます。
SELECT COUNT(*) FROM EXAMPLES WHERE 10000 <= SCORE AND SCORE <= 20000 ORDER BY SCORE;
実行計画
WHERE で指定している SCORE 列は Index を設定していないシンプルな Table なので、実行計画の一番下に TABLE ACCESS FULL と表示されています。Table 内の Row を総なめする実行計画ですね。
ちなみに、実行計画は、SQL Monitoring 機能を使ってリアルタイムに確認しています。Enterprise Manager を使って見ています。リアルタイムに SQL クエリーの詳細を確認できるのでオススメです。
Enterprise Manager の導入方法が気になる方はこちらをどうぞ。
自動設定 (parallel_degree_policy)
自動設定でパラレルクエリーをしていきましょう。初期化パラメータの parallel_degree_policy がその設定です。parallel_degree_policy の詳細が気になる場合は、次の Document を参照してください。
https://docs.oracle.com/cd/F19136_01/refrn/PARALLEL_DEGREE_POLICY.html#GUID-BF09265F-8545-40D4-BD29-E58D5F02B0E5
PDB に sysdba で接続して、Default の設定値を確認します。MANUAL となっているので、自動設定にはなっていません。
SQL> SELECT name, value FROM v$parameter WHERE name LIKE '%parallel_degree_policy%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
parallel_degree_policy
MANUAL
SQL>
AUTO に変更します。現在起動中のインスタンスも、サーバーパラメータファイル(spfile) も両方とも変更します。
alter system set parallel_degree_policy=AUTO scope=both;
変更されたことを確認です。
SQL> SELECT name, value FROM v$parameter WHERE name LIKE '%parallel_degree_policy%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
parallel_degree_policy
AUTO
SQL>
それでは、パラレルクエリーが自動的に実行されるか確認します。5000万行ある Table に対して、SELECT Query を投げます
SELECT COUNT(*) FROM EXAMPLES WHERE 10000 <= SCORE AND SCORE <= 20000 ORDER BY SCORE;
Enterprise Manager で実行計画を見てみましょう。2行目~6行目が変化しています。
2行目~6行目を抜粋します。
-
PX COORDINATOR: パラレルクエリーをうまく制御するための、QC(Query Coordinator) が動いていることがわかります。 -
PX SEND QC (RANDOM): PX(Parallel Execution Servers) がパラレルで取得したデータを、QC に送付。PX 側は送付順番は気にせずに、自分の担当分の処理が終わったタイミングで送信 -
SORT AGGREGATE: ORDER BY での集計処理を、各 PX で実施 -
PX BLOCK ITERATOR: 対象の TABLE を分割して、各 PX に割り当てられる -
TABLE ACCESS FULL: 各 PX は、自分の担当分をフルスキャンでアクセス
実行計画の読取り方法が、Document に書いています。よろしければこちらもどうぞ。
https://docs.oracle.com/cd/E57425_01/121/TGSQL/tgsql_interp.htm
EXPLAN コマンドで実行計画を取得する方法も紹介します。細かなポイントですが、EXPLAIN でのみ表示される項目もあります。
EXPLAIN PLAN FOR SELECT COUNT(*) FROM EXAMPLES WHERE 10000 <= SCORE AND SCORE <= 20000 ORDER BY SCORE;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
実行結果
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 77069 (1)| 00:00:04 | | | |
| 1 | SORT AGGREGATE | | 1 | 5 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 5 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 5 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 5099K| 24M| 77069 (1)| 00:00:04 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| EXAMPLES | 5099K| 24M| 77069 (1)| 00:00:04 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
TQ や IN-OUT, PQ Distrib の実行計画の詳細な見方は、こちらに書かれているのでどうぞ
https://www.oracle.com/webfolder/technetwork/jp/ondemand/ddd2013/A-4.pdf
memo : MANUAL への戻し方
alter system set parallel_degree_policy=MANUAL scope=both;
セッションに対して設定
現在接続しているセッションに対して、パラレルクエリーの設定です。セッションを切断すると、この指定は無効になります。
alter session force parallel query parallel 4;
実行
SELECT COUNT(*) FROM EXAMPLES WHERE 10000 <= SCORE AND SCORE <= 20000 ORDER BY SCORE;
実行計画です。自動設定したときと、同一の実行計画が選ばれています。
SQL クエリーにヒント句を埋め込む
SQL 単体に対して、パラレルクエリーを指定できます。/*+ PARALLEL(4) */ というヒント句を埋め込むことで、4多重でパラレルクエリーされます。
SELECT /*+ PARALLEL(4) */ COUNT(*) FROM EXAMPLES WHERE 10000 <= SCORE AND SCORE <= 20000 ORDER BY SCORE;
実行計画です。自動設定したときと、同一の実行計画が選ばれています。
DML(Insert, Update, Delete, Merge)
DML パラレルクエリーを確認していきます。今回の検証では、Update 文で確認していきます。次の3種類の方法でパラレルクエリーを指定可能です。
- セッション単位
- ヒント句
- 表の定義
Document : パラレルDML操作について
https://docs.oracle.com/cd/F19136_01/vldbg/types-parallelism.html#GUID-53E3F377-C8E5-434C-9DAB-E19D125F3DE3
パラレルクエリーなし
まず、パラレルクエリー無しで、普通に実行します。
UPDATE EXAMPLES SET LABEL='TEST' WHERe 10000 <= SCORE AND SCORE <= 20000;
実行計画です。UPDATE と TABLE ACCESS FULL となっています。パラレルクエリー無しの普通の実行計画です。
自動設定(parallel_degree_policy)
ちなみに、自動設定(parallel_degree_policy)が AUTO のときは、UPDATE 文はどんな動きになるのでしょうか、見てみましょう。AUTO に設定されている状況です。
SQL> SELECT name, value FROM v$parameter WHERE name LIKE '%parallel_degree_policy%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
parallel_degree_policy
AUTO
SQL>
実行
UPDATE EXAMPLES SET LABEL='TEST' WHERe 10000 <= SCORE AND SCORE <= 20000;
実行計画
UPDATE 文で対象となる行については、パラレルクエリーで4多重で集計される実行計画になっています。が、UPDATE そのものは、パラレル化となっていません。これは、自分の環境だけなのか、色々条件があるのかはわかりませんが、こうった結果となりました。
EXPLAIN でも見てみましょう。
EXPLAIN PLAN FOR UPDATE EXAMPLES SET LABEL='TEST' WHERe 10000 <= SCORE AND SCORE <= 20000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
同様の結果ですね。
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5099K| 515M| 77069 (1)| 00:00:04 | | | |
| 1 | UPDATE | EXAMPLES | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 5099K| 515M| 77069 (1)| 00:00:04 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 5099K| 515M| 77069 (1)| 00:00:04 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| EXAMPLES | 5099K| 515M| 77069 (1)| 00:00:04 | Q1,00 | PCWP | |
セッション単位
現在接続しているセッションに対して、パラレルクエリーの設定です。セッションを切断すると、この指定は無効になります。
alter session force parallel DML parallel 4;
実行
UPDATE EXAMPLES SET LABEL='TEST' WHERe 10000 <= SCORE AND SCORE <= 20000;
実行計画です。自分の理解した内容を記載します。実際の UPDATE も並列処理されているようですね。
6~8 行目 : 4つの PX(Parallel Execution Servers) が並列でアクセスして、実際に UPDATE 文を発行
4-5 行目 : 4つの PX が、また新たな 4 つの PX に対し、UPDATE 後のデータを送付
2-3 行目 : Index をメンテナンスした後に、QC(Query Coodinator) へデータ送付
0-1 行目 : QC が Update 文のパラレルクエリーを管理
SQL クエリーにヒント句を埋め込む
念のためセッションの指定を無効化します。
alter session disable parallel dml;
UPDATE 文に、/*+ ENABLE_PARALLEL_DML PARALLEL(4) */ というヒント句を入れています。DMLのパラレルクエリーとして、4多重実行するヒント句です。
UPDATE /*+ ENABLE_PARALLEL_DML PARALLEL(4) */ EXAMPLES SET LABEL='TEST' WHERe 10000 <= SCORE AND SCORE <= 20000;
確認です。セッション単位に指定した時の同じ実行計画になっています。
DMLを無効化(ヒント句)
DISABLE_PARALLEL_DML
https://docs.oracle.com/cd/E96517_01/sqlrf/Comments.html#GUID-D316D545-89E2-4D54-977F-FC97815CD62E
他の初期化パラメータ
パラレルクエリーに関する初期化パラメータは、様々なものがあります。その中で、一部紹介します。網羅的に調べたい方は次の Document を参照すると良いです。
パラレル並列処理上限(LEL_MAX_SERVERS)の設定があります。PQプロセスの最大起動数を指定できます。
SELECT name, value FROM v$parameter WHERE name LIKE '%parallel_max_servers%';
自分の環境では、160 が Default となっています。インスタンス全体で、160多重実行が出来る設定となっています。
SQL> SELECT name, value FROM v$parameter WHERE name LIKE '%parallel_max_servers%';
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
parallel_max_servers
160
SQL>
参考URL
パラレルクエリーに関する Document
https://docs.oracle.com/cd/F19136_01/vldbg/degree-parallel.html#GUID-68ED8F5E-DD97-4762-985C-4C3AF85F9629
実行計画の読み取り方法に関する Document
https://docs.oracle.com/cd/E96517_01/tgsql/reading-execution-plans.html#GUID-1FA56307-047B-42BB-AF17-3B05F1814559