In-Memory PQはCPU dpend?

ここまでの話の流れだとIn-Memory (Non) Parallel QueryはCPU dependで、
Parallel QueryはI/O dependみたいに感じる。
consistent gets量が多いにもかかわらずNOCOMPRESSが速い　で行ったテストと同じことをParallel Queryでも行ってみる。

まずはNOCOMPRESSをDOP=2でSelect：

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
に接続されました。
SQL> alter system flush buffer_cache;

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

SQL> alter session force parallel query parallel 2;

セッションが変更されました。

SQL> set autot trace stat
SQL> set timi on
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.14

統計
----------------------------------------------------------
         36  recursive calls
          0  db block gets
     103961  consistent gets
     102003  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
          7  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> set autot off
SQL>
SQL> select n.name, m.value
  2  from sys.v_$mystat m, sys.v_$statname n
  3  where n.name in ('CPU used by this session','DB time')
  4  and n.statistic# = m.statistic#
  5  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                122
DB time                                                                2409

次に、COMPRESSを同じくDOP=2でSelect：

SQL> alter system flush buffer_cache;

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

SQL> alter session force parallel query parallel 2;

セッションが変更されました。

SQL> set autot trace stat
SQL> set timi on
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.19

統計
----------------------------------------------------------
         37  recursive calls
          0  db block gets
      67123  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
          7  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> set autot off
SQL>
SQL> select n.name, m.value
  2  from sys.v_$mystat m, sys.v_$statname n
  3  where n.name in ('CPU used by this session','DB time')
  4  and n.statistic# = m.statistic#
  5  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                138
DB time                                                                1428

実行時間を比べると：
NOCOMPRESS　2.14秒
　COMPRESS　1.19秒
繰り返しになってしまうが、Parallel QueryはCOMPRESSの方が有利。

そして、CPU時間を比較してみる：

(in 10s of milliseconds)

In-Memory Parallel Queryと比べるとCPU使用時間もParallel Queryの方が多い。
In-Memoryの方がCPUを消費するから「多くてもたった5倍」しか速くない！と勘違いをしていた。