« macOS : カレンダーへのスパム参加通知をなんとかする方法を考えてみた | トップページ | わすれられない言葉 - Unforgettable word »

2016年12月17日 (土)

スタースキーマを扱う実行計画の特徴

JPOUG Advent Calendar 2016の17日目のエントリです。
昨日は、id:kenken08さんのMySQLのsql_modeにあるORACLEとは - kenken0807_DBメモでした。

第三の柴田さんのネタを見て、急遽内容を変更しました。:)
SQLチューニングと対戦格闘ゲームの類似性について語る。- JPOUG Advent Calendar 2016 Day 15 - - ねら~ITエンジニア雑記


DWH系のスタースキーマを扱う実行計画の特徴を簡単にまとめておきたいと思います。(個人的には、in-memory aggregationが今年のハイライトだったのでw)

※サンプルスキーマ:SHスキーマを利用しています。
Installing Sample Schemas

まず、ハッシュ結合とBloom Filterを利用した実行計画です。面倒な準備もなく、癖も少ないので力技でなんとかする系ではよく見かける実行計画です。
Right-Deep Join + Bloom Filter
Right-Deep Joinが可能なのはHash Joinのみです。 意図的に行う場合は、LEADING/USE_HASH/SWAP_JOIN_INPUTSを利用します。
Right-Deep Join Trees and Star Schema Queries
津島博士のパフォーマンス講座 - 第46回 パーティション・プルーニングとハッシュ結合について

スタースキーマでない結合や、NLJではLeft-Deep Joinとなるのが一般的なので見慣れない実行計画だと思う方もいると思いますが、巨大なファクト表よりサイズの小さいディメンジョン表が常にハッシュ結合のビルド表(外部表)になるように結合順序が入れ替えられています。

ハッシュ結合の実行計画としては理にかなっているのですが、超巨大なファクト表との結合がある場合、Exadataをもってしても倒すことができない敵に出会うこともありますw
弱点といえば弱点ですが、方式上難しいところでもあります。
パラレル度を増加させたとしても太刀打ちできないケースもね。。。。とほほ。

Execution Plan
----------------------------------------------------------
Plan hash value: 2503647845

------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1546 | 137K| 3879 (1)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10003 | 1546 | 137K| 3879 (1)| 00:00:01 | | | Q1,03 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 1546 | 137K| 3879 (1)| 00:00:01 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 1546 | 137K| 3879 (1)| 00:00:01 | | | Q1,03 | PCWP | |
| 5 | PX SEND RANGE | :TQ10002 | 1546 | 137K| 3879 (1)| 00:00:01 | | | Q1,02 | P->P | RANGE |
| 6 | HASH GROUP BY | | 1546 | 137K| 3879 (1)| 00:00:01 | | | Q1,02 | PCWP | |
|* 7 | HASH JOIN | | 580K| 50M| 3875 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 8 | PX RECEIVE | | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
| 11 | TABLE ACCESS INMEMORY FULL | COUNTRIES | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 12 | HASH JOIN | | 580K| 35M| 3872 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 13 | PX RECEIVE | | 55500 | 541K| 8 (13)| 00:00:01 | | | Q1,02 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10001 | 55500 | 541K| 8 (13)| 00:00:01 | | | Q1,01 | P->P | BROADCAST |
| 15 | PX BLOCK ITERATOR | | 55500 | 541K| 8 (13)| 00:00:01 | | | Q1,01 | PCWC | |
| 16 | TABLE ACCESS INMEMORY FULL| CUSTOMERS | 55500 | 541K| 8 (13)| 00:00:01 | | | Q1,01 | PCWP | |
|* 17 | HASH JOIN | | 580K| 29M| 3864 (1)| 00:00:01 | | | Q1,02 | PCWP | |
|* 18 | TABLE ACCESS INMEMORY FULL | CHANNELS | 2 | 42 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 19 | HASH JOIN | | 1161K| 36M| 3862 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 20 | PART JOIN FILTER CREATE | :BF0000 | 1845 | 22140 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 21 | TABLE ACCESS INMEMORY FULL| TIMES | 1845 | 22140 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 22 | PX BLOCK ITERATOR | | 3673K| 73M| 3857 (1)| 00:00:01 |:BF0000|:BF0000| Q1,02 | PCWC | |
| 23 | TABLE ACCESS FULL | SALES | 3673K| 73M| 3857 (1)| 00:00:01 |:BF0000|:BF0000| Q1,02 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of session
- 1 Sql Plan Directive used for this statement

Star Transformation
巨大なファクト表とディメンジョン表の結合が辛いなら、昔からあるスター変換だ! 
とも思うのですが、スター変換は頑固ものかつ、曲者なのが難点。

頑固さ
ディメンジョン表やファクト表にビットマップ索引や参照整合性制約作成等、利用できるようにするお膳立てができてないと、ピクリとも動きませんw
巨大なファクト表の外部キー列にビットマップ索引を1つ作成するのに、数時間w 複数作成して、さらに、ディメンジョン表との参照整合性制約まで必要なのでまともにやっていると、1日では終わらないことも><
(俺を信じろ、 RELYが利用できるデータの状態であれば楽ではありますが

スター変換の最大の弱点は、ビットマップ索引を利用したROWIDアクセス(以下の実行計画ではId=42の部分)による読み込み件数が多すぎるケースです。性能が伸びなかったり、または、悪化することもあります。
スター変換を利用するかどうかは、ディメンジョン表との結合でファクト表が十分に絞り込めるかにかかっています。

ディメンジョン表との結合キーがビットマップ索引中にあるため、ディメンジョン表とファクト表を結合することなく、ROWIDでファクト表をアクセスして集計することができます。
ファクト表のアクセス量が少ない場合はROWIDアクセスがメリットとなるわけですが、その逆のケースでは、ファクト表はROWIDで1行ごとにアクセスされることになるため、アクセスするファクト表の行数が多くなればなるほど不利になります。

ROWIDによるシングルブロックリードが数十億回繰り返されるとしたら、待機イベントのほとんどが、db file sequential readやcell single block physical read(Exadata)になってしまうことになります。

使いどころを見誤らないようにしたいものです。

Execution Plan
----------------------------------------------------------
Plan hash value: 2513598833

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6490 | 627K| | 5584 (1)| 00:00:01 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D662C_336236 | | | | | | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS INMEMORY FULL | TIMES | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 7 | PX COORDINATOR | | | | | | | | | | | |
| 8 | PX SEND QC (ORDER) | :TQ20003 | 6490 | 627K| | 5582 (1)| 00:00:01 | | | Q2,03 | P->S | QC (ORDER) |
| 9 | SORT GROUP BY | | 6490 | 627K| 60M| 5582 (1)| 00:00:01 | | | Q2,03 | PCWP | |
| 10 | PX RECEIVE | | 6490 | 627K| | 5582 (1)| 00:00:01 | | | Q2,03 | PCWP | |
| 11 | PX SEND RANGE | :TQ20002 | 6490 | 627K| | 5582 (1)| 00:00:01 | | | Q2,02 | P->P | RANGE |
| 12 | HASH GROUP BY | | 6490 | 627K| 60M| 5582 (1)| 00:00:01 | | | Q2,02 | PCWP | |
|* 13 | HASH JOIN | | 580K| 54M| | 4334 (1)| 00:00:01 | | | Q2,02 | PCWP | |
| 14 | PX RECEIVE | | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 15 | PX SEND BROADCAST | :TQ20000 | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q2,00 | P->P | BROADCAST |
| 16 | PX BLOCK ITERATOR | | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q2,00 | PCWC | |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662C_336236 | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
|* 18 | HASH JOIN | | 580K| 48M| | 4332 (1)| 00:00:01 | | | Q2,02 | PCWP | |
|* 19 | TABLE ACCESS INMEMORY FULL | CHANNELS | 2 | 42 | | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 20 | HASH JOIN | | 580K| 36M| | 4329 (1)| 00:00:01 | | | Q2,02 | PCWP | |
| 21 | PX RECEIVE | | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,02 | PCWP | |
| 22 | PX SEND BROADCAST | :TQ20001 | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,01 | P->P | BROADCAST |
|* 23 | HASH JOIN | | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,01 | PCWP | |
| 24 | TABLE ACCESS INMEMORY FULL | COUNTRIES | 23 | 621 | | 2 (0)| 00:00:01 | | | Q2,01 | PCWP | |
| 25 | PX BLOCK ITERATOR | | 55500 | 541K| | 8 (13)| 00:00:01 | | | Q2,01 | PCWC | |
| 26 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 55500 | 541K| | 8 (13)| 00:00:01 | | | Q2,01 | PCWP | |
| 27 | VIEW | VW_ST_A44449E3 | 580K| 16M| | 4319 (1)| 00:00:01 | | | Q2,02 | PCWP | |
| 28 | NESTED LOOPS | | 580K| 28M| | 4315 (1)| 00:00:01 | | | Q2,02 | PCWP | |
| 29 | PX PARTITION RANGE SUBQUERY | | 580K| 12M| | 15 (14)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q2,02 | PCWC | |
| 30 | BITMAP CONVERSION TO ROWIDS | | 580K| 12M| | 15 (14)| 00:00:01 | | | Q2,02 | PCWP | |
| 31 | BITMAP AND | | | | | | | | | Q2,02 | PCWP | |
| 32 | BITMAP MERGE | | | | | | | | | Q2,02 | PCWP | |
| 33 | BITMAP KEY ITERATION | | | | | | | | | Q2,02 | PCWP | |
| 34 | BUFFER SORT | | | | | | | | | Q2,02 | PCWP | |
|* 35 | TABLE ACCESS INMEMORY FULL| CHANNELS | 2 | 26 | | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 36 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | | | | | |KEY(SQ)|KEY(SQ)| Q2,02 | PCWP | |
| 37 | BITMAP MERGE | | | | | | | | | Q2,02 | PCWP | |
| 38 | BITMAP KEY ITERATION | | | | | | | | | Q2,02 | PCWP | |
| 39 | BUFFER SORT | | | | | | | | | Q2,02 | PCWP | |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662C_336236 | 1845 | 14760 | | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 41 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | | | | |KEY(SQ)|KEY(SQ)| Q2,02 | PCWP | |
| 42 | TABLE ACCESS BY USER ROWID | SALES | 1 | 29 | | 4304 (1)| 00:00:01 | ROWID | ROWID | Q2,02 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of session
- star transformation used for this statement
- 1 Sql Plan Directive used for this statement

長い前置きでしたが、やっと真打の登場です!
in-memory aggregationとして解説されていることが多いのですが、実行計画を眺めている人間からすると機能名よりvector transformationの方がイメージしやすいので、以下、Vector Tranformation/ベクター変換とします。

Vector Transformation
スター変換同様に、巨大なファクト表とディメンジョン表を直接結合しない点や、パラレル実行時も他の実行計画ではみられない(誤解をおそれずにいうと、全力投球に近いかもw)特徴があります。
索引や参照整合性制約などの作成は不要。
in-memory database関連の機能ではあるのですが、全ての表がinmemory化されていなくても発動させることができます。(inmemory_sizeパラメータの設定は必要となる模様。後述)
最強の力を発揮するのは、全てがinmemoryで動作した場合であることは間違いないわけですが、巨大過ぎるファクト表がinmemory化できるほどメモリが潤沢にあるかというと、そうじゃなかっりしますし。


ベクター変換の動きを簡単に説明すると以下のような感じです。
ディメンジョン表を元に集計結果相当の構造体(in-memory accumulatorと呼ばれる多次元構造体)をメモリ上に構築後、ファクト表を読みながらin-memory accumulator上で集計します!!!(画期的!)
ハッシュ結合が全くなくなるわけではないですが、集計終了後に読み替え目的で少量(この部分が少量じゃないと辛くなるはずなのでよーく確認しておくことをおすすめします)はのハッシュ結合が行われるだけなので、冒頭で紹介した巨大なファクト表とディメンジョン表の結合によるCPUネック部分を華麗に回避していることがわかります。
Right-Deep Join+Bloom Filterで苦しい状況になったら、in-memory aggregationのことを思い出してあげてください。

助けてくれるかもしれません。



Oracle Database In-Memory: In-Memory Aggregation - Oracle White Paper JANUARY 2015


Execution Plan
----------------------------------------------------------
Plan hash value: 3211261687

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 483 | 60858 | | 3840 (1)| 00:00:01 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6630_336236 | | | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10001 | 21 | 336 | | 3 (34)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 5 | BUFFER SORT | | 21 | 336 | | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | VECTOR GROUP BY | | 21 | 336 | | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | KEY VECTOR CREATE BUFFERED | :KV0000 | 1845 | 29520 | | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS INMEMORY FULL | TIMES | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 12 | LOAD AS SELECT | SYS_TEMP_0FD9D6631_336236 | | | | | | | | | | |
| 13 | PX COORDINATOR | | | | | | | | | | | |
| 14 | PX SEND QC (RANDOM) | :TQ20001 | 23 | 851 | | 12 (25)| 00:00:01 | | | Q2,01 | P->S | QC (RAND) |
| 15 | HASH GROUP BY | | 23 | 851 | 2624K| 12 (25)| 00:00:01 | | | Q2,01 | PCWP | |
| 16 | KEY VECTOR CREATE BUFFERED | :KV0001 | | | | | | | | Q2,01 | PCWP | |
| 17 | PX RECEIVE | | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,01 | PCWP | |
| 18 | PX SEND HASH | :TQ20000 | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,00 | P->P | HASH |
|* 19 | HASH JOIN | | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,00 | PCWP | |
| 20 | TABLE ACCESS INMEMORY FULL | COUNTRIES | 23 | 621 | | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 21 | PX BLOCK ITERATOR | | 55500 | 541K| | 8 (13)| 00:00:01 | | | Q2,00 | PCWC | |
| 22 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 55500 | 541K| | 8 (13)| 00:00:01 | | | Q2,00 | PCWP | |
| 23 | LOAD AS SELECT | SYS_TEMP_0FD9D6632_336236 | | | | | | | | | | |
| 24 | PX COORDINATOR | | | | | | | | | | | |
| 25 | PX SEND QC (RANDOM) | :TQ30001 | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | P->S | QC (RAND) |
| 26 | BUFFER SORT | | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 27 | VECTOR GROUP BY | | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 28 | KEY VECTOR CREATE BUFFERED | :KV0002 | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 29 | PX RECEIVE | | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,01 | PCWP | |
| 30 | PX SEND HASH | :TQ30000 | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | P->P | HASH |
| 31 | PX BLOCK ITERATOR | | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | PCWC | |
|* 32 | TABLE ACCESS INMEMORY FULL | CHANNELS | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | PCWP | |
| 33 | PX COORDINATOR | | | | | | | | | | | |
| 34 | PX SEND QC (ORDER) | :TQ40003 | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | P->S | QC (ORDER) |
| 35 | SORT GROUP BY | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | PCWP | |
| 36 | PX RECEIVE | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | PCWP | |
| 37 | PX SEND RANGE | :TQ40002 | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,02 | P->P | RANGE |
| 38 | HASH GROUP BY | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,02 | PCWP | |
|* 39 | HASH JOIN | | 483 | 60858 | | 3820 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 40 | PX RECEIVE | | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
| 41 | PX SEND BROADCAST | :TQ40000 | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | P->P | BROADCAST |
| 42 | PX BLOCK ITERATOR | | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | PCWC | |
| 43 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6631_336236 | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | PCWP | |
|* 44 | HASH JOIN | | 483 | 42987 | | 3818 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 45 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6630_336236 | 21 | 252 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
|* 46 | HASH JOIN | | 483 | 37191 | | 3816 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 47 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6632_336236 | 2 | 42 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
| 48 | VIEW | VW_VT_AF0F4755 | 483 | 27048 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 49 | HASH GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 50 | PX RECEIVE | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 51 | PX SEND HASH | :TQ40001 | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | P->P | HASH |
| 52 | VECTOR GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | PCWP | |
| 53 | HASH GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | PCWP | |
| 54 | KEY VECTOR USE | :KV0001 | 580K| 18M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 55 | KEY VECTOR USE | :KV0002 | 580K| 16M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 56 | KEY VECTOR USE | :KV0000 | 1161K| 27M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 57 | PX BLOCK ITERATOR | | 3673K| 73M| | 3811 (1)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q4,01 | PCWC | |
|* 58 | TABLE ACCESS FULL| SALES | 3673K| 73M| | 3811 (1)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q4,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of session
- 2 Sql Plan Directives used for this statement
- vector transformation used for this statement

超絶必殺技にも思えるベクター変換ですが、癖がないわけではありません。(大技につきものの反動というか、なんというかw)

癖 その1)
パラレルクエリー時に割り当てられるサーバープロセスが多く、割り当てられるサーバー数は、KEY VECTORの作成数により大きく変化します。
以下、SQL MONITORのParallel Execution Detailsセクション(抜粋)の比較

並列度は同じでも割り当てるサーバー数はこんなに違う!
VECTOR TRANSFORMATION
KEY VECTORが3つ作成されるベクター変換の場合
(SQL MONITORのParallel Execution Detailsセクションより抜粋)
Parallel Execution Details (DOP=4 , Servers Allocated=32)

KEY VECTORが2つ作成されるベクター変換の場合
Parallel Execution Details (DOP=4 , Servers Allocated=24)

STAR TRANSFORMATION
Parallel Execution Details (DOP=4 , Servers Allocated=12)

Hash Joinのみ
Parallel Execution Details (DOP=4 , Servers Allocated=8)


癖 その2)
スター変換を発動させるための索引作成や、制約作成の煩雑さは無く、全ての表がinmemoryになっていなくても発動させることはできるのですが、発動させるためは、最低限設定しなればならない(ほんと? 不具合?)パラメータが存在します。(

inmemory化する表は無くとも、inmemory_size=100m(設定可能な最小サイズ)に設定しないとVECTOR_TRANSFORMヒントが無視されるという点です。
この制限?を記載しているマニュアルなどは探し出せていないのですが、どこかに記載されているのでしょうか?(いまのところ見つけることができず。。。教えていただけるとうれしいです)


では、最後に、inmemory_size初期化パラメータの設定有無による変化をみてみましょう。

inmemory_size初期化パラメータが設定されている場合にはVECTOR_TRANSFORMヒントでベクター変換を強制できています。

10:36:23 orcl12c@SYSTEM> show parameter inmemory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 3
inmemory_query string ENABLE
inmemory_size big integer 512M
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE


10:37:04 ORCL@SH> set autot trace exp stat
10:56:18 ORCL@SH> @sample3_2

135 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3211261687

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 483 | 60858 | | 3967 (1)| 00:00:01 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6609_33B383 | | | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10001 | 21 | 336 | | 20 (5)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 5 | BUFFER SORT | | 21 | 336 | | 20 (5)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | VECTOR GROUP BY | | 21 | 336 | | 20 (5)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | KEY VECTOR CREATE BUFFERED | :KV0000 | 1845 | 29520 | | 20 (5)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | 1845 | 22140 | | 19 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 | 1845 | 22140 | | 19 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 1845 | 22140 | | 19 (0)| 00:00:01 | | | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS FULL | TIMES | 1845 | 22140 | | 19 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 12 | LOAD AS SELECT | SYS_TEMP_0FD9D660A_33B383 | | | | | | | | | | |
| 13 | PX COORDINATOR | | | | | | | | | | | |
| 14 | PX SEND QC (RANDOM) | :TQ20001 | 23 | 851 | | 122 (3)| 00:00:01 | | | Q2,01 | P->S | QC (RAND) |
| 15 | HASH GROUP BY | | 23 | 851 | 2624K| 122 (3)| 00:00:01 | | | Q2,01 | PCWP | |
| 16 | KEY VECTOR CREATE BUFFERED | :KV0001 | | | | | | | | Q2,01 | PCWP | |
| 17 | PX RECEIVE | | 55500 | 2005K| | 119 (0)| 00:00:01 | | | Q2,01 | PCWP | |
| 18 | PX SEND HASH | :TQ20000 | 55500 | 2005K| | 119 (0)| 00:00:01 | | | Q2,00 | P->P | HASH |
|* 19 | HASH JOIN | | 55500 | 2005K| | 119 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 20 | TABLE ACCESS FULL | COUNTRIES | 23 | 621 | | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 21 | PX BLOCK ITERATOR | | 55500 | 541K| | 117 (0)| 00:00:01 | | | Q2,00 | PCWC | |
| 22 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| | 117 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 23 | LOAD AS SELECT | SYS_TEMP_0FD9D660B_33B383 | | | | | | | | | | |
| 24 | PX COORDINATOR | | | | | | | | | | | |
| 25 | PX SEND QC (RANDOM) | :TQ30001 | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | P->S | QC (RAND) |
| 26 | BUFFER SORT | | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 27 | VECTOR GROUP BY | | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 28 | KEY VECTOR CREATE BUFFERED | :KV0002 | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 29 | PX RECEIVE | | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,01 | PCWP | |
| 30 | PX SEND HASH | :TQ30000 | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | P->P | HASH |
| 31 | PX BLOCK ITERATOR | | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | PCWC | |
|* 32 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | PCWP | |
| 33 | PX COORDINATOR | | | | | | | | | | | |
| 34 | PX SEND QC (ORDER) | :TQ40003 | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | P->S | QC (ORDER) |
| 35 | SORT GROUP BY | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | PCWP | |
| 36 | PX RECEIVE | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | PCWP | |
| 37 | PX SEND RANGE | :TQ40002 | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,02 | P->P | RANGE |
| 38 | HASH GROUP BY | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,02 | PCWP | |
|* 39 | HASH JOIN | | 483 | 60858 | | 3820 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 40 | PX RECEIVE | | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
| 41 | PX SEND BROADCAST | :TQ40000 | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | P->P | BROADCAST |
| 42 | PX BLOCK ITERATOR | | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | PCWC | |
| 43 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_33B383 | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | PCWP | |
|* 44 | HASH JOIN | | 483 | 42987 | | 3818 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 45 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_33B383 | 21 | 252 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
|* 46 | HASH JOIN | | 483 | 37191 | | 3816 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 47 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_33B383 | 2 | 42 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
| 48 | VIEW | VW_VT_AF0F4755 | 483 | 27048 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 49 | HASH GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 50 | PX RECEIVE | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 51 | PX SEND HASH | :TQ40001 | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | P->P | HASH |
| 52 | VECTOR GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | PCWP | |
| 53 | HASH GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | PCWP | |
| 54 | KEY VECTOR USE | :KV0001 | 580K| 18M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 55 | KEY VECTOR USE | :KV0002 | 580K| 16M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 56 | KEY VECTOR USE | :KV0000 | 1161K| 27M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 57 | PX BLOCK ITERATOR | | 3673K| 73M| | 3811 (1)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q4,01 | PCWC | |
|* 58 | TABLE ACCESS FULL| SALES | 3673K| 73M| | 3811 (1)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q4,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

11 - filter("T"."FISCAL_YEAR"=2000 OR "T"."FISCAL_YEAR"=2005 OR "T"."FISCAL_YEAR"=2010 OR "T"."FISCAL_YEAR"=2015 OR "T"."FISCAL_YEAR"=2016)
19 - access("C"."COUNTRY_ID"="R"."COUNTRY_ID")
32 - filter("CH"."CHANNEL_DESC"='Internet' OR "CH"."CHANNEL_DESC"='Partners')
39 - access("ITEM_13"=INTERNAL_FUNCTION("C0") AND "ITEM_14"="C4")
44 - access("ITEM_17"=INTERNAL_FUNCTION("C0") AND "ITEM_18"="C2")
46 - access("ITEM_15"=INTERNAL_FUNCTION("C0") AND "ITEM_16"="C2")
58 - filter(SYS_OP_KEY_VECTOR_FILTER("S"."TIME_ID",:KV0000) AND SYS_OP_KEY_VECTOR_FILTER("S"."CHANNEL_ID",:KV0002))

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of session
- 2 Sql Plan Directives used for this statement
- vector transformation used for this statement

inmemory_size初期化パラメータを0にし、同一SQL文を実行すると。。。。なんということでしょう。ベクター変換は発動しません!

10:46:22 orcl12c@SYSTEM> show parameter inmemory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE


10:47:56 ORCL@SH> @sample3_2

135 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2503647845

--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1546 | 137K| 4006 (1)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10003 | 1546 | 137K| 4006 (1)| 00:00:01 | | | Q1,03 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 1546 | 137K| 4006 (1)| 00:00:01 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 1546 | 137K| 4006 (1)| 00:00:01 | | | Q1,03 | PCWP | |
| 5 | PX SEND RANGE | :TQ10002 | 1546 | 137K| 4006 (1)| 00:00:01 | | | Q1,02 | P->P | RANGE |
| 6 | HASH GROUP BY | | 1546 | 137K| 4006 (1)| 00:00:01 | | | Q1,02 | PCWP | |
|* 7 | HASH JOIN | | 580K| 50M| 4002 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 8 | PX RECEIVE | | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL | COUNTRIES | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 12 | HASH JOIN | | 580K| 35M| 3999 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 13 | PX RECEIVE | | 55500 | 541K| 117 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10001 | 55500 | 541K| 117 (0)| 00:00:01 | | | Q1,01 | P->P | BROADCAST |
| 15 | PX BLOCK ITERATOR | | 55500 | 541K| 117 (0)| 00:00:01 | | | Q1,01 | PCWC | |
| 16 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| 117 (0)| 00:00:01 | | | Q1,01 | PCWP | |
|* 17 | HASH JOIN | | 580K| 29M| 3882 (1)| 00:00:01 | | | Q1,02 | PCWP | |
|* 18 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 19 | HASH JOIN | | 1161K| 36M| 3879 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 20 | PART JOIN FILTER CREATE| :BF0000 | 1845 | 22140 | 19 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 21 | TABLE ACCESS FULL | TIMES | 1845 | 22140 | 19 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 22 | PX BLOCK ITERATOR | | 3673K| 73M| 3857 (1)| 00:00:01 |:BF0000|:BF0000| Q1,02 | PCWC | |
| 23 | TABLE ACCESS FULL | SALES | 3673K| 73M| 3857 (1)| 00:00:01 |:BF0000|:BF0000| Q1,02 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------

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

7 - access("C"."COUNTRY_ID"="R"."COUNTRY_ID")
12 - access("S"."CUST_ID"="C"."CUST_ID")
17 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
18 - filter("CH"."CHANNEL_DESC"='Internet' OR "CH"."CHANNEL_DESC"='Partners')
19 - access("S"."TIME_ID"="T"."TIME_ID")
21 - filter("T"."FISCAL_YEAR"=2000 OR "T"."FISCAL_YEAR"=2005 OR "T"."FISCAL_YEAR"=2010 OR "T"."FISCAL_YEAR"=2015 OR
"T"."FISCAL_YEAR"=2016)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of session
- 1 Sql Plan Directive used for this statement

参考
Database Virtual Box Appliance / Virtual Machine
Installing Sample Schemas
Getting started with Oracle Database In-Memory Part V - Aggregation
津島博士のパフォーマンス講座 - 第54回 Oracle Database In-Memoryについて(2)

利用したSQL
sample1_2.sql
Right-Deep Join (Right-Deep Treeにならない場合には SWAP_JOIN_INPUTSで制御する必要がありますが、SHスキーマでオプティマイザ統計が取得されているのであれば不要.今回の例ではSWAP_JOIN_INPUTSは利用していませんが、オプティマイザは判断を誤るようであれば利用したほうがよいと思います。)

SELECT	
/*+
MONITOR
LEADING(r sum)
USE_HASH(r sum)
*/
sum.fiscal_year
, r.country_name
, sum.channel_class
, sum.sales_amount
FROM
(
SELECT
t.fiscal_year
, c.country_id
, ch.channel_class
, SUM(s.amount_sold) sales_amount
FROM
sales s
, times t
, customers c
, channels ch
WHERE
s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc in ('Internet','Partners')
AND t.fiscal_year IN (
2000, 2005, 2010, 2015, 2016
)
GROUP BY
ch.channel_class
, c.country_id
, t.fiscal_year
) sum
, countries r
WHERE
sum.country_id = r.country_id
ORDER BY
sum.fiscal_year
, r.country_name
, sum.channel_class
/

sample2_2.sql
スター変換ヒントが必要です。スター変換はデフォルトでOFFに設定されています。
SHスキーマはスタースキーマかつ、スター変換をすぐに試せる環境(ファクト表の外部キーのビットマップ索引やデョメンジョン表への参照整合性制約等)になっています。

SELECT	
/*+
MONITOR
LEADING(r sum)
USE_HASH(r sum)
*/
sum.fiscal_year
, r.country_name
, sum.channel_class
, sum.sales_amount
FROM
(
SELECT
/*+
STAR_TRANSFORMATION
*/
t.fiscal_year
, c.country_id
, ch.channel_class
, SUM(s.amount_sold) sales_amount
FROM
sales s
, times t
, customers c
, channels ch
WHERE
s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc in ('Internet','Partners')
AND t.fiscal_year IN (
2000, 2005, 2010, 2015, 2016
)
GROUP BY
ch.channel_class
, c.country_id
, t.fiscal_year
) sum
, countries r
WHERE
sum.country_id = r.country_id
ORDER BY
sum.fiscal_year
, r.country_name
, sum.channel_class
/

sample3_2.sql
ベクター変換の例です。ベクター変換のヒントは数種類(ファクト表を記述場合、ディメンジョン表を記述場合)あります。(詳細はv$sql_hintを参照のこと)

SELECT	
/*+
MONITOR
LEADING(r sum)
USE_HASH(r sum)
*/
sum.fiscal_year
, r.country_name
, sum.channel_class
, sum.sales_amount
FROM
(
SELECT
/*+
VECTOR_TRANSFORM
*/
t.fiscal_year
, c.country_id
, ch.channel_class
, SUM(s.amount_sold) sales_amount
FROM
sales s
, times t
, customers c
, channels ch
WHERE
s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc in ('Internet','Partners')
AND t.fiscal_year IN (
2000, 2005, 2010, 2015, 2016
)
GROUP BY
ch.channel_class
, c.country_id
, t.fiscal_year
) sum
, countries r
WHERE
sum.country_id = r.country_id
ORDER BY
sum.fiscal_year
, r.country_name
, sum.channel_class
/


明日は、@yoshikawさんです! お楽しみに!


俺のターンおわたー!:)

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/64637016

この記事へのトラックバック一覧です: スタースキーマを扱う実行計画の特徴:

コメント

コメントを書く