Search

OakieTags

more tags

Who's online

There are currently 1 user and 24 guests online.

Online users

Recent comments

Affiliations

Home

automatic DOP

Submitted by Ichiro Obata on Fri, 2010-09-03 13:24

nocompressでIn-Memory Parallel Query の中でCOMPRESS->NOCOMPRESSにしたらパラレル度が自動的に4->6になった。

NOCOMPRESSだとパラレル度は上がるのか？

NOCOMPRESSのlineitemをSelectする：

SQL> alter system flush buffer_cache;

システムが変更されました。

SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
  2  , sum(l_extendedprice) as sum_base_price
  3  , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
  4  , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
  5  , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
  6  , avg(l_discount) as avg_disc
  7  , count(*) as count_order
  8  from lineitem
  9  where l_shipdate <= date '1992-12-01' - interval '68' day (3)
 10  group by l_returnflag, l_linestatus
 11  order by l_returnflag, l_linestatus
 12  /

経過: 00:00:02.03

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

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |     5 |   135 |  6956   (1)| 00:01:24 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |     5 |   135 |  6956   (1)| 00:01:24 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT GROUP BY         |          |     5 |   135 |  6956   (1)| 00:01:24 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |     5 |   135 |  6956   (1)| 00:01:24 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000 |     5 |   135 |  6956   (1)| 00:01:24 |  Q1,00 | P->P | RANGE      |
|   6 |       HASH GROUP BY      |          |     5 |   135 |  6956   (1)| 00:01:24 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |   634K|    16M|  6941   (1)| 00:01:24 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| LINEITEM |   634K|    16M|  6941   (1)| 00:01:24 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

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

   8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 3


統計
----------------------------------------------------------
         22  recursive calls
          4  db block gets
     106301  consistent gets
     102004  physical reads
          0  redo size
       1456  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          2  rows processed

DOP=3が自動的に設定され、2.03秒かかった。
automatic DOP: Computed Degree of Parallelism is 3

次にCOMPRESSされたlineitemをSelectしてみる：


SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
  2  , sum(l_extendedprice) as sum_base_price
  3  , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
  4  , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
  5  , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
  6  , avg(l_discount) as avg_disc
  7  , count(*) as count_order
  8  from lineitem_comp
  9  where l_shipdate <= date '1992-12-01' - interval '68' day (3)
 10  group by l_returnflag, l_linestatus
 11  order by l_returnflag, l_linestatus
 12  /

経過: 00:00:01.51

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

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               |     5 |   135 |  6787   (2)| 00:01:22 |        |   |       |
|   1 |  PX COORDINATOR          |               |       |       |            |          |        |   |       |
|   2 |   PX SEND QC (ORDER)     | :TQ10001      |     5 |   135 |  6787   (2)| 00:01:22 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT GROUP BY         |               |     5 |   135 |  6787   (2)| 00:01:22 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |               |     5 |   135 |  6787   (2)| 00:01:22 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000      |     5 |   135 |  6787   (2)| 00:01:22 |  Q1,00 | P->P | RANGE      |
|   6 |       HASH GROUP BY      |               |     5 |   135 |  6787   (2)| 00:01:22 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |               |   634K|    16M|  6765   (1)| 00:01:22 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| LINEITEM_COMP |   634K|    16M|  6765   (1)| 00:01:22 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------

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

   8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2


統計
----------------------------------------------------------
         16  recursive calls
          4  db block gets
      67041  consistent gets
      66958  physical reads
          0  redo size
       1456  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          2  rows processed

DOP=2が自動的に設定され、1.51秒で終わった。

まとめてみる

NOCOMPRESS　　　automatic DOP=3
COMPRESS　　　　automatic DOP=2

NOCOMPRESSの方がDOPが大きく設定される。 --->アンコンプレスの処理負荷を考慮しているのだろうか？

実行時間は
NOCOMPRESS　　2.01秒
COMPRESS　　　1.51秒

COMPRESSの方が速い。それはphysical read量に比例する
　102004　　physical reads
　 66958　　physical reads

だから、Parallel QueryはCOMPRESSが有利。
でも、In-Memory Parallel Queryだと

上記のテスト後にもう一度二つのSQLを実行してみる：


SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
  2  , sum(l_extendedprice) as sum_base_price
  3  , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
  4  , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
  5  , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
  6  , avg(l_discount) as avg_disc
  7  , count(*) as count_order
  8  from lineitem
  9  where l_shipdate <= date '1992-12-01' - interval '68' day (3)
 10  group by l_returnflag, l_linestatus
 11  order by l_returnflag, l_linestatus
 12  /

経過: 00:00:00.33

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

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |     5 |   135 |  6956   (1)| 00:01:24 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |     5 |   135 |  6956   (1)| 00:01:24 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT GROUP BY         |          |     5 |   135 |  6956   (1)| 00:01:24 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |     5 |   135 |  6956   (1)| 00:01:24 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000 |     5 |   135 |  6956   (1)| 00:01:24 |  Q1,00 | P->P | RANGE      |
|   6 |       HASH GROUP BY      |          |     5 |   135 |  6956   (1)| 00:01:24 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |   634K|    16M|  6941   (1)| 00:01:24 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| LINEITEM |   634K|    16M|  6941   (1)| 00:01:24 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

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

   8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 3


統計
----------------------------------------------------------
         34  recursive calls
          4  db block gets
     106301  consistent gets
          0  physical reads
          0  redo size
       1456  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
  2  , sum(l_extendedprice) as sum_base_price
  3  , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
  4  , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
  5  , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
  6  , avg(l_discount) as avg_disc
  7  , count(*) as count_order
  8  from lineitem_comp
  9  where l_shipdate <= date '1992-12-01' - interval '68' day (3)
 10  group by l_returnflag, l_linestatus
 11  order by l_returnflag, l_linestatus
 12  /

経過: 00:00:00.62

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

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               |     5 |   135 |  6787   (2)| 00:01:22 |        |   |       |
|   1 |  PX COORDINATOR          |               |       |       |            |          |        |   |       |
|   2 |   PX SEND QC (ORDER)     | :TQ10001      |     5 |   135 |  6787   (2)| 00:01:22 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT GROUP BY         |               |     5 |   135 |  6787   (2)| 00:01:22 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |               |     5 |   135 |  6787   (2)| 00:01:22 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000      |     5 |   135 |  6787   (2)| 00:01:22 |  Q1,00 | P->P | RANGE      |
|   6 |       HASH GROUP BY      |               |     5 |   135 |  6787   (2)| 00:01:22 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |               |   634K|    16M|  6765   (1)| 00:01:22 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| LINEITEM_COMP |   634K|    16M|  6765   (1)| 00:01:22 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------

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

   8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2


統計
----------------------------------------------------------
         16  recursive calls
          4  db block gets
      67041  consistent gets
          0  physical reads
          0  redo size
       1456  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          2  rows processed

一回目のSQLでBuffer_Cache上にデータがあるのでphysical reads=0となる。

NOCOMPRESS　　　automatic DOP=3
COMPRESS　　　　automatic DOP=2

実行時間は
NOCOMPRESS　　0.33秒
COMPRESS　　　0.62秒

consistent gets量が多いにもかかわらずNOCOMPRESSの方が2倍速い。
　106301　　　consistent gets
　67041　　　consistent gets

でも、その差はたった0.3秒。しかし、その僅かな差を積み上げると：

という結果になった。