« 実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 5 / No.40 / PIVOT and UNPIVOT | トップページ | 実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 7 / No.42 / INDEX RANGE SCAN (MULTI VALUE) »

2022年12月 6日 (火) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 6 / No.41 / In-Memory Vectorized Join

Previously on Mac De Oracle...
Day 5は, 実行計画というレントゲンにも現れない PIVOT の謎。みたいなオチですが、UNPIVOTは現れるんだよねー。ということでした。

今日は、さらに、このタイトルでいいのか! という結末になっております (乞うご期待w

ということで、 Day 6の窓を開けましょう。

今回、データを準備するのは、面倒だな、と思ったので、 サンプルスキーマである SH スキーマの sales表とcustomer表を利用します。ただ、少しだけ準備は必要ですが.
(なお、INMEMORY_DEEP_VECTORIZATION パラメータのデフォルト値は、TRUEなので, INMEMORY_SIZE パラメータが設定されているのであれば、追加の設定は不要です)

SH@orclpdb1> show parameter INMEMORY_DEEP_VECTORIZATION

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
inmemory_deep_vectorization boolean TRUE


では、準備を。
各表をIn-Memory化してポピュレーションする必要があります。v$im_segmentsビューからすべて載ったことを確認します。

SH@orclpdb1> alter table customers inmemory priority high memcompress for capacity low;

表が変更されました。

SH@orclpdb1> alter table sales inmemory priority high memcompress for capacity low;

表が変更されました。

SH@orclpdb1> select /*+ full(s) no_parallel(s) */ count(*) from sales s;

count(*)
----------
918843

SH@orclpdb1> select /*+ full(c) no_parallel(c) */ count(*) from customers c;

count(*)
----------
55500

SH@orclpdb1> select segment_name,inmemory_size,bytes_not_populated from v$im_segments;

SEGMENT_NAME INMEMORY_SIZE BYTES_NOT_POPULATED
------------------------------ ------------- -------------------
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
DAY4 15990784 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
CUSTOMERS 2359296 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0

18行が選択されました。

実行計画という名のレントゲンがテーマのシリーズですがw 実行計画を見ただけでは、はっきりわからないというPIVOTに続き、2つ目の事例が!!!!!
実行時に実施するかどうかを判断しているとのことで、実行計画を立てている時点では判断できず、実行後または、実行中にのみ確認できるということらしい(なんとかならんのか。ん〜〜〜〜)

ということで、SQLモニターアクティブレポート(html)という名の内視鏡を利用して診る必要のある事例であることがわかりました!!!!!!!
(各表はin-memory fullでアクセスされてます。前提はクリアしているHASH JOINになっています)

SH@orclpdb1> @day6

1 SELECT
2 /*+
3 NO_STAR_TRANSFORMATION
4 NO_VECTOR_TRANSFORM
5 FULL(c)
6 FULL(s)
7 MONITOR
8 */
9 count(*)
10 FROM
11 sales s
12 INNER JOIN customers c
13 ON
14* s.cust_id = c.cust_id


実行計画
----------------------------------------------------------
Plan hash value: 3219640484

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 56 (24)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 10 | | | | |
|* 2 | HASH JOIN | | 918K| 8973K| 56 (24)| 00:00:01 | | |
| 3 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 55500 | 270K| 17 (6)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 918K| 4486K| 34 (21)| 00:00:01 | 1 | 28 |
| 5 | TABLE ACCESS INMEMORY FULL| SALES | 918K| 4486K| 34 (21)| 00:00:01 | 1 | 28 |
-----------------------------------------------------------------------------------------------------------

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

2 - access("S"."CUST_ID"="C"."CUST_ID")


統計
----------------------------------------------------------
4 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
593 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

...略...

内視鏡で撮影したスナップショットにもメモしてありますが、HASH JOIN操作の行のInfo列の双眼鏡アイコンをクリックすることで、In-Memory Vectorized Joinが行われたか否かを確認することができます。
DeepVec Hash Joins が重要なキーワードです。見逃さないようにしてくださいね。

今の所、この方法が最も確実で分かりやすい方法です。
Vectorized-join1
Vectorized-join11

次に、通常のHASH JOINとどう違うのかも見ておきましょう。
sales表と、customers表を非インメモリー化します。

SH@orclpdb1> alter table customers no inmemory;

表が変更されました。

SH@orclpdb1> alter table sales no inmemory;

表が変更されました。

SH@orclpdb1> select segment_name,inmemory_size,bytes_not_populated from v$im_segments;

SEGMENT_NAME INMEMORY_SIZE BYTES_NOT_POPULATED
------------------------------ ------------- -------------------
DAY4 15990784 0


1  SELECT
2 /*+
3 NO_STAR_TRANSFORMATION
4 NO_VECTOR_TRANSFORM
5 FULL(c)
6 FULL(s)
7 MONITOR
8 */
9 count(*)
10 FROM
11 sales s
12 INNER JOIN customers c
13 ON
14* s.cust_id = c.cust_id

実行計画
----------------------------------------------------------
Plan hash value: 3219640484

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 947 (2)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 10 | | | | |
|* 2 | HASH JOIN | | 918K| 8973K| 947 (2)| 00:00:01 | | |
| 3 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 270K| 423 (1)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL| | 918K| 4486K| 518 (2)| 00:00:01 | 1 | 28 |
| 5 | TABLE ACCESS FULL | SALES | 918K| 4486K| 518 (2)| 00:00:01 | 1 | 28 |
---------------------------------------------------------------------------------------------------

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

2 - access("S"."CUST_ID"="C"."CUST_ID")

統計
----------------------------------------------------------
178 recursive calls
0 db block gets
3444 consistent gets
1586 physical reads
220 redo size
593 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
1 rows processed

...略...


SQLモニターアクティブレポート(html)というなの内視鏡のスナップショット画像から、例の双眼鏡アイコンをクリックして結果をみると、In-Memory Vectorized Joinが行われていないことが分かります。また、In-memory table full も抑止したのでその差は物凄い差になって現れますね。
In-Memory Vectorized Join 19.39 ms
Hash Join / In-Memory Off 0.13 s


20221205-85808
20221205-85820

....

SQLモニターアクティブレポート(html)以外に、システム統計からも確認できるようですね。慣れないと読みにくいかも知れないですが、特定のSQL実行前後の変化を見る必要があります。ちょっと面倒ですね。セッション統計でも拾えるのだろうか。。。。(要追跡調査)

SOURCE     NAME                                     Before                After
---------- ---------------------------------------- -------------------- --------------------
sysstat IM simd KV add rows 610500 666000
sysstat IM simd KV probe calls 176 192
sysstat IM simd KV probe keys 393888 429696
sysstat IM simd KV probe rows 393888 429696
sysstat IM simd decode symbol calls 352 384
sysstat IM simd decode unpack calls 176 192
sysstat IM simd decode unpack selective calls 176 192

最後に、インメモリーにポピュレートされた状態で、In-Memory Vectorized Joinを無効化して差分をみてみましょう。

SH@orclpdb1> alter session set INMEMORY_DEEP_VECTORIZATION = false;

セッションが変更されました。

SH@orclpdb1> show parameter INMEMORY_DEEP_VECTORIZATION

NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
inmemory_deep_vectorization boolean FALSE


該当表はポピュレートされている状態です

SH@orclpdb1> select segment_name,inmemory_size,bytes_not_populated from v$im_segments;

SEGMENT_NAME INMEMORY_SIZE BYTES_NOT_POPULATED
------------------------------ ------------- -------------------
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
DAY4 15990784 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
CUSTOMERS 2359296 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0
SALES 1310720 0


実行計画は、In-Memory Vectorized Joinが行われたものと同じ(Plan hash valueが同一)ですが、前後で取得したシステム統計値で、In-Memory Vectorized Joinに関連するsimd関連の数値に変化がみられません!!!!
システム統計をみるとIn-Memory Vectorized Joinは動作してないことが分かります。とはいえ、SQLモニターレポート(アクティブレポート)が明らかに見やすいとは思います。

  1  SELECT
2 /*+
3 NO_STAR_TRANSFORMATION
4 NO_VECTOR_TRANSFORM
5 FULL(c)
6 FULL(s)
7 MONITOR
8 */
9 count(*)
10 FROM
11 sales s
12 INNER JOIN customers c
13 ON
14* s.cust_id = c.cust_id

経過: 00:00:00.03

実行計画
----------------------------------------------------------
Plan hash value: 3219640484

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 56 (24)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 10 | | | | |
|* 2 | HASH JOIN | | 918K| 8973K| 56 (24)| 00:00:01 | | |
| 3 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 55500 | 270K| 17 (6)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 918K| 4486K| 34 (21)| 00:00:01 | 1 | 28 |
| 5 | TABLE ACCESS INMEMORY FULL| SALES | 918K| 4486K| 34 (21)| 00:00:01 | 1 | 28 |
-----------------------------------------------------------------------------------------------------------

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

2 - access("S"."CUST_ID"="C"."CUST_ID")

統計
----------------------------------------------------------
33 recursive calls
0 db block gets
67 consistent gets
0 physical reads
0 redo size
593 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
1 rows processed

...略...

SOURCE NAME Before After
---------- -------------------------------------------------- ---------- ----------
sysstat IM simd KV add rows 0 0
sysstat IM simd KV probe calls 0 0
sysstat IM simd KV probe keys 0 0
sysstat IM simd KV probe rows 0 0
sysstat IM simd decode symbol calls 0 0
sysstat IM simd decode unpack calls 0 0
sysstat IM simd decode unpack selective calls 0 0


In-Memory Vectorized Joinが、利用されたかどうかを判断しやすいというわけではないですが、システム統計の差分で判断するのも、ありといえばアリですかね。この結果をみると。
In-Memory Vectorized Joinを無効化すると、IM simd ***という複数の統計値が変化しなくなりました!
ただ、血液検査みたいなものなので、これは、一般的には、判断の敷居が上がる気がします。

もう少し簡易に動作有無が判断できるようになると良いのではないかと思います。

例えば、実行後の NOTE) 等のテキストで、実行されたことが判断できる情報を付加してもらうとか。auto explainでもいいし、AWR SQLレポートや実行計画をリストするときに、追加で特定のビューを問い合わせると分かるとか、とか。

実行時間の差にも注目してみてください。

どちらもIn-Memory table full scanが行われた場合、このケースでIn-Memory Vectorized Joinを利用した場合、10%ほど改善しています、より効果的な場面で利用すれば大きな改善につながりそうではありますね。
In-Memory Vectorized Join 有効化 19.39 ms
In-Memory Vectorized Join 無効化 22.54 ms
20221205-82613
20221205-82624

明日も、やはり、担当は、私ですw



参考)
Oracle Database 21c / 8.3 Optimizing Joins Using In-Memory Deep Vectorization




Related article on Mac De Oracle
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 20 / MINUS
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#3 / RDFView
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#4 / INDEX FULL SCAN (MIN/MAX) - Index Only Scan
・実行計画は, SQL文のレントゲン写真だ! No.30 - LOAD TABLE CONVENTIONAL vs. LOAD AS SELECT
・実行計画は, SQL文のレントゲン写真だ! No.31 - TEMP TABLE TRANSFORMATION LOAD AS SELECT (CURSOR DURATION MEMORY)
・実行計画は, SQL文のレントゲン写真だ! No.32 - EXTERNAL TABLE ACCESS FULL / INMEMORY FULL
・実行計画は, SQL文のレントゲン写真だ! No.33 - BITMAP CONVERSION TO ROWIDS
・実行計画は, SQL文のレントゲン写真だ! No.34 - 似て非なるもの USE_CONCAT と OR_EXPAND ヒント と 手書きSQLのレントゲンの見分け方
・実行計画は, SQL文のレントゲン写真だ! No.35 - 似て非なるもの USE_CONCAT と OR_EXPANDヒントとパラレルクエリー
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 1 / No.36 / INTERSECT ALL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 2 / No.37 / MINUS ALL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 3 / No.38 / EXCEPT and EXCEPT ALL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 4 / No.39 / In-Memory Hybrid Scans
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 5 / No.40 / PIVOT and UNPIVOT

| |

コメント

コメントを書く