hammerora

Ichiro Obata's picture

定石はPartitioningで、無駄なFull Scanを減らすこと

前回の続き、、、
CompressでPQが速くなるのは、I/O量(回数)が減るからと前回書いた。
で、もっと無駄なFullScanを減らすのがPartitioning。1セッションでCompressだけだと>
それをPartition化してみると
そして、同時4セッションテストだと

最後に、
新品SSDを大事に使いたいから、以下のコマンドを入れてTPC-HのベンチマークでSSDにTemporary Segmentがとられないようにした:
SQL> alter tablespace tpchtab read only;
戻すときは:
SQL> alter tablespace tpch read write;

次に効果的なチューニングはTableの大きさを考慮したテーブルごとのDOPの設定となる。FORCEでDOPを乱暴に設定するのは好ましくない。が、前回、チューニングばかりに集中して、最後にわからなくなったので、ここまでとします。なぜなら、ASMと比較する(次回から)方が大事だからです。

create table lineitem
pctfree 1
pctused 99
initrans 10
storage (freelist groups 4 freelists 84)
parallel
nologging
partition by range (l_shipdate)
(
partition item1 values less than (to_date('1992-01-01','YYYY-MM-DD')),
partition item2 values less than (to_date('1992-02-01','YYYY-MM-DD')),
partition item3 values less than (to_date('1992-03-01','YYYY-MM-DD')),
partition item4 values less than (to_date('1992-04-01','YYYY-MM-DD')),
partition item5 values less than (to_date('1992-05-01','YYYY-MM-DD')),
partition item6 values less than (to_date('1992-06-01','YYYY-MM-DD')),
partition item7 values less than (to_date('1992-07-01','YYYY-MM-DD')),
partition item8 values less than (to_date('1992-08-01','YYYY-MM-DD')),
partition item9 values less than (to_date('1992-09-01','YYYY-MM-DD')),
partition item10 values less than (to_date('1992-10-01','YYYY-MM-DD')),
partition item11 values less than (to_date('1992-11-01','YYYY-MM-DD')),
partition item12 values less than (to_date('1992-12-01','YYYY-MM-DD')),
partition item13 values less than (to_date('1993-01-01','YYYY-MM-DD')),
partition item14 values less than (to_date('1993-02-01','YYYY-MM-DD')),
partition item15 values less than (to_date('1993-03-01','YYYY-MM-DD')),
partition item16 values less than (to_date('1993-04-01','YYYY-MM-DD')),
partition item17 values less than (to_date('1993-05-01','YYYY-MM-DD')),
partition item18 values less than (to_date('1993-06-01','YYYY-MM-DD')),
partition item19 values less than (to_date('1993-07-01','YYYY-MM-DD')),
partition item20 values less than (to_date('1993-08-01','YYYY-MM-DD')),
partition item21 values less than (to_date('1993-09-01','YYYY-MM-DD')),
partition item22 values less than (to_date('1993-10-01','YYYY-MM-DD')),
partition item23 values less than (to_date('1993-11-01','YYYY-MM-DD')),
partition item24 values less than (to_date('1993-12-01','YYYY-MM-DD')),
partition item25 values less than (to_date('1994-01-01','YYYY-MM-DD')),
partition item26 values less than (to_date('1994-02-01','YYYY-MM-DD')),
partition item27 values less than (to_date('1994-03-01','YYYY-MM-DD')),
partition item28 values less than (to_date('1994-04-01','YYYY-MM-DD')),
partition item29 values less than (to_date('1994-05-01','YYYY-MM-DD')),
partition item30 values less than (to_date('1994-06-01','YYYY-MM-DD')),
partition item31 values less than (to_date('1994-07-01','YYYY-MM-DD')),
partition item32 values less than (to_date('1994-08-01','YYYY-MM-DD')),
partition item33 values less than (to_date('1994-09-01','YYYY-MM-DD')),
partition item34 values less than (to_date('1994-10-01','YYYY-MM-DD')),
partition item35 values less than (to_date('1994-11-01','YYYY-MM-DD')),
partition item36 values less than (to_date('1994-12-01','YYYY-MM-DD')),
partition item37 values less than (to_date('1995-01-01','YYYY-MM-DD')),
partition item38 values less than (to_date('1995-02-01','YYYY-MM-DD')),
partition item39 values less than (to_date('1995-03-01','YYYY-MM-DD')),
partition item40 values less than (to_date('1995-04-01','YYYY-MM-DD')),
partition item41 values less than (to_date('1995-05-01','YYYY-MM-DD')),
partition item42 values less than (to_date('1995-06-01','YYYY-MM-DD')),
partition item43 values less than (to_date('1995-07-01','YYYY-MM-DD')),
partition item44 values less than (to_date('1995-08-01','YYYY-MM-DD')),
partition item45 values less than (to_date('1995-09-01','YYYY-MM-DD')),
partition item46 values less than (to_date('1995-10-01','YYYY-MM-DD')),
partition item47 values less than (to_date('1995-11-01','YYYY-MM-DD')),
partition item48 values less than (to_date('1995-12-01','YYYY-MM-DD')),
partition item49 values less than (to_date('1996-01-01','YYYY-MM-DD')),
partition item50 values less than (to_date('1996-02-01','YYYY-MM-DD')),
partition item51 values less than (to_date('1996-03-01','YYYY-MM-DD')),
partition item52 values less than (to_date('1996-04-01','YYYY-MM-DD')),
partition item53 values less than (to_date('1996-05-01','YYYY-MM-DD')),
partition item54 values less than (to_date('1996-06-01','YYYY-MM-DD')),
partition item55 values less than (to_date('1996-07-01','YYYY-MM-DD')),
partition item56 values less than (to_date('1996-08-01','YYYY-MM-DD')),
partition item57 values less than (to_date('1996-09-01','YYYY-MM-DD')),
partition item58 values less than (to_date('1996-10-01','YYYY-MM-DD')),
partition item59 values less than (to_date('1996-11-01','YYYY-MM-DD')),
partition item60 values less than (to_date('1996-12-01','YYYY-MM-DD')),
partition item61 values less than (to_date('1997-01-01','YYYY-MM-DD')),
partition item62 values less than (to_date('1997-02-01','YYYY-MM-DD')),
partition item63 values less than (to_date('1997-03-01','YYYY-MM-DD')),
partition item64 values less than (to_date('1997-04-01','YYYY-MM-DD')),
partition item65 values less than (to_date('1997-05-01','YYYY-MM-DD')),
partition item66 values less than (to_date('1997-06-01','YYYY-MM-DD')),
partition item67 values less than (to_date('1997-07-01','YYYY-MM-DD')),
partition item68 values less than (to_date('1997-08-01','YYYY-MM-DD')),
partition item69 values less than (to_date('1997-09-01','YYYY-MM-DD')),
partition item70 values less than (to_date('1997-10-01','YYYY-MM-DD')),
partition item71 values less than (to_date('1997-11-01','YYYY-MM-DD')),
partition item72 values less than (to_date('1997-12-01','YYYY-MM-DD')),
partition item73 values less than (to_date('1998-01-01','YYYY-MM-DD')),
partition item74 values less than (to_date('1998-02-01','YYYY-MM-DD')),
partition item75 values less than (to_date('1998-03-01','YYYY-MM-DD')),
partition item76 values less than (to_date('1998-04-01','YYYY-MM-DD')),
partition item77 values less than (to_date('1998-05-01','YYYY-MM-DD')),
partition item78 values less than (to_date('1998-06-01','YYYY-MM-DD')),
partition item79 values less than (to_date('1998-07-01','YYYY-MM-DD')),
partition item80 values less than (to_date('1998-08-01','YYYY-MM-DD')),
partition item81 values less than (to_date('1998-09-01','YYYY-MM-DD')),
partition item82 values less than (to_date('1998-10-01','YYYY-MM-DD')),
partition item83 values less than (to_date('1998-11-01','YYYY-MM-DD')),
partition item84 values less than (MAXVALUE))
as select * from lineitem_org;

ALTER TABLE lineitem MOVE PARTITION item1 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item2 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item3 COMPRESS;
..
ALTER TABLE lineitem MOVE PARTITION item81 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item82 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item83 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item84 COMPRESS;
ALTER TABLE lineitem MOVE PARTITION item85 COMPRESS;
Ichiro Obata's picture

8月13日のCompressで40% Up と同じテストを行う

Parallel QueryはTable Compressを行うとI/O量(回数)が減って速くなる。
そこで、以前のテスト(Compressで40%アップ)と同じことをしてみた:
SQL> ALTER TABLE LINEITEM MOVE COMPRESS;
Compress前はということで、同じようにCompressの効果が大きいことがわかる。

そして、8月13日のテストではタイムオーバーとなった「同時4セッション」処理も、10分ぐらい掛かったけれど完走した:
TPC-HベンチマークのようなFull Scanは転送量が全てだから、マザーボード上のSATAインターフェースの限界があるIntel P55系のLGA1156マザーボードでは今回のような転送量が出せなかったからタイムオーバとなった。これをLGA1156マザーボードでやろうと思ったら、10万円ぐらいするRAIDボードをPCI Expressバスに積まなければならない。あるいは、高価なSANデバイスを購入する。。。

今回はAMDに乗り換えて、たった4本のSSDで、900MB/sでParallel Queyが実行できたのだから、RAIDカード1枚より安いよね。SSDを2枚追加すれば1GB/sを超えるはずです。
今回の転送量:

最後に、
貴重なPCI ExpressバスはInfiniBandで使う。

Ichiro Obata's picture

TPC-Hベンチマークテストを行う

8月3日に行った「試作機でTPC-Hベンチマークテストを行う 」はIntel i7-860 Quad Core機でKingstonのお買い得SSD4本で構築したRAID-0。マザーボードは前回紹介したASUS製。
今回はAMD Phenom2 6 Coreにcrusial REAL SSDを4本で構築した、同じくRAID-0。マザーボードはGIGABYTE社製(前回のブログ参照)。

(前回)1セッション、パラレル度=6でテスト:

そのときのディスク転送量は:

今回:ディスク転送量は800MB/秒ぐらいを平均的に出している

4セッションに増やしてみると、、、

平均的に900MB/秒を上回る。

最初のテストから4ヵ月後に、高価で購入ができなかったSATA3対応のSSDが1本12000円ぐらいで買えるようになり、Intel系のマザーボードでは限界であった500MB/sの壁もAMDに乗り換えることで簡単に突破できた。それも、マザーボード込みの値段で35000円程度と、安いほうへの乗り換えでだ。

最後に、
来年になれば、Intelの次世代プロセッサーSandy Bridgeとそれに対応したマザーボードで、またまた簡単に速くなるのだろう。ソフトウェアのバージョンアップをする時代ではない!ハードウェアごとバージョンアップする方が効果的です。

冬なので、暖房もかねて、ケースを買わずにこうしました:

Ichiro Obata's picture

同時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が振り切れないように制御する「キューイング管理」がどのタイミングで働くかが良く解りません。

Ichiro Obata's picture

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

という結果になった

Ichiro Obata's picture

Heavy SQLの比較

以前行ったHeavy SQL Top5の実行時間(秒数)にIn-Memory PQも追加してみた:
A=non parallel(all physical reads)
B=in-memory nonParallel
C=parallel(DOP=6)
D=In-Memory Parallel

SQL# A B C D
1 7.95 3.63 3.24 1.22
2 7.67 2.60 2.71 1.33
3 5.24 1.01 1.51 0.35
4 8.61 3.95 3.82 1.66
5 2.81 1.02 2.82 0.66

繰り返しになるが、In-Memory Parallel Queryでは明示的にパラレル度を設定することはできない。
しかし、前回のテストで示したようにパラレル度(DOP)は自動的に6になっている。
そして上記CのParallel(DOP=6)と比較すると2倍から5倍の性能が出ている。

1 select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
from customer, orders, lineitem
where o_orderkey in
( select l_orderkey
from lineitem
group by l_orderkey
having sum(l_quantity) > 313)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by c_name, c_custkey
, o_orderkey
, o_orderdate
, o_totalprice
order by o_totalprice desc, o_orderdate;
2 select nation, o_year, sum(amount) as sum_profit
from ( select n_name as nation, extract(year from o_orderdate) as o_year
, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from part, supplier, lineitem, partsupp, orders, nation
where s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%navy%') profit
group by nation, o_year
order by nation, o_year desc;
3 select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count
, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count
from orders, lineitem
where o_orderkey = l_orderkey
and l_shipmode in ('SHIP', 'FOB')
and l_commitdate < l_receiptdate
and l_shipdate < l_commitdate
and l_receiptdate >= date '1997-01-01'
and l_receiptdate < date '1997-01-01' + interval '1' year
group by l_shipmode
order by l_shipmode;
4 select s_name, count(*) as numwait
from supplier, lineitem l1, orders, nation
where s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists
( select * from lineitem l2
where l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey)
and not exists
( select * from lineitem l3
where l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate)
and s_nationkey = n_nationkey
and n_name = 'CHINA'
group by s_name
order by numwait desc, s_name
5 select supp_nation, cust_nation, l_year, sum(volume) as revenue
from
( select n1.n_name as supp_nation, n2.n_name as cust_nation
, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume
from supplier, lineitem, orders, customer, nation n1, nation n2
where s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and ( (n1.n_name = 'JAPAN' and n2.n_name = 'ETHIOPIA')
or (n1.n_name = 'ETHIOPIA' and n2.n_name = 'JAPAN'))
and l_shipdate between date '1995-01-01'
and date '1996-12-31') shipping
group by supp_nation, cust_nation, l_year
order by supp_nation, cust_nation, l_year;
Ichiro Obata's picture

nocompressでIn-Memory Parallel Query

前回までのIn-Memory Parallel PQのテストはLineitemをPartitioning+Compressで行った。
今回は、In-Memory Nonparallel PQで成果を上げたnoCOMPRESSもnoPartitionをテストしてみる。
同時1セッション:

前回(Partition+Compressでの同時1セッション):
約33%速くなっている。

そしてCPUは:

前回

アクティブなスレッドが増えている。そこで、実際に動いているパラレル数を調べる:


SQL> alter system flush shared_pool;
実行....
set lines 250
set pages 200
col module format a20
col STIME format a8
col ETIME format a8
col PAST format a13
col PX format '99'
col CLWAIT format '99'
col APWAIT format '99'
col SORTS format '999'
col EXECS format '999'
select /*+ no_monitoring */
to_char(min(c.SQL_EXEC_START),'HH24:MI:SS') STIME
,to_char(max(c.SAMPLE_TIME),'HH24:MI:SS') ETIME
,substr(to_char(max(c.SAMPLE_TIME)-min(c.SQL_EXEC_START),'HH24:MI:SS'),11) PAST
,max(a.EXECUTIONS) EXECS
,max(a.FETCHES) FETCHES
,max(a.PX_SERVERS_EXECUTIONS) PX
,max(a.SORTS) SORTS
,max(a.ROWS_PROCESSED) ROWS_P
,round(max(a.USER_IO_WAIT_TIME)) IOWAIT
-- ,round(max(a.CLUSTER_WAIT_TIME)) CLWAIT
-- ,round(max(a.APPLICATION_WAIT_TIME)) APWAIT
,round(max(a.CONCURRENCY_WAIT_TIME)) CCWAIT
,round(max(a.ELAPSED_TIME)) ELAPSED
,round(max(a.CPU_TIME)) CPU_TIME
,max(a.BUFFER_GETS) BUF_GETS
,max(a.OPTIMIZER_COST) COST
,a.sql_id
-- ,a.PLAN_HASH_VALUE
,a.MODULE
from v$sql a, v$active_session_history C
where a.sql_id = c.sql_id
and ELAPSED_TIME > 0
and a.PARSING_SCHEMA_NAME <> 'SYS'
group by a.PLAN_HASH_VALUE, a.sql_id, a.MODULE
order by ELAPSED
/
STIME ETIME PAST EXECS FETCHES PX SORTS ROWS_P IOWAIT CCWAIT ELAPSED CPU_TIME BUF_GETS COST SQL_ID MODULE
-------- -------- ------------- ----- ---------- --- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------- --------------------
10:28:01 10:28:02 00:00:01.111 1 1134 6 3 11335 0 0 1338078 1341608 128846 10821 03m0rwpcbg91m wish85t.exe
10:28:03 10:28:04 00:00:01.112 1 1 6 6 4 0 0 2275193 2199614 129422 8493 fd2kzsw1661aj wish85t.exe
10:28:04 10:29:15 00:01:11.319 2 36 12 12 350 0 0 8974783 8923256 290550 10912 3tfa6ymac4hjf wish85t.exe
10:28:08 10:28:09 00:00:01.135 1 1 6 0 1 0 0 660284 639605 108937 7199 2hnp28fnqyf79 wish85t.exe
10:28:15 10:28:16 00:00:01.155 1 1 6 3 5 0 0 1186749 1185608 125730 8282 2td6vctt7yyyc wish85t.exe
10:28:16 10:29:13 00:00:57.319 2 2 6 0 2 0 0 958642 936006 212602 6955 d86pbmkaa4zw1 wish85t.exe
10:28:17 10:28:18 00:00:01.155 1 1 6 3 2 0 0 1257967 1326007 133357 8766 4nyu0zmkb0zwd wish85t.exe
10:28:17 10:28:19 00:00:02.165 1 18 6 6 175 0 0 4450244 4383628 132295 10912 bgdpdz34pk0bb wish85t.exe
10:28:19 10:28:20 00:00:01.165 1 3902 6 3 39011 0 0 1873109 1856413 128864 11135 f2nnd6kk9q59y wish85t.exe
10:28:22 10:28:23 00:00:01.175 1 1820 4 2 18199 0 0 444539 374403 19412 3302 awf1cvg2yrh77 wish85t.exe
10:28:24 10:28:25 00:00:01.175 1 1 6 0 1 0 0 1163116 1138808 110263 7290 5ruvwajtp4vqb wish85t.exe
10:28:25 10:28:42 00:00:17.228 2 160 12 11 1584 0 0 11755852 11778078 465088 15330 b3kxur1xqvuqc wish85t.exe
10:28:30 10:28:31 00:00:01.197 1 1 6 6 4 0 0 2453008 2371217 129422 8493 f5mqcn3wd2y6w wish85t.exe
10:28:31 10:29:28 00:00:57.360 2 36 12 12 350 0 0 9146533 9079259 290550 10912 3dvyjuqvfx4vu wish85t.exe
10:28:36 10:28:38 00:00:02.218 1 4 4 2 36 0 0 3822420 3900026 22467 7343 fcfjqugcc1zy0 wish85t.exe
10:28:38 10:28:39 00:00:01.218 1 1 6 3 1 0 0 693405 717604 110263 7227 d11p6bxust62f wish85t.exe
10:28:40 10:28:41 00:00:01.228 1 73 6 6 724 0 0 830928 826803 125846 8242 bbh7vn9zr7hzv wish85t.exe
10:28:42 10:28:43 00:00:01.228 1 1 4 2 7 0 4760 423592 436802 22457 2617 97g61yc8yd935 wish85t.exe
10:28:44 10:28:45 00:00:01.238 1 1126 6 3 11258 0 0 1337040 1248008 128846 10883 agc7krh3xknh5 wish85t.exe
10:28:46 10:28:47 00:00:01.238 1 1 6 6 4 0 0 2274846 2262016 92962 8493 cr48h47mncxcm wish85t.exe
10:28:49 10:28:50 00:00:01.248 1 1 4 3 2 0 0 896043 904806 125730 8283 8pggb3uqdcn3x wish85t.exe
10:28:53 10:28:54 00:00:01.258 1 1 6 0 1 0 0 1160554 1201209 110263 7290 1d5n1nf4ra2nh wish85t.exe
10:28:58 10:28:59 00:00:01.278 1 1 4 3 5 0 0 1144141 1138807 125730 8282 949qdu8zw48nr wish85t.exe
10:28:59 10:29:00 00:00:01.278 1 1 5 4 4 0 0 2321305 2355615 129422 8493 80cyur05brmpa wish85t.exe
10:29:00 10:29:02 00:00:02.289 1 18 6 6 175 0 0 4521806 4430429 145275 10912 95qwbv8awanbz wish85t.exe
10:29:05 10:29:06 00:00:01.299 1 1 6 3 1 0 0 730361 733206 110263 7227 guxycg5jk7pbf wish85t.exe

多くがDOP(PX)=6で動いている。
前回はパラレル度=4だったので不公平になる。そこで以下のパラメータを変更する:

alter system set parallel_degree_limit=4 scope=memory;

しかし、、、DOP=6で動いてしまう。変わらない。

Ichiro Obata's picture

In-Memory Parallel Queryセッション数

In-Memory PQ続き、

今度は前回と同じ条件で同時セッション数を増やしていく

セッション数=2                  1セッションと比べて約72%増えた。
セッション数=4                  2セッションと比べ21%程度しか増えない。
セッション数=6                  4セッションとほぼ同じ結果。
セッション数=7                  4セッションとほぼ同じ結果で急激なダウンが発生。
セッション数=8                   同時2セッションより下がった。

そして、CPU使用率は

セッション数=2             まだ余裕が見られる。
セッション数=4             ほぼ100%。
セッション数=7             すべて100%。
セッション数=8              自動適にキューイング機能が働き、定期的にCPUが空く状況となった。

セッション数=8の時のキューイング状況を見る:

SQL> select status,queuing_time,sql_id
2 ,to_char(sql_exec_start,'HH24:MI:SS')
3 ,sql_exec_id
4 from v$sql_monitor
5 where sql_text is not null
6* order by sql_exec_start

STATUS QUEUING_TIME SQL_ID TO_CHAR( SQL_EXEC_ID
------------------- ------------ ------------- -------- -----------
DONE (ALL ROWS) 2831511 gd9scwyt8v953 03:55:11 16777247
DONE (ALL ROWS) 2684562 814qvp0rkqug4 03:55:11 16777324
DONE (ALL ROWS) 1739474 f6jbr8wz0fv1t 03:55:12 16777216
DONE (ALL ROWS) 0 f7755257c288j 03:55:19 16777216
DONE (ALL ROWS) 0 5huqb0rhzf29q 03:55:19 16777681
DONE (ALL ROWS) 0 ayj1k1vnmp0f7 03:55:19 16777236
DONE (ALL ROWS) 0 du96rsyw3f7sz 03:55:21 16777229
DONE (ALL ROWS) 0 820qdj5r8cmrr 03:55:21 16777218
DONE (ALL ROWS) 0 6xrfbu2ts2pgk 03:55:22 16777220
DONE (ALL ROWS) 0 47s9vpct2js18 03:55:22 16777236
DONE (ALL ROWS) 46 8s85psf3jgy52 03:55:52 16777235
DONE (ALL ROWS) 3999283 64qpjd5t8rb40 03:55:52 16777221
DONE (ALL ROWS) 56 29rqwcj4cs31u 03:55:52 16777332
DONE (ALL ROWS) 3035084 gcuxbm6up4122 03:55:53 16777220
DONE (ALL ROWS) 669911 8dqsccvprfm78 03:55:56 16777217
DONE (ALL ROWS) 3506747 8s2krdc70vx18 03:55:57 16777216
DONE (ALL ROWS) 3517949 bf555m0vgxf0v 03:55:57 16777216
DONE (ALL ROWS) 42 cw42jzqxsm470 03:55:57 16777216
DONE (ALL ROWS) 3431230 av1rgj5v045d0 03:55:57 16777216
DONE (ALL ROWS) 3999450 7qqsm4251nu37 03:55:57 16777216
DONE (ALL ROWS) 3246401 2v03a3a51tdtv 03:55:58 16777216
DONE (ALL ROWS) 2701734 fkt8zyhzs9q59 03:55:58 16777216
DONE (ALL ROWS) 3248370 8w6atv1b7vyk6 03:55:58 16777216
DONE (ALL ROWS) 1105996 723j94mkj5vtk 03:56:00 16777216
DONE (ALL ROWS) 3999850 2rfpg0xh9m2j8 03:56:01 16777217
DONE (ALL ROWS) 2842527 66bx153zgukna 03:56:02 16777216
EXECUTING 2934971 4h393k5cvgyqz 03:56:02 16777217
DONE (ALL ROWS) 2934967 3msyu9tjy400d 03:56:02 16777216
EXECUTING 1816098 1xczk8apb4vpj 03:56:03 16777221
EXECUTING 1678900 8ycsk1j6bjq5p 03:56:04 16777223
EXECUTING 1657826 gbxbagzcd9xw9 03:56:04 16777221
QUEUED 3416073 chzq5gvh210th 03:56:06 16777230
QUEUED 2349656 7ukf2wrhkc5v1 03:56:07 16777216
QUEUED 1726668 6c2ma1t5gr2jy 03:56:07 16777218
QUEUED 1300262 ggp2tf8vm4tn2 03:56:08 16777230

35行が選択されました。

QUEUING_TIME(micro sec)がキューイングされている時間だ。

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

以下のパラメータを変更(半分に)してみたが、変えることはできなかった:

SQL> show parameter cpu

NAME TYPE VALUE
------------------------------------ ----------- -------
cpu_count integer 8
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 8
Ichiro Obata's picture

In-Memory Parallel Query

TPC-Hベンチマークの続き

hammeroraのスクリプトを編集して、In-Memory Parallel Queryのテストを行う:
set sql(1) "alter session set parallel_degree_policy=auto"

まずは1セッションで:

そのときのCPU使用率は:

In-Memory Parallel Queryでは、Parallel度を明示的に指定できない。
だから、「何Parallel」で動いているのかを知りたい:


SQL> alter system flush shared_pool;
set lines 250
set pages 200
col module format a20
col STIME format a8
col ETIME format a8
col PAST format a13
col PX format '99'
col CLWAIT format '99'
col APWAIT format '99'
col SORTS format '999'
col EXECS format '999'
select /*+ no_monitoring */
to_char(min(c.SQL_EXEC_START),'HH24:MI:SS') STIME
,to_char(max(c.SAMPLE_TIME),'HH24:MI:SS') ETIME
,substr(to_char(max(c.SAMPLE_TIME)-min(c.SQL_EXEC_START),'HH24:MI:SS'),11) PAST
,max(a.EXECUTIONS) EXECS
,max(a.FETCHES) FETCHES
,max(a.PX_SERVERS_EXECUTIONS) PX
,max(a.SORTS) SORTS
,max(a.ROWS_PROCESSED) ROWS_P
,round(max(a.USER_IO_WAIT_TIME)) IOWAIT
-- ,round(max(a.CLUSTER_WAIT_TIME)) CLWAIT
-- ,round(max(a.APPLICATION_WAIT_TIME)) APWAIT
,round(max(a.CONCURRENCY_WAIT_TIME)) CCWAIT
,round(max(a.ELAPSED_TIME)) ELAPSED
,round(max(a.CPU_TIME)) CPU_TIME
,max(a.BUFFER_GETS) BUF_GETS
,max(a.OPTIMIZER_COST) COST
,a.sql_id
-- ,a.PLAN_HASH_VALUE
,a.MODULE
from v$sql a, v$active_session_history C
where a.sql_id = c.sql_id
and ELAPSED_TIME > 0
and a.PARSING_SCHEMA_NAME <> 'SYS'
group by a.PLAN_HASH_VALUE, a.sql_id, a.MODULE
order by ELAPSED
/
STIME ETIME PAST EXECS FETCHES PX SORTS ROWS_P IOWAIT CCWAIT ELAPSED CPU_TIME BUF_GETS COST SQL_ID MODULE
-------- -------- ------------- ----- ---------- --- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------- --------------------
00:47:24 04:50:15 04:02:51.629 1 1 4 4 2 0 0 141260 140400 2974 291 5huqb0rhzf29q wish85t.exe
04:54:49 04:54:50 00:00:01.404 1 75 4 2 744 0 0 411164 436804 28853 2903 arz3wfaba8da7 wish85t.exe
04:54:46 04:54:47 00:00:01.394 1 1 4 2 1 0 0 1038073 1029606 65835 6747 bk9sn1kbzs7yn wish85t.exe
04:54:43 04:54:44 00:00:01.384 1 1 4 2 2 0 0 1044030 1045207 74255 7614 akt0yxaa05ggd wish85t.exe
04:54:36 04:54:37 00:00:01.364 1 1152 4 2 11514 0 0 1209427 1201208 56191 9318 6c31ta3vgcwzh wish85t.exe
04:54:39 04:54:40 00:00:01.374 1 1 4 2 2 0 4 1473278 1513208 88811 9053 cruv5v9furgq5 wish85t.exe
04:54:38 04:54:39 00:00:01.364 1 1 4 4 4 0 0 1770111 1887612 41766 4239 5bgxrmr55wvb6 wish85t.exe
04:54:37 04:54:38 00:00:01.364 1 1 4 2 5 0 0 2055977 2090414 84879 8656 ampnk72s02dtp wish85t.exe
04:54:42 04:54:43 00:00:01.384 1 3798 4 2 37980 0 7 2152165 2293216 84405 12609 gwpu7myf17bz7 wish85t.exe
00:46:21 04:54:45 04:08:24.384 1 4 4 2 36 0 0 3714914 3759624 22537 7343 3ndjyxw801gnu wish85t.exe
04:54:40 04:54:42 00:00:02.374 1 18 4 4 175 0 0 4058806 4009226 100793 11871 3w4gvgbnb2tc3 wish85t.exe
04:54:33 04:54:36 00:00:03.364 1 1 3 2 4 0 0 4708541 4711230 62300 6534 3f14rpqjvdpz4 wish85t.exe
04:54:46 04:54:49 00:00:03.394 1 1 4 2 9 0 0 4984365 5070032 146284 19339 2xjra7wm4cryf wish85t.exe
04:54:49 04:54:52 00:00:03.405 1 78 4 2 780 0 0 5720865 5740836 143681 14808 bwynwaxkzqkxj wish85t.exe

ひとつを除いてはDOP(PX)=4で動いている。DOP(PX)=3で動いているSQLはlineitemのみNO_JOINのSQLだ。

bk9sn1kbzs7yn select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#14' and p_container = 'JUMBO CAN' and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey)
arz3wfaba8da7 select s_name, s_address from supplier, nation where s_suppkey in ( select ps_suppkey from partsupp where ps_partkey in ( select p_partkey from part where p_name like 'plum%') and ps_availqty > ( select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date '1997-01-01' and l_shipdate < date '1997-01-01' + interval '1' year)) and s_nationkey = n_nationkey and n_name = 'PERU' order by s_name
2xjra7wm4cryf select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 315) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate
5bgxrmr55wvb6 select supp_nation, cust_nation, l_year, sum(volume) as revenue from ( select n1.n_name as supp_nation, n2.n_name as cust_nation, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'SAUDI ARABIA' and n2.n_name = 'MOZAMBIQUE') or (n1.n_name = 'MOZAMBIQUE' and n2.n_name = 'SAUDI ARABIA')) and l_shipdate between date '1995-01-01' and date '1996-12-31') shipping group by supp_nation, cust_nation, l_year order by supp_nation, cust_nation, l_year
6c31ta3vgcwzh select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'MACHINERY' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-14' and l_shipdate > date '1995-03-14' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate
akt0yxaa05ggd select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count from orders, lineitem where o_orderkey = l_orderkey and l_shipmode in ('REG AIR', 'AIR') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate and l_receiptdate >= date '1997-01-01' and l_receiptdate < date '1997-01-01' + interval '1' year group by l_shipmode order by l_shipmode
3ndjyxw801gnu select c_count, count(*) as custdist from ( select c_custkey, count(o_orderkey) as c_count from customer left outer join orders on c_custkey = o_custkey and o_comment not like '%unusual%requests%' group by c_custkey) c_orders group by c_count order by custdist desc, c_count desc
3f14rpqjvdpz4 select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date '1998-12-01' - interval '68' day (3) group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus
5huqb0rhzf29q select s_suppkey, s_name, s_address, s_phone, total_revenue from supplier, revenue0 where s_suppkey = supplier_no and total_revenue = ( select max(total_revenue) from revenue0) order by _suppkey
ampnk72s02dtp select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' and o_orderdate >= date '1996-01-01' and o_orderdate < date '1996-01-01' + interval '1' year group by n_name order by revenue desc
bwynwaxkzqkxj select s_name, count(*) as numwait from supplier, lineitem l1, orders, nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey) and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate) and s_nationkey = n_nationkey and n_name = 'ARGENTINA' group by s_name order by numwait desc, s_name
3w4gvgbnb2tc3 select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%olive%') profit group by nation, o_year order by nation, o_year desc
gwpu7myf17bz7 select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '1994-01-01' and o_orderdate < date '1994-01-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc
cruv5v9furgq5 select o_year, sum(case when nation = 'UNITED KINGDOM' then volume else 0 end) / sum(volume) as mkt_share from ( select extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) as
volume, n2.n_name as nation from part, supplier, lineitem, orders, customer, nation n1, nation n2, region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey and r_name = 'EUROPE' and s_nationkey = n2.n_nationkey and o_orderdate between date '1995-01-01' and date '1996-12-31' and p_type = 'SMALL ANODIZED TIN') all_nations group by o_year order by o_year

今回の結果
前回までのTPC-Hの結果と比較すると、やはりIn-Memory PQは一番速い。
でも、驚くほど速いというわけではない。
今回は、Partitioning+Compressの環境で行ったからだろうか?

最後に、
hammeroraを使う場合は、OPTIMIZER_DYNAMIC_SAMPLING=0の環境にしないと、SQL統計情報の有無に関わらず発行されるRecursive Callがカウントされてしまうので注意です。

Ichiro Obata's picture

In-Memory nonParallel + nocompressで16000qph達成

TPC-Hベンチマークの続き、

今までの結論は、

In-Memory nonParallelはTPC-Hの総合点には大きく貢献したが、実際のデータウェアハウスではParallel Queryの方が優れていることが確認できた。

もう少し、総合点を上げるためにIn-Memory nonParallelのテストを追加した。

Parallel Queryを速くするCompressオプションを外してみる。

1セッション COMPRESS:

NOCOMPRESSにすると

同時4セッションCOMPRESS:

NOCOMPRESSにすると

同時8セッションCOMPRESS:

NOCOMPRESSにすると

そして、Resut_Cacheを使った、今までの最高点:

NOCOMPRESSにすると

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

今回の結果はあくまで参考値。
大規模データウェアハウス環境ではParallel Query効率化のためのCOMPRESSは非常に有効。
In-Memory nonParallelは、TPC-Hベンチマークの点数を上げるためには都合の良いテクニックだったけど、せっかくのSSD RAID0の意味がなくなっている。

因みに、Exadata1のTPC-Hベンチマークでは、sub partition(composit key)を使っているのでCOMPRESSは使えない筈。そしてV1なので、カラムCOMPRESSも使えない。

To prevent automated spam submissions leave this field empty.
Syndicate content