実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 16 / No.51 / Concurrent Execution of Union All and Union Tweet
Previously on Mac De Oracle... Day 15は, 分散, リモートクエリーにフォーカスをあてました. 好き嫌い多いネタだったかもしれませんねぇw
では, Day 16のお題, Concurrent Execution of Union All and Union
これは何かというと, 12c R1以降だったか？（定かでないw）に, UNION/UNION ALLの各分岐がパラレルで実行されるという機能です. それまではシリアルに処理されていたので, 分岐が多いほど処理時間も増加していた訳ですが, 分岐がそれぞれパラレルで実行される分処理時間は短くなるというやつですね.
Concurrent Execution of Union All and Unionが実行されている時の特徴は, PX SELECTOR があるかどうかで見分けます.
いつもと同じように 21c で確認します.
SCOTT@orclpdb1> select banner from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
準備として, 12c R1以前ではシリアルに処理される代表格, リモート表を用意します. （昨日のネタはこの準備のためでもあったり）
SCOTT@orclpdb2> @day16
1* CREATE DATABASE LINK remote_scott CONNECT TO scott IDENTIFIED BY tiger USING 'orclpdb1'
データベース・リンクが作成されました.
1 CREATE TABLE local_emp
2 AS
3 SELECT *
4 FROM
5 emp@remote_scott
6 ORDER BY
7 empno
8* FETCH FIRST 5 ROWS ONLY
表が作成されました.
1* INSERT INTO local_emp(empno, ename) VALUES(1, 'NULL')
1行が作成されました.
クエリーの結果、たまたま、empno順に並んでいるように見える結果もありますが、ソートしたい場合は、必ず！！！ ORDER BY句が必要ですからねw（誰となくw）
シリアルで実行してみます. よく見る実行計画という名のレントゲンです. リモート表は Operationが REMOTE として現れています. 2つある分岐は, シリアルに実行されます.
1 SELECT
2 *
3 FROM
4 local_emp
5 UNION ALL
6 SELECT
7 *
8 FROM
9* emp@remote_scott
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
1 NULL
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
実行計画
----------------------------------------------------------
Plan hash value: 1607333107
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 990 | 5 (0)| 00:00:01 | | |
| 1 | UNION-ALL | | | | | | | |
| 2 | TABLE ACCESS FULL| LOCAL_EMP | 6 | 522 | 2 (0)| 00:00:01 | | |
| 3 | REMOTE | EMP | 12 | 468 | 3 (0)| 00:00:01 | REMOT~ | R->S |
------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "EMP"
"EMP" (accessing 'REMOTE_SCOTT' )
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
次に, パラレルクエリーにして, かつ, Concurrent Execution を抑止する NO_PQ_CONCURRENT_UNION を付加して実行してみました.
PX SELECTOR は現れていません. パラレルクエリーではありますが, 分岐は, 順に処理されていきます.
1 SELECT
2 /*+
3 PARALLEL(3)
4 NO_PQ_CONCURRENT_UNION
5 */
6 *
7 FROM
8 local_emp
9 UNION ALL
10 SELECT
11 *
12 FROM
13* emp@remote_scott
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
1 NULL
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
実行計画
----------------------------------------------------------
Plan hash value: 3305250800
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ/Ins |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 990 | 5 (0)| 00:00:01 | | | |
| 1 | UNION-ALL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 6 | 522 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 6 | 522 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| LOCAL_EMP | 6 | 522 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 6 | REMOTE | EMP | 12 | 468 | 3 (0)| 00:00:01 | REMOT~ | R->S | |
----------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
6 - SELECT /*+ SHARED (3) */ "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "EMP"
"EMP" (accessing 'REMOTE_SCOTT' )
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 3 because of hint
本日の主役 PX SELECTOR が REMOTE 操作の上に出てきました. この状態であれば, 各分岐はパラレルに処理されることになり, 分岐が順に処理されるより処理時間は短縮されることになります（REMOTE処理が重かったら, その処理時間に引きづら訳ですが, 順に処理するよりは早く終わると予想できますよね）
1 SELECT
2 /*+
3 PARALLEL(3)
4 */
5 *
6 FROM
7 local_emp
8 UNION ALL
9 SELECT
10 *
11 FROM
12* emp@remote_scott
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
1 NULL
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
実行計画
----------------------------------------------------------
Plan hash value: 2359762278
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 990 | 5 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | | | | | Q1,00 | P->S | QC (RAND) |
| 3 | UNION-ALL | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 6 | 522 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| LOCAL_EMP | 6 | 522 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 6 | PX SELECTOR | | | | | | Q1,00 | PCWP | |
| 7 | REMOTE | EMP | 12 | 468 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
7 - SELECT /*+ SHARED (3) */ "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "EMP"
"EMP" (accessing ':Q1000' )
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 3 because of hint
以下, UNIONでも同様に, 実行計画というレントゲンを確認しておきましょう. まずはシリアル.
1 SELECT
2 *
3 FROM
4 local_emp
5 UNION
6 SELECT
7 *
8 FROM
9* emp@remote_scott
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
1 NULL
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
実行計画
----------------------------------------------------------
Plan hash value: 2189749816
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 990 | 7 (29)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 18 | 990 | 7 (29)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | TABLE ACCESS FULL| LOCAL_EMP | 6 | 522 | 2 (0)| 00:00:01 | | |
| 4 | REMOTE | EMP | 12 | 468 | 3 (0)| 00:00:01 | REMOT~ | R->S |
-------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "EMP"
"EMP" (accessing 'REMOTE_SCOTT' )
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
NO_PQ_CONCURRENT_UNIONでConcurrent Execution を抑止してみると, 順に処理されていることがわかります.
1 SELECT
2 /*+
3 PARALLEL(3)
4 NO_PQ_CONCURRENT_UNION 5 */
6 *
7 FROM
8 local_emp
9 UNION
10 SELECT
11 *
12 FROM
13* emp@remote_scott
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
7902 FORD ANALYST 7566 81-12-03 3000 20
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7369 SMITH CLERK 7902 80-12-17 800 20
1 NULL
7900 JAMES CLERK 7698 81-12-03 950 30
7934 MILLER CLERK 7782 82-01-23 1300 10
7839 KING PRESIDENT 81-11-17 5000 10
7782 CLARK MANAGER 7839 81-06-09 2450 10
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
実行計画
----------------------------------------------------------
Plan hash value: 622056383
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ/Ins |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 990 | 7 (29)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 18 | 990 | 7 (29)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | 18 | 990 | 7 (29)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 18 | 990 | 7 (29)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | 18 | 990 | 7 (29)| 00:00:01 | Q1,01 | P->P | HASH |
| 6 | HASH UNIQUE | | 18 | 990 | 7 (29)| 00:00:01 | Q1,01 | PCWP | |
| 7 | UNION-ALL | | | | | | Q1,01 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 6 | 522 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 9 | TABLE ACCESS FULL | LOCAL_EMP | 6 | 522 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 10 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 11 | PX RECEIVE | | 12 | 468 | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 12 | PX SEND ROUND-ROBIN| :TQ10000 | 12 | 468 | 3 (0)| 00:00:01 | | S->P | RND-ROBIN |
| 13 | REMOTE | EMP | 12 | 468 | 3 (0)| 00:00:01 | REMOT~ | R->S | |
-----------------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
13 - SELECT /*+ SHARED (3) */ "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "EMP" "EMP"
(accessing 'REMOTE_SCOTT' )
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 3 because of hint
UNIONでもPX SELECTORが現れ, 分岐がパラレルに処理されていることが見えます. ：）
1 SELECT
2 /*+
3 PARALLEL(3)
4 */
5 *
6 FROM
7 local_emp
8 UNION
9 SELECT
10 *
11 FROM
12* emp@remote_scott
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7369 SMITH CLERK 7902 80-12-17 800 20
1 NULL
7900 JAMES CLERK 7698 81-12-03 950 30
7934 MILLER CLERK 7782 82-01-23 1300 10
7839 KING PRESIDENT 81-11-17 5000 10
7782 CLARK MANAGER 7839 81-06-09 2450 10
実行計画
----------------------------------------------------------
Plan hash value: 3647789528
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 990 | 7 (29)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 18 | 990 | 7 (29)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | 18 | 990 | 7 (29)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 18 | 990 | 7 (29)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 18 | 990 | 7 (29)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | HASH UNIQUE | | 18 | 990 | 7 (29)| 00:00:01 | Q1,00 | PCWP | |
| 7 | UNION-ALL | | | | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 6 | 522 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| LOCAL_EMP | 6 | 522 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX SELECTOR | | | | | | Q1,00 | PCWP | |
| 11 | REMOTE | EMP | 12 | 468 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
11 - SELECT /*+ SHARED (3) */ "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "EMP"
"EMP" (accessing ':Q1000' )
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 3 because of hint
ちなみに, Concurrent Execution of Union All and Union と書いている通り, MINUS/EXCEPT/INTERSECTでは今まで通りです. EXCEPTとINTERSECTの実行計画で見てみます. PX SELECTOR は現れていません. (MINUSとEXCEPTはどちらもMINUSとして扱われているので片方だけで十分です)
1 SELECT
2 /*+
3 PARALLEL(3)
4 */
5 *
6 FROM
7 local_emp
8 EXCEPT
9 SELECT
10 *
11 FROM
12* emp@remote_scott
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
1 NULL
実行計画
----------------------------------------------------------
Plan hash value: 331225933
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ/Ins |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 990 | 7 (29)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | | | | | Q1,02 | P->S | QC (RAND) |
| 3 | MINUS HASH | | | | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | | | | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | | | | | Q1,01 | P->P | HASH |
| 6 | HASH UNIQUE | | 6 | 522 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 6 | 522 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 8 | TABLE ACCESS FULL| LOCAL_EMP | 6 | 522 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 9 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 10 | PX RECEIVE | | 12 | 468 | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 12 | 468 | 3 (0)| 00:00:01 | | S->P | HASH |
| 12 | REMOTE | EMP | 12 | 468 | 3 (0)| 00:00:01 | REMOT~ | R->S | |
-------------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
12 - SELECT /*+ SHARED (3) */ "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "EMP"
"EMP" (accessing 'REMOTE_SCOTT' )
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 3 because of hint
1 SELECT
2 /*+
3 PARALLEL(3)
4 */
5 *
6 FROM
7 local_emp
8 INTERSECT
9 SELECT
10 *
11 FROM
12* emp@remote_scott
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
実行計画
----------------------------------------------------------
Plan hash value: 1417135745
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ/Ins |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 990 | 7 (29)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | | | | | Q1,02 | P->S | QC (RAND) |
| 3 | INTERSECTION HASH | | | | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | | | | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | | | | | Q1,01 | P->P | HASH |
| 6 | HASH UNIQUE | | 6 | 522 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 6 | 522 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 8 | TABLE ACCESS FULL| LOCAL_EMP | 6 | 522 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 9 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 10 | PX RECEIVE | | 12 | 468 | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 12 | 468 | 3 (0)| 00:00:01 | | S->P | HASH |
| 12 | REMOTE | EMP | 12 | 468 | 3 (0)| 00:00:01 | REMOT~ | R->S | |
-------------------------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
12 - SELECT /*+ SHARED (3) */ "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "EMP"
"EMP" (accessing 'REMOTE_SCOTT' )
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 3 because of hint
ということで, Day 16 done
残り9日だ. ふ〜〜〜〜っ. 明日も絶対, 担当は私です.
ではまた.
参考） Oracle Database 21c - VLDB and Partitioning Guide / 8.5.3.14 Concurrent Execution of Union All
Related article on Mac De Oracle
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 20 / MINUS
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#3 / RDFView
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database編 (全部俺）Advent Calendar 2019 - おまけ#4 / INDEX FULL SCAN (MIN/MAX) - Index Only Scan
・実行計画は, SQL文のレントゲン写真だ！ No.30 - LOAD TABLE CONVENTIONAL vs. LOAD AS SELECT
・実行計画は, SQL文のレントゲン写真だ！ No.31 - TEMP TABLE TRANSFORMATION LOAD AS SELECT (CURSOR DURATION MEMORY)
・実行計画は, SQL文のレントゲン写真だ！ No.32 - EXTERNAL TABLE ACCESS FULL / INMEMORY FULL
・実行計画は, SQL文のレントゲン写真だ！ No.33 - BITMAP CONVERSION TO ROWIDS
・実行計画は, SQL文のレントゲン写真だ！ No.34 - 似て非なるもの USE_CONCAT と OR_EXPAND ヒント と 手書きSQLのレントゲンの見分け方
・実行計画は, SQL文のレントゲン写真だ！ No.35 - 似て非なるもの USE_CONCAT と OR_EXPANDヒントとパラレルクエリー
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 1 / No.36 / INTERSECT ALL
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 2 / No.37 / MINUS ALL
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 3 / No.38 / EXCEPT and EXCEPT ALL
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 4 / No.39 / In-Memory Hybrid Scans
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 5 / No.40 / PIVOT and UNPIVOT
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 6 / No.41 / In-Memory Vectorized Join
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 7 / No.42 / INDEX RANGE SCAN (MULTI VALUE)
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 8 / No.43 / TABLE ACCESS BY INDEX ROWID BATCHED
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 9 / No.44 / COLLECTION ITERATOR PICKLER FETCH
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 10 / No.45 / MAT_VIEW REWRITE ACCESS FULL
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 11 / No.46 / GROUPING SETS, ROLLUP, CUBE
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 12 / No.47 / TEMP TABLE TRANSFORMATION
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 13 / No.48 / MULTI-TABLE INSERT
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 14 / No.49 / the DUAL Table
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 15 / No.50 / REMOTE
| 固定リンク | 0
コメント