consistent gets量が多いにもかかわらずNOCOMPRESSが速い

In-Memory Parallel Queryの続き、

automatic DOPの検証で、In-Memory PQはNOCOMPRESSの方が２倍も速かった。
実行時間は
NOCOMPRESS　　0.33秒
COMPRESS　　　0.62秒
consistent gets量が多いにもかかわらずNOCOMPRESSの方が2倍速い。
　106301　　　consistent gets
　67041　　　consistent gets

分析

NOCOMPRESSでのCPU使用時間：

SQL> alter session set parallel_degree_policy=auto;

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

SQL> set autot trace stat
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  /


統計
----------------------------------------------------------
         22  recursive calls
          0  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> 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                                                 80
DB time                                                                 194

NOCOMPRESSでのCPU使用時間：

SQL> set autot trace stat
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_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  /


統計
----------------------------------------------------------
         16  recursive calls
          0  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> 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                                                                 305

CPU使用時間を比べてみる：
NOCOMPRESS　　80+194 = 174
　COMPRESS　　122+305= 427
COMPRESS時はNOCOMPRESS時の2.5倍のCPUが使われている。

In-Memory nonParallel + nocompressで16000qph達成 の中で、

COMPRESSされたOracle BlockをUNCOMPRESSするCPU負荷がなくなったことが高結果の要因だと思う。
COMPRESSでディスクI/O回数を減らすコストの方がUNCOMPRESSのコストより数倍大きい。でも今回はディスクI/OゼロだからUNCOMPRESSの負荷が目立ったんだな。。。

と書いた。今回それを証明した。