Search

Top 60 Oracle Blogs

Recent comments

Parallel Queryを検証する

TPC-Hベンチマークの続き

CPUはたいして回らなかった、そしてdirect path read待ちばかり、、、
ディスク転送量は限界に近づくどころか、むしろ下がっていった。
絶対におかしい!!!

Parallel Queryを検証する

初心に帰って、Parallel Queryが「どのくらい価値のあるものなのか?」を検証してみた。

過去の検証で作った大きなテーブルを使い負荷の高そうなSQLを適当に作った。
LINEITEM     : partitioning+compress
LINEITEM_COMP  : compress
LINEITEM_ORG   : オリジナル

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and (
5 (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
6 or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
7 or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
8 or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
9 or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
10 )


まずはNO_PARALLELで動かしてみる:

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 1;

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and (
5 (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
6 or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
7 or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
8 or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
9 or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
10 )
11 /

レコードが選択されませんでした。

経過: 00:14:33.61


実行計画
----------------------------------------------------------
Plan hash value: 2728639899
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17M| 3521M| | 331K(1)| 01:06:23 | | |
|* 1 | HASH JOIN | | 17M| 3521M| 468M| 331K(1)| 01:06:23 | | |
| 2 | TABLE ACCESS FULL | LINEITEM_ORG | 8476K| 371M| | 18970(2)| 00:03:48 | | |
|* 3 | HASH JOIN | | 24M| 3943M| 314M| 79115(1)| 00:15:50 | | |
| 4 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 245M| | 12309(2)| 00:02:28 | | |
| 5 | PARTITION RANGE ALL| | 5997K| 726M| | 11504(2)| 00:02:19 | 1 | 84 |
| 6 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| | 11504(2)| 00:02:19 | 1 | 84 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_COMMENT" LIKE '%obata%' AND "A"."L_RECEIPTDATE"<"B"."L_COMMITDATE" OR
"B"."L_COMMENT" LIKE '%tanaka%' AND "A"."L_RECEIPTDATE">"B"."L_COMMITDATE" AND "B"."L_COMMENT" IS NOT
NULL OR "A"."L_COMMENT" LIKE '%ichiro%' AND "A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE" OR "C"."L_COMMENT"
LIKE '%tanaka%' AND "A"."L_RECEIPTDATE">"C"."L_COMMITDATE" AND "C"."L_COMMENT" IS NOT NULL OR
"C"."L_COMMENT" LIKE '%ichiro%' AND "C"."L_COMMENT" IS NOT NULL AND
"A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")
3 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")


統計
----------------------------------------------------------
6136 recursive calls
0 db block gets
230506 consistent gets
990101 physical reads
7516 redo size
1441 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


なるほど遅い。14分33秒かかった。

次にパラレル度2で動かしてみる:

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and (
5 (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
6 or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
7 or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
8 or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
9 or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
10 )
11 ;

レコードが選択されませんでした。

経過: 00:00:32.26


実行計画
----------------------------------------------------------
Plan hash value: 854440364

---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17M| 3521M| 118K (2)| 00:23:47 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10012 | | | | | | | Q1,12 | P->S | QC (RAND) |
| 3 | BUFFER SORT | | 17M| 3521M| | | | | Q1,12 | PCWP | |
| 4 | CONCATENATION | | | | | | | | Q1,12 | PCWP | |
|* 5 | HASH JOIN | | 6966K| 1435M| 23787 (2)| 00:04:46 | | | Q1,12 | PCWP | |
| 6 | PX RECEIVE | | 1215K| 197M| 13240 (2)| 00:02:39 | | | Q1,12 | PCWP | |
| 7 | PX SEND HASH | :TQ10005 | 1215K| 197M| 13240 (2)| 00:02:39 | | | Q1,05 | P->P | HASH |
| 8 | BUFFER SORT | | 17M| 3521M| | | | | Q1,05 | PCWP | |
|* 9 | HASH JOIN | | 1215K| 197M| 13240 (2)| 00:02:39 | | | Q1,05 | PCWP | |
| 10 | PX RECEIVE | | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,05 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10000 | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,00 | P->P | BROADCAST |
| 12 | BUFFER SORT | | 17M| 3521M| | | | | Q1,00 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,00 | PCWC | |
|* 14 | TABLE ACCESS FULL| LINEITEM_COMP | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,00 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,05 | PCWC | |
| 16 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,05 | PCWP | |
| 17 | PX RECEIVE | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,12 | PCWP | |
| 18 | PX SEND HASH | :TQ10006 | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,06 | P->P | HASH |
| 19 | BUFFER SORT | | 17M| 3521M| | | | | Q1,06 | PCWP | |
| 20 | PX BLOCK ITERATOR | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,06 | PCWC | |
| 21 | TABLE ACCESS FULL | LINEITEM_ORG | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,06 | PCWP | |
|* 22 | HASH JOIN | | 3353K| 690M| 23783 (2)| 00:04:46 | | | Q1,12 | PCWP | |
| 23 | PX RECEIVE | | 585K| 94M| 13240 (2)| 00:02:39 | | | Q1,12 | PCWP | |
| 24 | PX SEND BROADCAST | :TQ10007 | 585K| 94M| 13240 (2)| 00:02:39 | | | Q1,07 | P->P | BROADCAST |
| 25 | BUFFER SORT | | 17M| 3521M| | | | | Q1,07 | PCWP | |
|* 26 | HASH JOIN | | 585K| 94M| 13240 (2)| 00:02:39 | | | Q1,07 | PCWP | |
| 27 | PX RECEIVE | | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,07 | PCWP | |
| 28 | PX SEND BROADCAST | :TQ10001 | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,01 | P->P | BROADCAST |
| 29 | BUFFER SORT | | 17M| 3521M| | | | | Q1,01 | PCWP | |
| 30 | PX BLOCK ITERATOR | | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,01 | PCWC | |
|* 31 | TABLE ACCESS FULL| LINEITEM_COMP | 299K| 12M| 6843 (2)| 00:01:23 | | | Q1,01 | PCWP | |
| 32 | PX BLOCK ITERATOR | | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,07 | PCWC | |
| 33 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,07 | PCWP | |
| 34 | PX BLOCK ITERATOR | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,12 | PCWC | |
| 35 | TABLE ACCESS FULL | LINEITEM_ORG | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,12 | PCWP | |
|* 36 | HASH JOIN | | 6451K| 1328M| 23786 (2)| 00:04:46 | | | Q1,12 | PCWP | |
| 37 | PX RECEIVE | | 1126K| 182M| 13240 (2)| 00:02:39 | | | Q1,12 | PCWP | |
| 38 | PX SEND HASH | :TQ10008 | 1126K| 182M| 13240 (2)| 00:02:39 | | | Q1,08 | P->P | HASH |
| 39 | BUFFER SORT | | 17M| 3521M| | | | | Q1,08 | PCWP | |
|* 40 | HASH JOIN | | 1126K| 182M| 13240 (2)| 00:02:39 | | | Q1,08 | PCWP | |
| 41 | PX RECEIVE | | 299K| 36M| 6397 (3)| 00:01:17 | | | Q1,08 | PCWP | |
| 42 | PX SEND BROADCAST | :TQ10002 | 299K| 36M| 6397 (3)| 00:01:17 | | | Q1,02 | P->P | BROADCAST |
| 43 | BUFFER SORT | | 17M| 3521M| | | | | Q1,02 | PCWP | |
| 44 | PX BLOCK ITERATOR | | 299K| 36M| 6397 (3)| 00:01:17 | 1 | 84 | Q1,02 | PCWC | |
|* 45 | TABLE ACCESS FULL| LINEITEM | 299K| 36M| 6397 (3)| 00:01:17 | 1 | 84 | Q1,02 | PCWP | |
| 46 | PX BLOCK ITERATOR | | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,08 | PCWC | |
| 47 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,08 | PCWP | |
| 48 | PX RECEIVE | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,12 | PCWP | |
| 49 | PX SEND HASH | :TQ10009 | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,09 | P->P | HASH |
| 50 | BUFFER SORT | | 17M| 3521M| | | | | Q1,09 | PCWP | |
| 51 | PX BLOCK ITERATOR | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,09 | PCWC | |
| 52 | TABLE ACCESS FULL | LINEITEM_ORG | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,09 | PCWP | |
|* 53 | HASH JOIN | | 15328 | 3233K| 23755 (2)| 00:04:46 | | | Q1,12 | PCWP | |
| 54 | PX RECEIVE | | 4082 | 689K| 16914 (2)| 00:03:23 | | | Q1,12 | PCWP | |
| 55 | PX SEND BROADCAST | :TQ10010 | 4082 | 689K| 16914 (2)| 00:03:23 | | | Q1,10 | P->P | BROADCAST |
| 56 | BUFFER SORT | | 17M| 3521M| | | | | Q1,10 | PCWP | |
|* 57 | HASH JOIN | | 4082 | 689K| 16914 (2)| 00:03:23 | | | Q1,10 | PCWP | |
| 58 | PX RECEIVE | | 21191 | 951K| 10518 (2)| 00:02:07 | | | Q1,10 | PCWP | |
| 59 | PX SEND BROADCAST | :TQ10003 | 21191 | 951K| 10518 (2)| 00:02:07 | | | Q1,03 | P->P | BROADCAST |
| 60 | BUFFER SORT | | 17M| 3521M| | | | | Q1,03 | PCWP | |
| 61 | PX BLOCK ITERATOR | | 21191 | 951K| 10518 (2)| 00:02:07 | | | Q1,03 | PCWC | |
|* 62 | TABLE ACCESS FULL| LINEITEM_ORG | 21191 | 951K| 10518 (2)| 00:02:07 | | | Q1,03 | PCWP | |
| 63 | PX BLOCK ITERATOR | | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,10 | PCWC | |
| 64 | TABLE ACCESS FULL | LINEITEM | 5997K| 726M| 6374 (2)| 00:01:17 | 1 | 84 | Q1,10 | PCWP | |
| 65 | PX BLOCK ITERATOR | | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,12 | PCWC | |
| 66 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,12 | PCWP | |
|* 67 | HASH JOIN | | 306K| 63M| 23780 (2)| 00:04:46 | | | Q1,12 | PCWP | |
| 68 | PX RECEIVE | | 81629 | 13M| 16939 (2)| 00:03:24 | | | Q1,12 | PCWP | |
| 69 | PX SEND BROADCAST | :TQ10011 | 81629 | 13M| 16939 (2)| 00:03:24 | | | Q1,11 | P->P | BROADCAST |
| 70 | BUFFER SORT | | 17M| 3521M| | | | | Q1,11 | PCWP | |
|* 71 | HASH JOIN | | 81629 | 13M| 16939 (2)| 00:03:24 | | | Q1,11 | PCWP | |
| 72 | PX RECEIVE | | 299K| 36M| 6397 (3)| 00:01:17 | | | Q1,11 | PCWP | |
| 73 | PX SEND BROADCAST | :TQ10004 | 299K| 36M| 6397 (3)| 00:01:17 | | | Q1,04 | P->P | BROADCAST |
| 74 | BUFFER SORT | | 17M| 3521M| | | | | Q1,04 | PCWP | |
| 75 | PX BLOCK ITERATOR | | 299K| 36M| 6397 (3)| 00:01:17 | 1 | 84 | Q1,04 | PCWC | |
|* 76 | TABLE ACCESS FULL| LINEITEM | 299K| 36M| 6397 (3)| 00:01:17 | 1 | 84 | Q1,04 | PCWP | |
| 77 | PX BLOCK ITERATOR | | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,11 | PCWC | |
| 78 | TABLE ACCESS FULL | LINEITEM_ORG | 8476K| 371M| 10510 (2)| 00:02:07 | | | Q1,11 | PCWP | |
| 79 | PX BLOCK ITERATOR | | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,12 | PCWC | |
| 80 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 245M| 6819 (2)| 00:01:22 | | | Q1,12 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
9 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")
14 - filter("C"."L_COMMENT" LIKE '%ichiro%' AND "C"."L_COMMENT" IS NOT NULL)
22 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
26 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE">"C"."L_COMMITDATE" AND (LNNVL("C"."L_COMMENT" LIKE '%ichiro%') OR LNNVL("C"."L_COMMENT" IS NOT
NULL) OR LNNVL("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")))
31 - filter("C"."L_COMMENT" LIKE '%tanaka%' AND "C"."L_COMMENT" IS NOT NULL)
36 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE")
40 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter((LNNVL("C"."L_COMMENT" LIKE '%tanaka%') OR LNNVL("A"."L_RECEIPTDATE">"C"."L_COMMITDATE") OR LNNVL("C"."L_COMMENT" IS
NOT NULL)) AND (LNNVL("C"."L_COMMENT" LIKE '%ichiro%') OR LNNVL("C"."L_COMMENT" IS NOT NULL) OR
LNNVL("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")))
45 - filter("A"."L_COMMENT" LIKE '%ichiro%')
53 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter((LNNVL("C"."L_COMMENT" LIKE '%tanaka%') OR LNNVL("A"."L_RECEIPTDATE">"C"."L_COMMITDATE") OR LNNVL("C"."L_COMMENT" IS
NOT NULL)) AND (LNNVL("C"."L_COMMENT" LIKE '%ichiro%') OR LNNVL("C"."L_COMMENT" IS NOT NULL) OR
LNNVL("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")))
57 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE">"B"."L_COMMITDATE" AND (LNNVL("A"."L_COMMENT" LIKE '%ichiro%') OR
LNNVL("A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE")))
62 - filter("B"."L_COMMENT" IS NOT NULL AND "B"."L_COMMENT" LIKE '%tanaka%')
67 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter((LNNVL("C"."L_COMMENT" LIKE '%tanaka%') OR LNNVL("A"."L_RECEIPTDATE">"C"."L_COMMITDATE") OR LNNVL("C"."L_COMMENT" IS
NOT NULL)) AND (LNNVL("C"."L_COMMENT" LIKE '%ichiro%') OR LNNVL("C"."L_COMMENT" IS NOT NULL) OR
LNNVL("A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")))
71 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")
filter("A"."L_RECEIPTDATE"<"B"."L_COMMITDATE" AND (LNNVL("B"."L_COMMENT" LIKE '%tanaka%') OR
LNNVL("A"."L_RECEIPTDATE">"B"."L_COMMITDATE") OR LNNVL("B"."L_COMMENT" IS NOT NULL)) AND (LNNVL("A"."L_COMMENT" LIKE '%ichiro%') OR
LNNVL("A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE")))
76 - filter("A"."L_COMMENT" LIKE '%obata%')


統計
----------------------------------------------------------
616 recursive calls
8 db block gets
566686 consistent gets
637866 physical reads
0 redo size
1441 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
22 sorts (memory)
4 sorts (disk)
0 rows processed

なるほど速い。32秒で終わった。
27倍の効果があった。

Parallel度をもう少し増やしてみる:

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3;

SQL> select A.* from lineitem A, lineitem_org B, lineitem_comp C
2 where A.l_orderkey = B.l_orderkey
3 and A.l_orderkey = C.l_orderkey
4 and (
5 (A.l_receiptdate < B.l_commitdate and A.l_comment like '%obata%')
6 or (A.l_receiptdate > B.l_commitdate and B.l_comment like '%tanaka%')
7 or (A.l_receiptdate <> B.l_commitdate and A.l_comment like '%ichiro%')
8 or (A.l_receiptdate > C.l_commitdate and C.l_comment like '%tanaka%')
9 or (A.l_receiptdate <> C.l_commitdate and C.l_comment like '%ichiro%')
10 )
11 ;

レコードが選択されませんでした。

経過: 00:04:38.49

実行計画
----------------------------------------------------------
Plan hash value: 3950163066

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17M| 3521M| | 38329 (2)| 00:07:40 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 17M| 3521M| | 38329 (2)| 00:07:40 | | | Q1,03 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 17M| 3521M| | 38329 (2)| 00:07:40 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 5997K| 245M| | 4871 (2)| 00:00:59 | | | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 5997K| 245M| | 4871 (2)| 00:00:59 | | | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 5997K| 245M| | 4871 (2)| 00:00:59 | | | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL | LINEITEM_COMP | 5997K| 245M| | 4871 (2)| 00:00:59 | | | Q1,00 | PCWP | |
|* 8 | HASH JOIN | | 34M| 5671M| 156M| 33083 (1)| 00:06:37 | | | Q1,03 | PCWP | |
| 9 | PX RECEIVE | | 8476K| 371M| | 7507 (2)| 00:01:31 | | | Q1,03 | PCWP | |
| 10 | PX SEND HASH | :TQ10001 | 8476K| 371M| | 7507 (2)| 00:01:31 | | | Q1,01 | P->P | HASH |
| 11 | PX BLOCK ITERATOR | | 8476K| 371M| | 7507 (2)| 00:01:31 | | | Q1,01 | PCWC | |
| 12 | TABLE ACCESS FULL| LINEITEM_ORG | 8476K| 371M| | 7507 (2)| 00:01:31 | | | Q1,01 | PCWP | |
| 13 | PX RECEIVE | | 5997K| 726M| | 4552 (2)| 00:00:55 | | | Q1,03 | PCWP | |
| 14 | PX SEND HASH | :TQ10002 | 5997K| 726M| | 4552 (2)| 00:00:55 | | | Q1,02 | P->P | HASH |
| 15 | PX BLOCK ITERATOR | | 5997K| 726M| | 4552 (2)| 00:00:55 | 1 | 84 | Q1,02 | PCWC | |
| 16 | TABLE ACCESS FULL| LINEITEM | 5997K| 726M| | 4552 (2)| 00:00:55 | 1 | 84 | Q1,02 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."L_ORDERKEY"="C"."L_ORDERKEY")
filter("A"."L_COMMENT" LIKE '%obata%' AND "A"."L_RECEIPTDATE"<"B"."L_COMMITDATE" OR "B"."L_COMMENT" LIKE '%tanaka%' AND
"A"."L_RECEIPTDATE">"B"."L_COMMITDATE" AND "B"."L_COMMENT" IS NOT NULL OR "A"."L_COMMENT" LIKE '%ichiro%' AND
"A"."L_RECEIPTDATE"<>"B"."L_COMMITDATE" OR "C"."L_COMMENT" LIKE '%tanaka%' AND "A"."L_RECEIPTDATE">"C"."L_COMMITDATE" AND
"C"."L_COMMENT" IS NOT NULL OR "C"."L_COMMENT" LIKE '%ichiro%' AND "C"."L_COMMENT" IS NOT NULL AND
"A"."L_RECEIPTDATE"<>"C"."L_COMMITDATE")
8 - access("A"."L_ORDERKEY"="B"."L_ORDERKEY")


統計
----------------------------------------------------------
31 recursive calls
0 db block gets
231720 consistent gets
229981 physical reads
0 redo size
1441 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

げっ!遅くなった。
11倍遅くなった。

結果を表にすると

DOP MI:SS physical reads x8K(block size)
no parallel 14:33.61 990101 7.55GB
parallel 2 00:32.26 637866 4.87GB
parallel 3 04:38.49 229981 1.8GB
parallel 4 03:48.20 229981 1.8GB
parallel 8 02:58.65 229981 1.8GB

なぜ???