Search

OakieTags

more tags

Who's online

There are currently 0 users and 34 guests online.

Recent comments

Affiliations

Home

Parallel Queryを検証する

Submitted by Ichiro Obata on Sun, 2010-08-15 22:11

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

なぜ？？？