実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 4 / No.39 / In-Memory Hybrid Scans Tweet
reviously on Mac De Oracle...
Day3は, Oracleの場合の EXCEPT/EXCEPT ALLは, 内部で MINUS/MINUS ALLになってるね. やってること同じだし. SQLのキーワードが違うだけなので, まあ, そりゃそうだという感じの実装になっているようだ.
というところを, 実行計画という名のレントゲンで確認しましたw
それでは, Day 4 の窓を開けましょう!
タイトルの通り, SQL単体ではなく, 実行計画上の新機能を診てみたいと思います. 狙い通りのレントゲンが取れるでしょうか. . . .
いつもと同じように 21c で確認します.
SCOTT@orclpdb1> select banner from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
なお, 今回は, in-memory機能を利用するのでお約束の inmemory_size パラメータを確認しておきますよ.
以前, 何度かセットし忘れで in-memory 発動せず!
みたいなこと何度かやらかしてたのことを思い出しますw
ちなみに, この inmemory_size パラメータのデフォルトは 0
SYS@ORCLCDB> show parameter inmemory_size
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
inmemory_size big integer 112M
まず, 準備から. データサイズが大したことないのと, データのカーディナリティもあまり深く考えてなかったのでイマイチかも知れません.
in-memory columnsと no in-memory columnsを持つ表を作成します. In-Memory Hybrid Scansは, 同一表の列がインメモリーだったり非インメモリーだったという状況で効果のある操作なので. :)
SCOTT@orclpdb1> @day4
表が削除されました.
経過: 00:00:04.17
1 CREATE TABLE day4
2 (
3 no_im_id NUMBER
4 , im_subid NUMBER
5 , no_im_str VARCHAR2(1000)
6 , im_str VARCHAR2(1000)
7 )
8 INMEMORY PRIORITY HIGH
9 MEMCOMPRESS FOR CAPACITY LOW
10 NO INMEMORY (
11 no_im_id
12 , no_im_str
13* )
表が作成されました.
経過: 00:00:00.02
1 BEGIN
2 FOR i IN 1..100000 LOOP
3 INSERT
4 INTO day4
5 VALUES(i, i+1000, LPAD(TO_CHAR(i),1000,'*'), LPAD(TO_CHAR(i+1000),1000.,'*'));
6 IF MOD(i,100) = 0
7 THEN
8 COMMIT;
9 END IF;
10 END LOOP;
11* END;
PL/SQLプロシージャが正常に完了しました.
経過: 00:00:16.88
定義を確認しておきましょうね.
1 SELECT
2 table_name
3 , inmemory_compression "compression"
4 , inmemory_priority "priority"
5 FROM
6 user_tables
7 WHERE
8* table_name = 'DAY4'
TABLE_NAME compression priority
---------- --------------------------------------------------- ------------------------
DAY4 FOR CAPACITY LOW HIGH
経過: 00:00:00.00
1 SELECT
2 obj_num
3 , segment_column_id
4 , inmemory_compression
5 FROM
6 v$im_column_level im
7 INNER JOIN dba_objects do
8 ON
9 im.obj_num = do.object_id
10 WHERE
11* do.object_name = 'DAY4'
OBJ_NUM SEGMENT_COLUMN_ID INMEMORY_COM
---------- ----------------- ------------
77251 1 NO INMEMORY
77251 2 DEFAULT
77251 3 NO INMEMORY
77251 4 DEFAULT
経過: 00:00:00.01
データのPOPULATEを行います. 必要な列はすべてインメモリーにPOPULATEされたようです:)
1 SELECT
2 /*+
3 FULL(day4)
4 NO_PARALLEL(day4)
5 */
6 COUNT(*)
7 FROM
8* day4
COUNT(*)
----------
100000
経過: 00:00:00.08
1 SELECT
2 segment_name
3 , inmemory_size
4 , bytes_not_populated
5 FROM
6* v$im_segments
SEGMENT_NAME INMEMORY_SIZE BYTES_NOT_POPULATED
------------ ------------- -------------------
DAY4 15990784 0
経過: 00:00:00.01
準備運動からw SELECT LISTにある2列は, どちらも非インメモリー列なので, 結果としてはよく見る table full scanになります. 当然の結果ですね.
1 SELECT
2 MAX(no_im_id)
3 , COUNT(no_im_str)
4 FROM
5* day4
MAX(NO_IM_ID) COUNT(NO_IM_STR)
------------- ----------------
100000 100000
経過: 00:00:00.09
実行計画
----------------------------------------------------------
Plan hash value: 2217228964
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 515 | 9104 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 515 | | |
| 2 | TABLE ACCESS FULL| DAY4 | 87887 | 43M| 9104 (1)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
33544 consistent gets
0 physical reads
0 redo size
685 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
ベタな検証その2. 今度は, SELECT LISTの2列はどちらもインメモリー列です. 結果はみなさんの想像の通り, TABLE ACCESS INMEMORY FULLです.
consistent getsが非常に少なくなっています. (処理時間的なところはまあ, データ量次第なので大きな差にはなってないですね. 深く分析していないので, 別の機会にデータ量などを変えつつ検証しておきたいですよね, この辺り)
1 SELECT
2 MAX(im_subid)
3 , COUNT(im_str)
4 FROM
5* day4
MAX(IM_SUBID) COUNT(IM_STR)
------------- -------------
101000 100000
経過: 00:00:00.10
実行計画
----------------------------------------------------------
Plan hash value: 2217228964
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 515 | 382 (13)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 515 | | |
| 2 | TABLE ACCESS INMEMORY FULL| DAY4 | 87887 | 43M| 382 (13)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
683 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
次に, in-memory hybrid scan発動の条件に該当しない例をみてみましょう.
このクエリーでは, SELECT LISTにインメモリー列のみ. WHERE句に非インメモリー列のみがありますが, これは, in-memory hybrid scan発動条件に一致しません.
結果として, 通常のTABLE ACCESS FULLとなってしまいますので注意しましょうね.
1 SELECT
2 MAX(im_subid)
3 , COUNT(im_str)
4 FROM
5 day4
6 WHERE
7* no_im_id < 5000
MAX(IM_SUBID) COUNT(IM_STR)
------------- -------------
5999 4999
経過: 00:00:00.08
実行計画
----------------------------------------------------------
Plan hash value: 2217228964
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 528 | 9104 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 528 | | |
|* 2 | TABLE ACCESS FULL| DAY4 | 4794 | 2471K| 9104 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NO_IM_ID"<5000)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
33544 consistent gets
0 physical reads
0 redo size
684 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
最後に, 本日の主役の登場です!
SELECT LISTには, 非インメモリー列のみ. WHERE句には, インメモリー列のみが利用されています. これが発動条件です.
結果として, これまで, みたことのないOperationである, TABLE ACCESS INMEMORY FULL (HYBRID) が現れています. ふむふむ.
consistent getsもかなり少ないですし, このケースでは, 処理時間も早くなっていますね. ますます別途詳しく調査したくなりまし:)
なんとなくですが, この in-memory hybrid scan を見て, MySQL Heatwaveが浮かんできますよね. 諸々応用しているのでしょうかね...
1 SELECT
2 MAX(no_im_id)
3 , COUNT(no_im_str)
4 FROM
5 day4
6 WHERE
7* im_subid < 5000
MAX(NO_IM_ID) COUNT(NO_IM_STR)
------------- ----------------
3999 3999
経過: 00:00:00.02
実行計画
----------------------------------------------------------
Plan hash value: 2217228964
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 528 | 9104 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 528 | | |
|* 2 | TABLE ACCESS INMEMORY FULL (HYBRID)| DAY4 | 1598 | 823K| 9104 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("IM_SUBID"<5000)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
1342 consistent gets
0 physical reads
0 redo size
687 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
アドベントカレンダー, 全部俺をやらなきゃ, 師走は走り回るほど忙しいわけではないはずだったのになぁw と, 遠ーーーくを見てるw
では, また,
明日の担当も! 私です.
参考 Database In-Memory Office Hours / In-Memory Hybrid Scans
そして, Ask Tomがこんな感じになるとは:) いい感じですよね.
Database In-Memory Office Hours / ASK TOM
Database In-Memory Guide / 21c
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
| 固定リンク | 0
コメント