[Oracle Cloud] Oracle Database をパラレルクエリーで高速化

# はじめに

Oracle Database Enterprise Edition では、CPU を効率的に利用するためのパラレルクエリー機能があります。Standard Edition では、1個の SQL クエリーを 1個の CPU で処理をします。他の CPU は そのSQL クエリーに関わる仕事はしていません。他の SQL クエリーや、Oracle Database 以外の仕事をしています。1個の SQL クエリーを複数の CPU で処理するための機能が、パラレルクエリーです。
大量のデータ処理を行う、データウェアハウス用途で大きな効果を発揮します。逆に、オンライントランザクションのような、少数のデータを扱う用途では、パラレルクエリーはそれほど効果的ではない場合があります。複数の CPU を管理して動かすオーバーヘッドの方が時間が掛かってしまうことがあります。



パラレルクエリーの[概要図](https://www.oracle.com/technetwork/jp/content/parallel-execution-132539-ja.pdf)があったので、抜粋します。

![1597304615255.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/c5782321-fd3a-eb06-63aa-d162a6a836f9.png)



パラレルクエリーの実行方法を検証したため、備忘録として残しておきます。すべてを網羅的に書いているわけではないので、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 を参照しながらご確認ください。

並列処理の種類
https://docs.oracle.com/cd/F19136_01/vldbg/types-parallelism.html#GUID-FCE748F1-0CDB-4D95-9849-6A062E653945



# SELECT のパラレルクエリー

SELECT 句でパラレルクエリーをするときには、次の 4種類の方法があります。

- 自動設定 (parallel_degree_policy)
- セッションに対して設定する
- SQL クエリーにヒント句を埋め込む
- Table や Index に対して設定する

基本的には 1番目の自動設定が楽だと思います。SQL クエリーに合わせて、自動的に効率的なパラレルクエリーを実行してくれます。パラレルクエリーと通常実行を比較して、パラレルクエリーの方が速そうなら、自動的にパラレルクエリーをしてくれます。また、パラレルクエリーの並列度も合わせて自動的に行ってくれます。

参照元 : P.25
https://www.oracle.com/technetwork/jp/ondemand/database/db-technique/parallel-exe-evening-100714-251722-ja.pdf



それでは、実際に 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 の導入方法が気になる方は[こちら](https://qiita.com/sugimount/items/1ca6aedfaa08e43ef9b4)をどうぞ。

![1597373295129.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/69c8670d-cc65-935c-b96b-98833f317ef0.png)



## 自動設定 (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行目が変化しています。

![1597301793985.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/195fc33b-f2f5-b29d-7775-71cc25e913a4.png)



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;
```



実行計画です。自動設定したときと、同一の実行計画が選ばれています。

![1597369383570.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/4e51c115-2495-f9b9-b0cb-6d3008bcc5ac.png)



## SQL クエリーにヒント句を埋め込む

SQL 単体に対して、パラレルクエリーを指定できます。`/*+ PARALLEL(4) */` というヒント句を埋め込むことで、4多重でパラレルクエリーされます。

```
SELECT /*+ PARALLEL(4) */ COUNT(*) FROM EXAMPLES WHERE 10000 <= SCORE AND SCORE <= 20000 ORDER BY SCORE;
```



実行計画です。自動設定したときと、同一の実行計画が選ばれています。

![1597369685375.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/201f0668-f00e-4fdb-8ece-c2940a62297f.png)





# 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` となっています。パラレルクエリー無しの普通の実行計画です。

![1597372091446.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/a4f0cab7-a5d6-f9a7-1d63-b7bd071b36ad.png)



## 自動設定(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 そのものは、パラレル化となっていません。これは、自分の環境だけなのか、色々条件があるのかはわかりませんが、こうった結果となりました。

![1597372890176.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/c93a2a22-b817-0fe2-a99e-30a486d7a647.png)



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 文のパラレルクエリーを管理

![1597376503304.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/ae7225f3-7c71-b802-e689-0cc7283023c9.png)



## 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;
```



確認です。セッション単位に指定した時の同じ実行計画になっています。

![1597375561057.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/7657dbc2-d9d8-596d-dee7-ed0e9bcfb026.png)


# DMLを無効化(ヒント句)

DISABLE_PARALLEL_DML
https://docs.oracle.com/cd/E96517_01/sqlrf/Comments.html#GUID-D316D545-89E2-4D54-977F-FC97815CD62E


# 他の初期化パラメータ

パラレルクエリーに関する初期化パラメータは、様々なものがあります。その中で、一部紹介します。網羅的に調べたい方は次の Document を参照すると良いです。

https://docs.oracle.com/cd/F19136_01/vldbg/parameters-parallel-exec.html#GUID-7ED3242C-D2FF-4279-AF8A-C7CFB7ED5F2A



パラレル並列処理上限(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

https://logmi.jp/tech/articles/237514

https://www.oracle.com/technetwork/jp/ondemand/database/db-technique/parallel-exe-evening-100714-251722-ja.pdf

https://www.oracle.com/webfolder/technetwork/jp/ondemand/ddd2013/A-4.pdf

https://www.oracle.com/technetwork/jp/content/parallel-execution-132539-ja.pdf

https://oreno-it.info/archives/560

https://www.oracle.com/technetwork/jp/database/bi-datawarehousing/twp-parallel-execution-fundamentals-133639-ja.pdf

https://www.oracle.com/technetwork/jp/ondemand/database/db-technique/1221-1500-parallel-easy-for-dba-297517-ja.pdf

http://www.doppo1.net/oracle/tuning/explain-plan.html#3