Search

Navigation

Affiliations

In-Memory PQ

In-Memory Parallel QueryのテストをするとCPUビジーになるけど、、、

Original article

Submitted by Ichiro Obata on Thu, 2010-09-16 19:33

In-Memory Parallel Queryセッション数のときのリソースモニターを見ると、セッション数を増やしていくとCPUは100%ビジーとなった。
でもIn-Memory PQはCPU dpend? ではParallel Queryの方が実はCPU合計時間が多いと書いた。
Parallel Queryはdirect path readのビジーによりCPUが回らないだけで、合計のCPU時間はIn-Memory PQより多い。

Heavy SQLの比較で取得したTPC-HのTop5 SQLのCPU時間を比較してみる。
比較は、前回のV_$mystatからの統計情報を使用した。値はCPU時間（カッコ内）はDB時間

SQL#	In-Memory PQ	Paralle Query
1	414 (794)	433 (1379)
2	418 (943)	449 (1347)
3	140 (357)	166 (837)
4	548 (1160)	566 (1432)
5	219 (508)	246 (855)

確かに、Parallel Queryの方がCPU時間が多い。
でも、これは全てがbuffer cache上に収まっているからだ。ということが前回のテストで分かった。
buffer cacheにデータがないと：

SQL#	In-Memory PQ	Paralle Query
1	553 (1488)	433 (1379)
2	514 (1793)	449 (1347)
3	247 (1113)	166 (837)
4	567 (1790)	566 (1432)
5	307 (1195)	246 (855)

>「buffer cacheにデータがないと」... In-Memory PQは実行できない。上記との整合性のためIn-Memory PQと記した。
In-Memory Parallel Queryはbuffer cacheにscattered readでデータを読み込むのでCPUも多く使う。
だからIn-Memory Parallel Queryはセッションレベルで意識して使うべき。と思う。

In-Memory PQ

Buffer Cacheにデータがない場合のparallel_degree_policy=autoの動き

Original article

Submitted by Ichiro Obata on Thu, 2010-09-16 18:16

前回、buffer cacheが足りなくなったら？というシナリオでテストをしたけど、なんとなく納得できない。
そもそもparallel_degree_policy=autoでIn-Memory PQが動くはず（動いているはず）と信じていいの？なんていう疑問が湧いてくる。
buffer cacheをflushしてbuffer cacheにデータがない状況で

parallel_degree_policy=auto:


Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 

SQL> alter system flush buffer_cache;
システムが変更されました。
SQL> alter system flush shared_pool;
システムが変更されました。
SQL> set timi on
SQL> set autot traceSQL> alter session set parallel_degree_policy=auto;
セッションが変更されました。
SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
  2  from customer, orders, lineitem
  3  where o_orderkey in
  4  ( select l_orderkey
  5  from lineitem
  6  group by l_orderkey
  7  having sum(l_quantity) > 313)
  8  and c_custkey = o_custkey
  9  and o_orderkey = l_orderkey
 10  group by c_name, c_custkey
 11  , o_orderkey
 12  , o_orderdate
 13  , o_totalprice
 14  order by o_totalprice desc, o_orderdate;
12行が選択されました。
経過: 00:00:02.88
実行計画----------------------------------------------------------Plan hash value: 1448507623
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          | 15209 |   950K|       | 20231   (2)| 00:04:03 |        |      |            |
|   1 |  PX COORDINATOR                       |          |       |       |       |            |       |   |      |            |
|   2 |   PX SEND QC (ORDER)                  | :TQ10005 | 15209 |   950K|       | 20231   (2)| 00:04:03 |  Q1,05 | P->S | QC (ORDER) |
|   3 |    SORT GROUP BY                      |          | 15209 |   950K|  1088K| 20231   (2)| 00:04:03 |  Q1,05 | PCWP |            |
|   4 |     PX RECEIVE                        |          | 15209 |   950K|       | 20229   (2)| 00:04:03 |  Q1,05 | PCWP |            |
|   5 |      PX SEND RANGE                    | :TQ10004 | 15209 |   950K|       | 20229   (2)| 00:04:03 |  Q1,04 | P->P | RANGE      |
|*  6 |       HASH JOIN                       |          | 15209 |   950K|       | 20229   (2)| 00:04:03 |  Q1,04 | PCWP |            |
|   7 |        PX RECEIVE                     |          |  3750 |   201K|       | 13435   (3)| 00:02:42 |  Q1,04 | PCWP |            |
|   8 |         PX SEND BROADCAST             | :TQ10003 |  3750 |   201K|       | 13435   (3)| 00:02:42 |  Q1,03 | P->P | BROADCAST  |
|*  9 |          HASH JOIN                    |          |  3750 |   201K|       | 13435   (3)| 00:02:42 |  Q1,03 | PCWP |            |
|  10 |           PX RECEIVE                  |          |  3750 |   113K|       | 13128   (3)| 00:02:38 |  Q1,03 | PCWP |            |
|  11 |            PX SEND BROADCAST          | :TQ10002 |  3750 |   113K|       | 13128   (3)| 00:02:38 |  Q1,02 | P->P | BROADCAST  |
|* 12 |             HASH JOIN                 |          |  3750 |   113K|       | 13128   (3)| 00:02:38 |  Q1,02 | PCWP |            |
|  13 |              PX RECEIVE               |          | 73952 |   433K|       | 11166   (3)| 00:02:14 |  Q1,02 | PCWP |            |
|  14 |               PX SEND BROADCAST       | :TQ10001 | 73952 |   433K|       | 11166   (3)| 00:02:14 |  Q1,01 | P->P | BROADCAST  |
|  15 |                VIEW                   | VW_NSO_1 | 73952 |   433K|       | 11166   (3)| 00:02:14 |  Q1,01 | PCWP |            |
|* 16 |                 FILTER                |          |       |       |       |            |       |  Q1,01 | PCWC |       |
|  17 |                  HASH GROUP BY        |          |   185 |   649K|   114M| 11166   (3)| 00:02:14 |  Q1,01 | PCWP |            |
|  18 |                   PX RECEIVE          |          |  5997K|    51M|       |  6773   (1)| 00:01:22 |  Q1,01 | PCWP |            |
|  19 |                    PX SEND HASH       | :TQ10000 |  5997K|    51M|       |  6773   (1)| 00:01:22 |  Q1,00 | P->P | HASH       |
|  20 |                     PX BLOCK ITERATOR |          |  5997K|    51M|       |  6773   (1)| 00:01:22 |  Q1,00 | PCWC |            |
|  21 |                      TABLE ACCESS FULL| LINEITEM |  5997K|    51M|       |  6773   (1)| 00:01:22 |  Q1,00 | PCWP |            |
|  22 |              PX BLOCK ITERATOR        |          |  1500K|    35M|       |  1953   (1)| 00:00:24 |  Q1,02 | PCWC |            |
|  23 |               TABLE ACCESS FULL       | ORDERS   |  1500K|    35M|       |  1953   (1)| 00:00:24 |  Q1,02 | PCWP |            |
|  24 |           PX BLOCK ITERATOR           |          |   150K|  3515K|       |   306   (1)| 00:00:04 |  Q1,03 | PCWC |            |
|  25 |            TABLE ACCESS FULL          | CUSTOMER |   150K|  3515K|       |   306   (1)| 00:00:04 |  Q1,03 | PCWP |            |
|  26 |        PX BLOCK ITERATOR              |          |  5997K|    51M|       |  6773   (1)| 00:01:22 |  Q1,04 | PCWC |            |
|  27 |         TABLE ACCESS FULL             | LINEITEM |  5997K|    51M|       |  6773   (1)| 00:01:22 |  Q1,04 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------

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

   6 - access("O_ORDERKEY"="L_ORDERKEY")
   9 - access("C_CUSTKEY"="O_CUSTKEY")
  12 - access("O_ORDERKEY"="L_ORDERKEY")
  16 - filter(SUM("L_QUANTITY")>313)

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


統計
----------------------------------------------------------
       1079  recursive calls
          0  db block gets
     156736  consistent gets
      89285  physical reads
          0  redo size
       1563  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
         12  rows processed

automatic DOP: Computed Degree of Parallelism is 2と出た。
あれ？Parallelで動いてる？
間違ったことを書いちゃった！
そんなはずはない：

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','physical reads direct')
  4  and n.statistic# = m.statistic#
  5  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                511
DB time                                                                1608
physical reads direct                                                     0

「良かった。間違ってない」ちゃんとdirect path readはしていない。

確認のため、同じことをParallel Queryでやってみる（念のためbuffer_cacheもflushして）

force prallel query 2:


SQL> alter system flush buffer_cache;
システムが変更されました。
SQL> alter system flush shared_pool;
システムが変更されました。
SQL> set timi on
SQL> set autot trace
SQL> alter session force parallel query parallel 2;
セッションが変更されました。
SQL> select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
  2  from customer, orders, lineitem
  3  where o_orderkey in
  4  ( select l_orderkey
  5  from lineitem
  6  group by l_orderkey
  7  having sum(l_quantity) > 313)
  8  and c_custkey = o_custkey
  9  and o_orderkey = l_orderkey
 10  group by c_name, c_custkey
 11  , o_orderkey
 12  , o_orderdate
 13  , o_totalprice
 14  order by o_totalprice desc, o_orderdate;

12行が選択されました。

経過: 00:00:02.77
....
....  same plan ...
....
統計
----------------------------------------------------------
       1009  recursive calls
          0  db block gets
     156652  consistent gets
     156241  physical reads
          0  redo size
       1563  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL> set autot off
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','physical reads direct')
  4  and n.statistic# = m.statistic#
  5  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                460
DB time                                                                1532
physical reads direct                                                153219

physical reads direct=153219とphysical reads数とほぼ同じになっている。

実行時間を比べてみると、やはりdirect path readを行うParallel Queryの方が速い。

まとめると、
buffer cacheにデータが全くない場合parallel_degree_policy=autoだと、

Parallel Queryをしたのと同じ実行計画が出てくる

automatic DOP: Computed Degree of Parallelism is 2とも出る

でもParallel実行はされていない

In-Memory PQ

In-Memory Parallel Query環境でメモリがだんだん足りなくなると

Original article

Submitted by Ichiro Obata on Tue, 2010-09-14 08:24

前回In-Memory PQはdirect path readではなくてsequential readとscattered readでbuffer_cacheに読み込むと書いた。
今回のシナリオは、

対象が全てIn-memoryに有り、最高のスループットを出していた
しかし、月日の経過とともにデータ量が増え始め、ディスクI/Oで「継ぎ足しながら」In-Memory PQが動くようになった

同時4セッションで」のhammerora TPC-Hを動かした状況で

ASH Viewerでみると、scatterd readとsequential readの待ちが多く発生している。
read by other sessionのコンテンションが際立っている（前回の新久保君のコメント通り）。
もう少しデータを増やしてみると：

memory_targetで指定した領域が足りなくなり、3セッションがdirect path readでbuffer_cacheを使わないParallel Queryを行うように変わった。

こんな状況になってたら（#572406;">cache buffers chainsラッチミスより）：

■データ量に「見合わない」実メモリしか積んでいないと「あきらめる」
■In-Memory Parallel Queryを使わない。pararell_degree_policy=MANUALに戻す。
■或いは、Partition化してbuffer_cacheに収まるサイズに縮小する

前回は、強引に「cache buffers chainsラッチミスが慢性的に発生しているときは」と書いてしまったけど、In-Memory Parallel Queryが使えない状況をLatch Timeoutのような「いち」現象から言い当てるのは難しいです。
なぜならば、AUTO指定でParallel Queryが動いたとたん、待機要素のパーセンテージはdirect path readに独占されるからです。（テストで使用しているSSD　x　4のRAID0は、何度も書いていますが、520MB/sでデータ転送をしているのにも拘らずです。）

cache buffers chainsラッチミス

Original article

Submitted by Ichiro Obata on Tue, 2010-09-14 03:27

前回（同時4セッションIn-Memory Parallel Query）でcache buffers chainsラッチミスが気になった。

Non Parallel Query　->　buffer_cacheに読み込む
Parallel Query　　　->　buffer_cacheは使わない

が、まず基本です。そして、発行されるReadの種類も違います：

Non Parallel Query -> sequential read, scattered read
Parallel Query -> direct path read

Non Parallel Queryの2種類のReadの使い分けは：

sequential read　->　Index検索
scattered read　->　full scan

そして、ほとんどIndex検索ができないTPC-Hを複数セッションで同時に動かすと、full scanのscattered readがbuffer_cacheを奪い合い、cache buffers chainsラッチミスが発生します。
で、今回はIn-Memory Parallelのテストでこれが発生したのだから、
In-Memory PQもNon Parallelと同じようなReadでbuffer_cacheにデータを読み込むということが判ります。
In-Memory Parallel Queryも　->　sequential read, scattered read

始めからデータベースバッファに全て入っていれば"cache buffers chains"の競合は起こらない。

でも、実際の環境では、全部のデータをデータベースバッファに入れておくことなんてできないから、
In-Memory Parallel Queryを使い始めるとcache buffers chainsラッチミスが多くなる。

cache buffers chainsラッチミスが一時的に大きくなるのは仕方がない
cache buffers chainsラッチミスが慢性的に発生しているときは

データ量に「見合わない」実メモリしか積んでいないと「あきらめる」
In-Memory Parallel Queryを使わない。pararell_degree_policy=MANUALに戻す。

或いは、Partition化してbuffer_cacheに収まるサイズに縮小する

あきらめてもParallel Queryが動くのだから、けしてネガティブな対応ではないと思う。

In-Memory PQ

同時4セッション In-Memory Parallel Query

Original article

Submitted by Ichiro Obata on Wed, 2010-09-08 17:52

TPC-Hベンチマークの続き
In-Memory Non PQは8セッションまで、ある程度スケーラブルに処理能力は上がった（TPC-Hの結果参照）。
しかし、In-Memory PQはIn-Memory Parallel Queryセッション数でテストしたように、同時4セッションでqphは横ばいとなった。
念のために、もう一回4セッションをテスト：そして、CPU%は、前回とほぼ同じ「殆んど100%」で振り切れていた。

無駄なCPUはLatch TimeoutのSPINを調べるのが定石

statspackで分析してみると：


 Parent Latch Statistics DB/Inst: ORACLE/oracle  Snaps: 13-14
-> only latches with sleeps are shown
-> ordered by name

                                     Get                                Spin
Latch Name                      Requests       Misses     Sleeps        Gets
------------------------ --------------- ------------ ---------- -----------
Real-time plan statistic           7,048        1,133        107       1,026
active service list               25,346        2,817      1,716       1,139
call allocation                   41,422        2,215         39       2,177
dummy allocation                   9,807        1,635          2       1,633
enqueues                          63,653        3,107         10       3,097
messages                           9,540            4          1           3
parameter table manageme          24,461        2,459          1       2,458
qmn task queue latch                  52            1          1           0
query server process                  31           11         24           0
resmgr:free threads list           9,808        1,488          1       1,487
          -------------------------------------------------------------
 Child Latch Statistics DB/Inst: ORACLE/oracle  Snaps: 13-14
-> only latches with sleeps/gets > 1/100000 are shown
-> ordered by name, gets desc

                         Child          Get                                Spin
Latch Name                 Num     Requests       Misses     Sleeps        Gets
---------------------- ------- ------------ ------------ ---------- -----------
cache buffers chains     49990    2,791,680        5,344         32       5,313
cache buffers chains     42860      112,396        2,595          4       2,586
cache buffers chains     37884       91,118        3,150          2       3,148
cache buffers chains     31347       16,878        1,166          1       1,165
cache buffers chains     16597        5,280            4          1           3
cache buffers chains     48852        5,280            3          1           2
cache buffers chains     10163        5,200            3          1           2
cache buffers chains      4153        4,570            2          1           1
cache buffers chains     13172        4,568            1          1           0
cache buffers chains     14344        4,564            2          1           1
cache buffers chains     18218        4,564            2          1           1
cache buffers chains     46359        4,560            2          1           1
cache buffers chains     47367        4,560            2          1           1
cache buffers chains     55125        4,560            1          1           0
cache buffers chains     65459        4,560            4          1           3
cache buffers chains      6706        4,560            3          1           2
cache buffers chains      5678        4,560            2          1           1
cache buffers chains     17931        4,560            3          1           2
cache buffers chains     36331        4,560            1          1           0
cache buffers chains     43826        4,000            2          1           1
cache buffers chains     23857        3,440            5          1           4
cache buffers chains     49962        3,040            3          1           2
cache buffers chains     42216        3,040            3          1           2
cache buffers chains       345        3,040            1          1           0
cache buffers chains     51797        3,040            1          1           0
cache buffers chains     26238        2,885            2          1           1
cache buffers chains     35482        2,880            1          1           0
cache buffers chains     32567        2,880            3          1           2
cache buffers chains     39474        2,495            3          1           2
cache buffers chains     30680        2,480            1          1           0
cache buffers chains     44458        2,480            1          1           0
cache buffers chains     20453        2,480            3          1           2
cache buffers chains     46045        2,480            1          1           0
cache buffers chains     11421        2,480            1          1           0
cache buffers chains     31662        2,160            4          2           3
cache buffers chains     29644        1,920            1          1           0
cache buffers chains     33569        1,520            3          1           2
cache buffers lru chai      45       54,182          211          2         209
object queue header op      23       54,897        1,443          1       1,442
object queue header op      18       54,867        1,173          2       1,171
object queue header op      24       54,844        1,457          1       1,456
object queue header op      22       54,823        1,386          5       1,381
parallel query stats         1        5,720          920         13         907
process queue                6        2,526          110          2         108
process queue referenc      30      128,252           18          3          16
shared pool                  1      133,446       14,215         21      14,194
shared pool                  2      127,339       19,019         12      19,007
          -------------------------------------------------------------

cache buffers chainsでLatch get missがたくさん発生していた。
Oracle8の時代ならば_db_block_hash_bucketsやdb_block_lru_latchesなんてパラメータがあったのだけれど、11gではない。

一度にREADするBUFFER_CACHEの量を減らせばcache buffers chainsのLatch範囲も減る予感がする

db_file_multiblock_read_countを64->32に変更：あれ？同じ4セッションでキューイング管理が働いてしまった。

CPU%は：断続的に開放されている。
これは、In-Memory Parallel Queryセッション数でのテスト結果では同時8セッションで発生した現象だ。
そしてそのときは、以下のように書いた：

8セッションからのキューイング管理は実装CPUのスレッド数から割り出されているのだろうけど、今回使用しているCore i7 860だと、管理が始まる8セッションは少し遅すぎるのではないかと感じる。実際4セッションでCPUは振り切れている。

このときはキューイング管理は実装されるCPU数で決められると思ってたけど、db_file_multiblock_read_countが関係する。

今度は、db_file_multiblock_read_countを32->128と、当初の倍に増やしてみる：
そして、CPUもほぼ振り切れている状況に戻った

最後に、
DWHシステムでは、In-Memory Parallel Queryは「どんなことがあっても使いたい！」一番魅力的な機能です。
でも全てのデータをBUFFER_CACHEに乗せる事は物理的に難しいのでParallel Queryで補完する。それが理想形です。だから、前回のブログで大容量192GBメモリー搭載可能　System-Xマンのコマーシャル映像を載せたのです。
しかし、、、parallel_degree_policy=auto だけがIn-Memory PQを制御する指定で、CPUが振り切れないように制御する「キューイング管理」がどのタイミングで働くかが良く解りません。

ORA_LPENABLEを1に設定

Original article

Submitted by Ichiro Obata on Tue, 2010-09-07 16:12

In-Memory Parallel Queryの続き

データベースが大量のメモリーにアクセスするのがIn-Memory Parallel Queryだから、Large Pageサポート機能を検証してみた。

ラージ・ページのサポートは、Oracle Database 10gリリース1（10.1）以上の機能です。ラージ・ページのサポートにより、Windows Server 2003で実行されているメモリー集中型のデータベース・インスタンスのパフォーマンスが向上します。新たに導入されたオペレーティング・システム・サポートを利用することにより、Oracle Database 10gリリース1（10.1）以上では、プロセッサ・メモリー・アドレッシング・リソースをより効率よく使用できるようになりました。具体的には、ラージ・ページのサポートが有効になっていると、システムのCPUはRAM内のOracle Databaseバッファにより高速にアクセスできるようになります。4KBの増分でバッファをアドレッシングするかわりに、CPUはデータベース・バッファをアドレッシングする際にPhysical Address Extension（PAE）モードでは2MBのページ・サイズ、非PAEモードでは4MBのページ・サイズを使用するように指示されます。

Oracle Databaseプラットフォーム・ガイド　11gリリース1（11.1） for Microsoft Windows　E05885-03

まずは、メモリ内のPageロック機能権限を与える：

そして、レジストリにORAL_LPENABLE=1をセットする：

一応Rebootして、
THP-Hベンチマークを行うと：

nocompressでIn-Memory Parallel Queryの時の結果と比べると：

少し上がったようにも見えるが、ほとんど変わらない。

DB_BUFFER_CACHEが100GBなんていう環境であれば効果はあるのだろう。
非ページング対象になるという意味ではlock_sgaやnailed_sgaパラメータと似ている。

以前書いたExpress 5800 128GBメモリ搭載機クラス用の設定だと思う。

PQ, In-Memory PQ, In-Memory Non PQを比較する

Original article

Submitted by Ichiro Obata on Sat, 2010-09-04 17:53

hammeroraのTPC-Hベンチマークの10回繰り返しを行いASH Viewerで結果をモニタリングした：

A,B　Parallel Query　(PQが得意なCOMPRESSモードで実行)
C,D In-Memory Parallel Query　(In-Memoryが得意なNOCOMPRESSモードで実行。ただしlineitemのみ）
E,F In-Memory Non Parallel Query　(同上)

それぞれ、同時1セッションと2セッションの結果だ。

時間軸（X軸）を見るとCおよびDのIn-Memory Parallel Queryが一番速いのが分かる

AのPQ　1セッションとCのIn-Memory PQのCPU（緑）を見るとCのIn-Memory PQの方がCPUを使っているように見えるが、In-Memory PQはCPU dpend?で証明したようにParallel QueryのCPU使用時間の方が大きい。I/O待ちでCPUが回らないだけ

BのPQ　2セッションではI/O待ち(青)が限界となり、1セッションの2倍ぐらいの時間がかかっている

TPC-Hの結果でも以下のように書いた：

Parallel Queryは同時2セッションが限界で、それ以上は安定した計測値が出ないし、時間も掛かり過ぎた

Cの1セッションIn-Memory PQは、初めにphysical readsを行いbuffer_cacheにデータを読み込んでいるが、I/O待ちは発生しないので(青)が出ていない

Dの2セッションIn-Memory PQは1セッション実行より若干時間は掛かったが優秀にスケーラブルであった

しかし同時3セッションで全てのCPUスレッドが一杯になり、4セッションでRun Queue待ちが予測できる

E,FのIn-Memory Non PQはスケーラブルにほぼ同一時間内に終了している

そして、同時8セッションまである程度スケーラブルに処理されることが予測できる

TPC-Hの結果でも以下のように書いた：

それから、
In-Memoryはスケーラビリティでは大勝です。
でも、メモリに収まるサイズだからです。。。

でも、よくよく考えてみると、当たり前だな、同時8セッションまではスケーラブルで、それ以上は動かなくなるんだろう。

そして、Parallel Queryは初めから限界ディスク転送量を出してしまうので「同時実行制御」の工夫が必要だということが、今回のテストを通じて理解できた。

とIn-Memory non Parallel Queryじゃダメなんだよ！の中で書いたけど、In-Memory (Non) PQにも同じことが言える。In-Memory PQにはQueueing制御があるのだけど、

8セッションからのキューイング管理は実装CPUのスレッド数から割り出されているのだろうけど、今回使用しているCore i7 860だと、管理が始まる8セッションは少し遅すぎるのではないかと感じる。
実際4セッションでCPUは振り切れている。「In-Memory Parallel Queryセッション数」

同時8セッションまで動かしてしまうと、全てが動かなくなるので、やはり、同時制御の工夫が大事だと思う。

最後に、
Parallel QueryはCOMPRESSが得意。
In-Memory PQはNOCOMPRESSが得意。だから、

ALTER TABLE lineitem MOVE PARTITION 今月 NOCOMPRESS;
ALTER TABLE lineitem MOVE PARTITION 2ヶ月前 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION 3ヶ月前 COMPRESS;
..
ALTER TABLE lineitem MOVE PARTITION 1年前 COMPRESS;

のように工夫する。

In-Memory PQはCPU dpend?

Original article

Submitted by Ichiro Obata on Sat, 2010-09-04 03:25

ここまでの話の流れだと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-Memory PQ		Parallel Query
	CPU used	DB time	CPU used	DB time
NOCOMPRESS	80	194	122	2409
COMPRESS	122	305	138	1429

(in 10s of milliseconds)

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

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

Original article

Submitted by Ichiro Obata on Sat, 2010-09-04 02:55

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の負荷が目立ったんだな。。。

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

In-Memory PQ

automatic DOP

Original article

Submitted by Ichiro Obata on Fri, 2010-09-03 12: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秒。しかし、その僅かな差を積み上げると：

という結果になった。