2017年10月 6日 (金)

DBMS_SQLTUNE.PACK_STGTAB_SQLSETって、例外投げんのかよw

ということで、タイトル通り
マニュアル上、例外を投げるとは記載されてないのですが、テストしてたら”例外投げる”PL/SQLプロシージャ、意外と多いんですw
意図的に例外投げるよーというのは大抵マニュアルに記載されているんですが。人が書いてますからね、記載漏れも仕方ないっすねぇw 

マニュアルバグ、忘れちゃうので、自分向けFAQ and 備忘録 


Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1 (12.1) B71281-05
DBMS_SQLTUNE.PACK_STGTAB_SQLSETプロシージャ

Oracle Database PL/SQL Packages and Types Reference (12.2)のマニュアルには DBMS_SQLTUNE.PACK_STGTAB_SQLSET Procedure は例外を投げるよ!という記載はないが...

DBMS_SQLTUNE.PACK_STGTAB_SQLSET Procedure
DBMS_SQLTUNE.PACK_STGTAB_SQLSET Procedure

orcl@SYS> r
1 SELECT
2 ID
3 ,NAME
4 ,OWNER
5 ,CREATED
6 ,LAST_MODIFIED
7 ,STATEMENT_COUNT
8 FROM
9 dba_sqlset
10 ORDER BY
11 OWNER
12 ,CREATED
13*


ID NAME OWNER CREATED LAST_MODI STATEMENT_COUNT
---------- ------------------------------ ------------------------------ --------- --------- ---------------
1 STS201710052340431 SYS 05-OCT-17 05-OCT-17 0
2 STS201710061431151 SYS 06-OCT-17 06-OCT-17 2

statement_count=0と2の2つのSTS(SQL Tuning Set)があります。
STS(SQL Tuning Set)については、ここでは記載しませんが、何それ? 気になる、気になる〜という方は、
Oracle Database の STS(SQL Tuning Set) を活用して、SQL の 性能統計 や 実行計画 を キャプチャする。を覗いて見てください。

(次回のネタは思いっきりそこなので、予習にもなりますよ :)

例外を投げるとはマニュアルに記載されてないプロシージャが実は例外投げるじゃん! 
というケースは、dbms_sqltune.pack_stgtab_sqlset以外にもあるんですが、
急ぎのやっつけ仕事で遭遇すると、ここで見つかってよかった!!
という安堵感とともに、
ムカ〜〜〜〜っという憎悪も湧いてくるわけですw 
(何れにしてもテストはしっかりやりましょうw

以下、例外投げるとは書かれてないけど、投げるじゃん、dbms_sqltune.pack_stgtab_sqlset の簡易テストの記録

準備として、dbms_sqltune.pack_stgtab_sqlsetは、STSを退避する為に必要なステージング表を作成します。

orcl@SYS> exec dbms_sqltune.create_stgtab_sqlset(table_name=>'TEST',schema_name=>'STSUSR');

PL/SQL procedure successfully completed.

statement_count=0つまり、STSに記録されたSQLの情報がない場合です。
ワーニングとしての意味が強いと思われますが、ORA-15701が投げつけられて、STSが空だということを教えてくれます! :)
マニュアルには記載されてなかったので焦りますw 実際のところざっくり書いたコードではこの例外をハンドリングなんてしてませんでした。

orcl@SYS> l
1 begin
2 dbms_sqltune.pack_stgtab_sqlset(
3 sqlset_name=>'STS201710052340431'
4 ,sqlset_owner=>null
5 ,staging_table_name=>'TEST'
6 ,staging_schema_owner=>'STSUSR'
7 );
8* end;
orcl@SYS> /
begin
*
ERROR at line 1:
ORA-15701: All "SQL Tuning Set(s)" with name like "STS201710052340431" and owner like "SYS" are empty
ORA-06512: at "SYS.DBMS_SQLTUNE", line 5398
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7928
ORA-06512: at line 2


一方、statement_count=2でSTSに記録されたSQLがある場合、つまり、STSは空じゃない場合は正常終了します。

orcl@SYS> l
1 begin
2 dbms_sqltune.pack_stgtab_sqlset(
3 sqlset_name=>'STS201710061431151'
4 ,sqlset_owner=>null
5 ,staging_table_name=>'TEST'
6 ,staging_schema_owner=>'STSUSR'
7 );
8* end;
orcl@SYS> /

PL/SQL procedure successfully completed.

STSが空だという例外を拾って、後続処理にあるであろう、ステージング表のエクスポートとかをバイパスしたりすることはできますね:)

dbms_sqltune.pack_stgtab_sqlsetは、STSが空だと例外投げるよ というお話でした。


To be continued...

| | コメント (0) | トラックバック (0)

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さんです! お楽しみに!


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

| | コメント (0) | トラックバック (0)

2016年7月19日 (火)

DB Tech Showcase 2016 Tokyo - E35 - SQLチューニング総合診療所的予防医学のセッション資料

DB Tech Showcase 2016 Tokyo - E35 - SQLチューニング総合診療所的予防医学のセッション資料を公開しました。

ぼくとつと、性能試験データの質などのことを言い続ける感じになっていたでしょうか?w

ところで、次回のJPOUG主催のイベントの開催が決まりました。セッション内容は現在準備中ですが、確定次第随時更新します。
お申し込みは以下のイベントベージよりお願いします。

JPOUG in 15 minutes #1

| | コメント (0) | トラックバック (0)

2016年3月27日 (日)

OracleのB*Tree索引にはNULLが含まれる場合があるんです! - その性質を使ってチューニングすることもあるよ:) その3


前回までのMac De Oracle

OracleのB*Tree索引にはNULLが含まれる場合があるんです! - その性質を使ってチューニングすることもあるよ:)
OracleのB*Tree索引にはNULLが含まれる場合があるんです! - その性質を使ってチューニングすることもあるよ:) その2

ということで、続きで〜〜すっ。

こんな表定義で

orcl@SCOTT> desc tab01
Name Null? Type
--------- -------- --------
FOO NUMBER
BAR NUMBER
HOGE NOT NULL CHAR(2)
ID NOT NULL NUMBER

こんな索引があって

****** Index column info : tab01 ******

INDEX_NAME COLUMN_NAME DESC
------------------------------ ------------------------------ ----
IX1_TAB01 FOO ASC

IX2_TAB01 BAR ASC
FOO ASC

IX3_TAB01 ID ASC
FOO ASC

IX4_TAB01 ID ASC
BAR ASC
FOO ASC

PK_TAB01 ID ASC


こんなデータで

orcl@SCOTT> set null [NULL]
orcl@SCOTT> select * from tab01

FOO BAR HO ID
---------- ---------- -- ----------
[NULL] [NULL] ** 1
1 [NULL] ** 2
[NULL] 1 ** 3
1 1 ** 4


2列の複合索引、どちらの列もNULLだと、やはり、NULLは索引に含まれないので IS NULL検索だと索引は利用されないですよねぇ〜。このような状態ではヒントで索引利用を強制利用させようとしても無理です。

orcl@SCOTT> r
1 select
2 /*+
3 gather_plan_statistics
4 index(tab01 ix2_tab01)
5 no_index(tab01 ix4_tab01)
6 no_index(tab01 ix3_tab01)
7 */
8 *
9 from
10 tab01
11 where
12 foo is null
13* and bar is null
     
FOO BAR HO ID
---------- ---------- -- ----------
[NULL] [NULL] ** 1

・・・略・・・
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| TAB01 | 1 | 1 | 1 |00:00:00.01 | 8 |
-------------------------------------------------------------------------------------

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

1 - filter(("FOO" IS NULL AND "BAR" IS NULL))

でも、NOT NUL制約の列が1列でも含まれている索引であればNULLは索引に含まれます。(前回までの復習も兼ねた確認)
第1列がWHERE句で記述されていないので索引スキップスキャンになっていますが.....IS NULL検索を索引アクセスだけで行っているのがよく分かる例の一つです:)

orcl@SCOTT> r
1 select
2 /*+
3 gather_plan_statistics
4 */
5 *
6 from
7 tab01
8 where
9 foo is null
10* and bar is null

FOO BAR HO ID
---------- ---------- -- ----------
[NULL] [NULL] ** 1

・・・略・・・
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB01 | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
|* 2 | INDEX SKIP SCAN | IX4_TAB01 | 1 | 1 | 1 |00:00:00.01 | 2 | 1 |
--------------------------------------------------------------------------------------------------------------------

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

2 - access("BAR" IS NULL AND "FOO" IS NULL)
filter(("FOO" IS NULL AND "BAR" IS NULL))


最後にもう少しわかりやすい例を。

SQL文を少々書き換えてIndex Only Scanになるようにしました。索引にNULLが含まれていないと索引だけのアクセスで済むわけがないわけで、これ以上わかりやすい例はないと思います:)

まず、索引が利用できない例から。
FOO列とBAR列だけの複合索引をヒントで強制利用させようとしていますが、この索引は2列ともnullableなので2列をIS NULL検索しても索引が利用されません!
全ての列がNULLである場合、キーエントリーは索引に作成されない。単一列でも複合索引でも同じであることが確認できます。

orcl@SCOTT> r
1 select
2 /*+
3 gather_plan_statistics
4 index(tab01 ix2_tab01)
5 no_index(tab01 ix4_tab01)
6 no_index(tab01 ix3_tab01)
7 */
8 *
9 from
10 tab01
11 where
12 foo is null
13* and bar is null

FOO BAR HO ID
---------- ---------- -- ----------
[NULL] [NULL] ** 1

・・・略・・・
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| TAB01 | 1 | 1 | 1 |00:00:00.01 | 8 |
-------------------------------------------------------------------------------------

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

1 - filter(("FOO" IS NULL AND "BAR" IS NULL))

おっと、
大切なのを忘れてました。

FOO列(nullable(、BAR列(nullable)の複合索引を IS NOT NULL AND IS NULLで検索した場合はどうなるか?
答えは以下の通り。IS NULL と IS NOT NULLの組み合わせでも、索引が利用されます。:)

BAR IS NULLで範囲検索しFOO IS NOT NULLでフィルタリングしています。 NULLが含まれていないと不可能な索引レンジスキャンと索引読み時のフィルタリング!

orcl@SCOTT> r
1 select
2 /*+
3 gather_plan_statistics
4 no_index(tab01 ix3_tab01)
5 no_index(tab01 ix4_tab01)
6 */
7 foo
8 , bar
9 from
10 tab01
11 where
12 foo is not null
13* and bar is null

FOO BAR
---------- ----------
1 [NULL]

・・・略・・・
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 1 | 1 |
|* 1 | INDEX RANGE SCAN| IX2_TAB01 | 1 | 1 | 1 |00:00:00.02 | 1 | 1 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("BAR" IS NULL)
filter("FOO" IS NOT NULL)

その逆も!

orcl@SCOTT> r
1 select
2 /*+
3 gather_plan_statistics
4 no_index(tab01 ix3_tab01)
5 no_index(tab01 ix4_tab01)
6 */
7 foo
8 , bar
9 from
10 tab01
11 where
12 foo is null
13* and bar is not null

FOO BAR
---------- ----------
[NULL] 1

・・・略・・・
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
|* 1 | INDEX SKIP SCAN | IX2_TAB01 | 1 | 1 | 1 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------

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

1 - access("FOO" IS NULL)
filter(("FOO" IS NULL AND "BAR" IS NOT NULL))


もういっちょ!
2列とものnullableな索引だとindex fast full scanにはできないけど、index full scanにはできるんですよ〜。

orcl@SCOTT7gt; r
1 select
2 /*+
3 gather_plan_statistics
4 no_index(tab01 ix3_tab01)
5 no_index(tab01 ix4_tab01)
6 */
7 foo
8 , bar
9 from
10 tab01
11 where
12 foo is not null
13* and bar is not null

FOO BAR
---------- ----------
1 1

・・・略・・・
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
|* 1 | INDEX FULL SCAN | IX2_TAB01 | 1 | 1 | 1 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------

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

1 - filter(("FOO" IS NOT NULL AND "BAR" IS NOT NULL))

Note
-----
- statistics feedback used for this statement

後に、NOT NULL列が含まれる索引なら index fast full scanでもできるはず!
独り言;index only scan+index fast full scanなんてのもセグメントサイズが小さければ物理読み込み量削減には効果があるんですよねぇ〜 ;-)

orcl@SCOTT> r
1 select
2 /*+
3 gather_plan_statistics
4 no_index(tab01 ix3_tab01)
5 index_ffs(tab01 ix4_tab01)
6 no_index(tab01 ix2_tab01)
7 */
8 foo
9 , bar
10 from
11 tab01
12 where
13 foo is not null
14* and bar is not null

FOO BAR
---------- ----------
1 1

・・・略・・・
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 4 | 1 |
|* 1 | INDEX FAST FULL SCAN| IX4_TAB01 | 1 | 1 | 1 |00:00:00.03 | 4 | 1 |
-----------------------------------------------------------------------------------------------------

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

1 - filter(("FOO" IS NOT NULL AND "BAR" IS NOT NULL))


オラクルの索引にNULLは絶対含まれない、というのは都市伝説! 
IS NOT NULLは索引使えないとかIS NULLは索引使えないというというのも違うんですよね。 

OracleのB*Tree索引では、索引に含まれる全列がNULLの場合以外はNULLが含まれてまっす! というのが正しいですよね!?

この手の問題でピンチになったら、思い出してみてください ;)
USE THE INDEX ONLY SCAN, LUKE! w

| | コメント (0) | トラックバック (0)

2015年12月30日 (水)

Pipelined table function で ascii art

クリスマスも過ぎ、新年へのカウントダウンも始まっていますが、
Pipelined table functionを使って簡単なASCIIアートをJPOUG Advent Calendar 2012描画してましたが中身を公開してなかったな〜
ということで、少々手直しして公開!

公開しておくとPipelined table functionとかtable functionを書かなきゃなった時に思い出しやすいのでw 自分向けなんですけどね。 (^^;;


参考:
Oracle Database Online Documentation 12c Release 1 (12.1)
Unstructured Data and Content Management
13 Using Pipelined and Parallel Table Functions



create or replace type messageTblType as table of varchar2(300)
/


注)
以下関数ではdbms_lock.sleep()を利用しているため、事前にexecute権限を付与しておく必要があります。
また、エラー処理は端折ってます. (^^;;
 

create or replace function tree(lheight pls_integer, linterval_change_color float)
return messageTblType
pipelined
as
height_of_tree pls_integer := lheight;
center pls_integer;
i pls_integer := 1;
siz_of_trunk pls_integer := 3;
pos_of_trunk pls_integer;
interval_change_color float := linterval_change_color;
begin
center := ceil((1 + height_of_tree + ( height_of_tree - 2)) / 2);
pos_of_trunk := case when ceil(height_of_tree / 5) > 10 then 10 else ceil(height_of_tree / 5) end;
for l in 1..75 loop
pipe row(chr(27)||'[2J'||chr(27)||'[1;'||to_char(ceil(dbms_random.value(1,7))+30)||'m');
i := 1;
--draw leaves
while (i < height_of_tree) loop
pipe row(
chr(27)||'['||to_char(i,'FM0999')||';'||to_char(center - i,'FM0999')||'H'||lpad('*', i + (i - 2) + 1, '*')
);
i := i + 1;
end loop;
--draw trunk
for j in -1..pos_of_trunk loop
pipe row(
chr(27)||'['||to_char(j + height_of_tree,'FM0999')||';'||to_char(center - 2,'FM0999')||'H'||lpad('*', siz_of_trunk, '*')
);
end loop;
dbms_lock.sleep(interval_change_color);
end loop;
pipe row(chr(27)||'[0m');
return;
end;
/
show errors


以下、実行コードの例
Treeのサイズを10/20/50、色の変更感覚を0.2秒として実行してます。


set linesize 300
set pagesize 0
set array 1
select * from table(tree(10, 0.2));
select * from table(tree(20, 0.2));
select * from table(tree(50, 0.2));

実行結果はYoutubeで:)


| | コメント (0) | トラックバック (0)

2014年11月19日 (水)

SQLチューニング総合診療所 ケースファイルX / db tech showcase tokyo 2014

恒例となってきたデータベースのお祭り db tech showcase tokyo

今年も、JPOUGのSQLチューニング総合診療医w として治療話をしてきました :)

20141119_222101


貴重な機会を提供いただいたインサイトテクノロジーの皆様、そして、お忙しい中、セッションに参加してくださった皆様、ありがとうございました。

当日は気合で乗り切ったものの翌日は力尽きて発熱で資料アップが遅れていましたが
L35 「SQLチューニング総合診療所 ケースファイルX」の資料をslideshareに公開しました。

来年のお祭りを楽しみにしています:)


あ、そうそう、
Craig Shallahamer さんのセッションを聴講したときに感じたのですが、「本人が楽しそうに話す」の重要!!
ですね。 :)


2013年以前のセッション資料は以下から。
- db tech showcase tokyo 2013 - A35 - JPOUG特濃:潮溜まりでジャブジャブ、SQLチューニング Tweet
- Unconference at db tech showcase 2012の資料公開 :)

| | コメント (0) | トラックバック (0)