前回はOracle Database 21c XEの構築を行いましたが、今回は21c XE上でオプション機能のIn-Memoryを試してみようと思います。
なおIn-Memoryの解説は以下をご参照ください。
津島博士のOracle Database In-Memory (DBIM) の基礎 と 最新情報
データの準備
データについてはお馴染みのサンプルスキーマを活用しました。
サンプルスキーマを作成するスクリプトは以下から入手できます。
Oracle Database 21c Sample Schemas
DBサーバにアップロードして適当な場所に解凍しましょう。
なおスクリプト内のパスで「__SUB__CWD__」となっている箇所を置換しないとエラーになりますので、例えば次のようなコマンドで置き換えましょう。
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat
PDBにログインしてスクリプトを実行します。
$ sqlplus sys@localhost:1521/xepdb1 as sysdba
SQL> @<path to dir>/mksample <SYSTEM_password> <SYS_password> <HR_password> <OE_password> <PM_password> <IX_password> <SH_password> <BI_password> USERS TEMP <path to log dir> localhost:1521/xepdb1
以上でデータの準備は完了です。
In-Memoryの有効化
ルート・コンテナにログインしてinmemory_sizeを設定した後、インスタンスを再起動してIn-Memoryを有効化します。
今回は適当に300Mで確保しました。
$ sqlplus / as sysdba
SQL> alter system set inmemory_size=300M scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1610612080 bytes
Fixed Size 9686384 bytes
Variable Size 335544320 bytes
Database Buffers 939524096 bytes
Redo Buffers 7090176 bytes
In-Memory Area 318767104 bytes
Database mounted.
Database opened.
上記ログからIn-Memory用の領域が確保されていると分かります。
In-Memoryを使用しない状態でクエリ実行
手始めにIn-Memoryを使わない状態でクエリを実行してみます。
Sales Historyの表に対して集計するクエリを発行します。
-- バッファキャッシュのクリア
$ sqlplus sys@localhost:1521/xepdb1 as sysdba
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> exit
-- 実行時間と実行計画の出力設定
$ sqlplus sh@localhost:1521/xepdb1
SQL> set timing on
SQL> set autotrace traceonly
-- 表示設定
SQL> set pagesize 1000
SQL> set linesize 1000
-- 集計クエリ実行
SQL> SELECT channel_desc, TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$,
RANK() OVER (ORDER BY SUM(amount_sold)) AS default_rank,
RANK() OVER (ORDER BY SUM(amount_sold) DESC NULLS LAST) AS custom_rank
FROM sh.sales, sh.products, sh.customers, sh.times, sh.channels, sh.countries
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
AND customers.country_id = countries.country_id AND sales.time_id=times.time_id
AND sales.channel_id=channels.channel_id
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND country_iso_code='US'
GROUP BY channel_desc;
Elapsed: 00:00:00.20
Execution Plan
----------------------------------------------------------
Plan hash value: 1456095225
--------------------------------------------------------------------------------
----------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------------
| 0 | SELECT STATEMENT | | 5 | 380 | 969
(2)| 00:00:01 | | |
| 1 | WINDOW SORT | | 5 | 380 | 969
(2)| 00:00:01 | | |
| 2 | WINDOW SORT | | 5 | 380 | 969
(2)| 00:00:01 | | |
| 3 | HASH GROUP BY | | 5 | 380 | 969
(2)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 2016 | 149K| 966
(2)| 00:00:01 | | |
|* 5 | HASH JOIN | | 2016 | 141K| 966
(2)| 00:00:01 | | |
| 6 | TABLE ACCESS FULL | CHANNELS | 5 | 65 | 3
(0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 2016 | 116K| 963
(2)| 00:00:01 | | |
| 8 | PART JOIN FILTER CREATE | :BF0000 | 61 | 976 | 18
(0)| 00:00:01 | | |
|* 9 | TABLE ACCESS FULL | TIMES | 61 | 976 | 18
(0)| 00:00:01 | | |
|* 10 | HASH JOIN | | 48360 | 2030K| 945
(2)| 00:00:01 | | |
|* 11 | HASH JOIN | | 2921 | 52578 | 426
(1)| 00:00:01 | | |
|* 12 | TABLE ACCESS FULL | COUNTRIES | 1 | 8 | 3
(0)| 00:00:01 | | |
| 13 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| 423
(1)| 00:00:01 | | |
| 14 | PARTITION RANGE JOIN-FILTER| | 918K| 21M| 516
(2)| 00:00:01 |:BF0000|:BF0000|
| 15 | TABLE ACCESS FULL | SALES | 918K| 21M| 516
(2)| 00:00:01 |:BF0000|:BF0000|
|* 16 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | 4 | 0
(0)| 00:00:01 | | |
--------------------------------------------------------------------------------
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SALES"."CHANNEL_ID"="CHANNELS"."CHANNEL_ID")
7 - access("SALES"."TIME_ID"="TIMES"."TIME_ID")
9 - filter("TIMES"."CALENDAR_MONTH_DESC"='2000-09' OR "TIMES"."CALENDAR_MONTH
_DESC"='2000-10')
10 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID")
11 - access("CUSTOMERS"."COUNTRY_ID"="COUNTRIES"."COUNTRY_ID")
12 - filter("COUNTRY_ISO_CODE"='US')
16 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
2097 recursive calls
0 db block gets
5391 consistent gets
1875 physical reads
308 redo size
958 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
138 sorts (memory)
0 sorts (disk)
3 rows processed
In-Memoryを使用した状態でクエリ実行
次にIn-Memoryを使用して同じクエリを再実行してみます。
-- バッファキャッシュのクリア
$ sqlplus sys@localhost:1521/xepdb1 as sysdba
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> exit
-- SHスキーマの対象テーブルをIn-Memory領域にポピュレート
$ sqlplus sh@localhost:1521/xepdb1
SQL> alter table sales inmemory priority critical;
SQL> alter table products inmemory priority critical;
SQL> alter table customers inmemory priority critical;
SQL> alter table times inmemory priority critical;
SQL> alter table channels inmemory priority critical;
SQL> alter table countries inmemory priority critical;
-- 表がポピュレートされたことを確認
SQL> set pagesize 1000
SQL> set linesize 1000
SQL> col owner for a10
SQL> col segment_name for a20
SQL> SELECT v.owner, v.segment_name,
v.bytes orig_size,
v.inmemory_size in_mem_size,
v.bytes / v.inmemory_size comp_ratio
FROM v$im_segments v;
OWNER SEGMENT_NAME ORIG_SIZE IN_MEM_SIZE COMP_RATIO
---------- -------------------- ---------- ----------- ----------
SH SALES 876544 1310720 .66875
SH SALES 647168 1310720 .49375
SH SALES 868352 1310720 .6625
SH SALES 753664 1310720 .575
SH SALES 819200 1310720 .625
SH CUSTOMERS 12427264 5505024 2.25744048
SH SALES 696320 1310720 .53125
SH SALES 999424 1310720 .7625
SH SALES 843776 1310720 .64375
SH SALES 868352 1310720 .6625
SH SALES 884736 1310720 .675
SH SALES 786432 1310720 .6
SH SALES 557056 1310720 .425
SH SALES 917504 1310720 .7
SH SALES 901120 1310720 .6875
SH TIMES 434176 1310720 .33125
SH SALES 770048 1310720 .5875
SH SALES 802816 1310720 .6125
-- 集計クエリ実行
SQL> SELECT channel_desc, TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$,
RANK() OVER (ORDER BY SUM(amount_sold)) AS default_rank,
RANK() OVER (ORDER BY SUM(amount_sold) DESC NULLS LAST) AS custom_rank
FROM sh.sales, sh.products, sh.customers, sh.times, sh.channels, sh.countries
WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
AND customers.country_id = countries.country_id AND sales.time_id=times.time_id
AND sales.channel_id=channels.channel_id
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND country_iso_code='US'
GROUP BY channel_desc;
Elapsed: 00:00:00.18
Execution Plan
----------------------------------------------------------
Plan hash value: 1538282445
--------------------------------------------------------------------------------
------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
------------------------------------
| 0 | SELECT STATEMENT | | 5 | 380 |
70 (29)| 00:00:01 | | |
| 1 | WINDOW SORT | | 5 | 380 |
70 (29)| 00:00:01 | | |
| 2 | WINDOW SORT | | 5 | 380 |
70 (29)| 00:00:01 | | |
| 3 | HASH GROUP BY | | 5 | 380 |
70 (29)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 2016 | 149K|
67 (26)| 00:00:01 | | |
|* 5 | HASH JOIN | | 2016 | 141K|
67 (26)| 00:00:01 | | |
| 6 | TABLE ACCESS INMEMORY FULL | CHANNELS | 5 | 65 |
3 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 2016 | 116K|
64 (27)| 00:00:01 | | |
| 8 | JOIN FILTER CREATE | :BF0001 | 61 | 976 |
1 (0)| 00:00:01 | | |
| 9 | PART JOIN FILTER CREATE | :BF0000 | 61 | 976 |
1 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS INMEMORY FULL | TIMES | 61 | 976 |
1 (0)| 00:00:01 | | |
|* 11 | HASH JOIN | | 48360 | 2030K|
62 (26)| 00:00:01 | | |
| 12 | JOIN FILTER CREATE | :BF0002 | 2921 | 52578 |
20 (5)| 00:00:01 | | |
|* 13 | HASH JOIN | | 2921 | 52578 |
20 (5)| 00:00:01 | | |
| 14 | JOIN FILTER CREATE | :BF0003 | 2921 | 52578 |
20 (5)| 00:00:01 | | |
|* 15 | TABLE ACCESS INMEMORY FULL| COUNTRIES | 1 | 8 |
3 (0)| 00:00:01 | | |
| 16 | JOIN FILTER USE | :BF0003 | 55500 | 541K|
16 (0)| 00:00:01 | | |
|* 17 | TABLE ACCESS INMEMORY FULL| CUSTOMERS | 55500 | 541K|
16 (0)| 00:00:01 | | |
| 18 | JOIN FILTER USE | :BF0001 | 918K| 21M|
40 (33)| 00:00:01 | | |
| 19 | JOIN FILTER USE | :BF0002 | 918K| 21M|
40 (33)| 00:00:01 | | |
| 20 | PARTITION RANGE JOIN-FILTER| | 918K| 21M|
40 (33)| 00:00:01 |:BF0000|:BF0000|
|* 21 | TABLE ACCESS INMEMORY FULL| SALES | 918K| 21M|
40 (33)| 00:00:01 |:BF0000|:BF0000|
|* 22 | INDEX UNIQUE SCAN | PRODUCTS_PK | 1 | 4 |
0 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------
------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SALES"."CHANNEL_ID"="CHANNELS"."CHANNEL_ID")
7 - access("SALES"."TIME_ID"="TIMES"."TIME_ID")
10 - inmemory("TIMES"."CALENDAR_MONTH_DESC"='2000-09' OR "TIMES"."CALENDAR_MON
TH_DESC"='2000-10')
filter("TIMES"."CALENDAR_MONTH_DESC"='2000-09' OR "TIMES"."CALENDAR_MONTH
_DESC"='2000-10')
11 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID")
13 - access("CUSTOMERS"."COUNTRY_ID"="COUNTRIES"."COUNTRY_ID")
15 - inmemory("COUNTRY_ISO_CODE"='US')
filter("COUNTRY_ISO_CODE"='US')
17 - inmemory(SYS_OP_BLOOM_FILTER(:BF0003,"CUSTOMERS"."COUNTRY_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0003,"CUSTOMERS"."COUNTRY_ID"))
21 - inmemory(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0002,"SALES"."CU
ST_ID"),SYS_OP_BLOOM_FILTER(
:BF0001,"SALES"."TIME_ID")))
filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0002,"SALES"."CUST
_ID"),SYS_OP_BLOOM_FILTER(:B
F0001,"SALES"."TIME_ID")))
22 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
2283 recursive calls
0 db block gets
3855 consistent gets
135 physical reads
0 redo size
958 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
149 sorts (memory)
0 sorts (disk)
3 rows processed
軽いクエリなので実行時間に大きな差は出ていませんが、「TABLE ACCESS INMEMORY FULL」などIn-Memoryを利用したことが実行計画に表れています。
また8行目などにブルーム・フィルタを活用している点が表れており、In-Memory未使用時と実行計画が変わっています。
このようにOracle Database 21c XEなら無料で、本来有償のオプション機能を試せるようになっています。
気になるオプション機能や新機能がありましたら、お気軽に使ってみてください！
Slowly But Surely 