Search

Top 60 Oracle Blogs

Recent comments

In-Memory PQ

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

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はセッションレベルで意識して使うべき。と思う。

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

前回、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 Parallel Query環境でメモリがだんだん足りなくなると

    前回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ラッチミス

    前回(同時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が動くのだから、けしてネガティブな対応ではないと思う。

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

    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に設定

    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を比較する

    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?

    ここまでの話の流れだと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が速い

    In-Memory Parallel Queryの続き、

    automatic DOPの検証で、In-Memory PQはNOCOMPRESSの方が2倍も速かった。
    実行時間は
    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の負荷が目立ったんだな。。。

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

    automatic DOP

    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秒。しかし、その僅かな差を積み上げると:

    という結果になった