技术标签： LiveLabs Database Oracle DBIM 动手实验 Oracle LiveLabs
本文为Oracle Database In-Memory实验的下篇，上篇参见这里。
下篇的实验属于附加实验，并没有对应的实验手册，但这些实验还是很有用的，因此我自己做了一遍。
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1
进入im-parallel目录，登录数据库：
cd /home/oracle/labs/inmemory/im-parallel
sqlplus ssb/Ora_DB4U@localhost:1521/pdb1
此实验的主要脚本执行序列如下，后面会详述：
01_parallel_status.sql
02_ssb_tables.sql
03_im_populated.sql
04_serial_single.sql
05_parallel_single.sql
06_serial_join.sql
07_parallel_join.sql
08_serial_vgb.sql
09_parallel_vgb.sql
10_imds.sql
首先查看并行的参数设置：
SQL> @01_parallel_status.sql
Connected.
SQL>
SQL> show parameters parallel
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
awr_pdb_max_parallel_slaves integer 10
containers_parallel_degree integer 65535
fast_start_parallel_rollback string LOW
max_datapump_parallel_per_job string 50
optimizer_ignore_parallel_hints boolean FALSE
parallel_adaptive_multi_user boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_max_servers integer 80
parallel_min_degree string 1
parallel_min_percent integer 0
parallel_min_servers integer 8
parallel_min_time_threshold string AUTO
parallel_servers_target integer 32
parallel_threads_per_cpu integer 1
recovery_parallelism integer 0
发布lineorder表：
SQL> @02_ssb_tables.sql
Connected.
SQL>
SQL> alter table LINEORDER inmemory priority high;
Table altered.
SQL> exec dbms_inmemory.populate('SSB','LINEORDER');
PL/SQL procedure successfully completed.
SQL>
SQL> set echo off
查看发布状态：
SQL> @03_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 where owner not in ('AUDSYS','SYS')
5 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB CUSTOMER COMPLETED 24,928,256 23,199,744 0
SSB DATE_DIM COMPLETED 122,880 1,179,648 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
SSB PART COMPLETED 56,893,440 16,973,824 0
SSB SUPPLIER COMPLETED 1,769,472 2,228,224 0
9 rows selected.
串行，单表查询：
SQL> @04_serial_single.sql
-- 耗时
Elapsed: 00:00:00.02
-- 执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4376 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE ALL | | 41M| 358M| 4376 (27)| 00:00:01 | 1 | 5 |
| 3 | TABLE ACCESS INMEMORY FULL| LINEORDER | 41M| 358M| 4376 (27)| 00:00:01 | 1 | 5 |
----------------------------------------------------------------------------------------------------------
并行(通过设置parallel_degree_policy为AUTO)，单表查询：
SQL> @05_parallel_single.sql
SQL> alter session set parallel_degree_policy=auto;
-- 耗时
Elapsed: 00:00:00.07
-- 执行计划
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1216 (100)| | | | | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 9 | | | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 9 | | | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 41M| 358M| 1216 (27)| 00:00:01 | 1 | 5 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS INMEMORY FULL| LINEORDER | 41M| 358M| 1216 (27)| 00:00:01 | 1 | 5 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - inmemory(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 4 because of degree limit
执行计划显示启用了4并行，Cost也降低了。但执行时间看不出有提升，可能因为表太小。
串行，多表的联结查询。由于有提示NO_VECTOR_TRANSFORM，因此用的是Bloom filter：
SQL> 06_serial_join.sql
-- 耗时
Elapsed: 00:00:00.16
-- 执行计划
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5280 (100)| | | |
| 1 | HASH GROUP BY | | 1000 | 77000 | 5280 (38)| 00:00:01 | | |
|* 2 | HASH JOIN | | 98430 | 7401K| 5275 (38)| 00:00:01 | | |
| 3 | JOIN FILTER CREATE | :BF0001 | 365 | 4380 | 1 (0)| 00:00:01 | | |
| 4 | PART JOIN FILTER CREATE | :BF0000 | 365 | 4380 | 1 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 365 | 4380 | 1 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 451K| 28M| 5272 (38)| 00:00:01 | | |
| 7 | JOIN FILTER CREATE | :BF0002 | 4102 | 73836 | 4 (25)| 00:00:01 | | |
|* 8 | TABLE ACCESS INMEMORY FULL | SUPPLIER | 4102 | 73836 | 4 (25)| 00:00:01 | | |
|* 9 | HASH JOIN | | 2216K| 99M| 5256 (38)| 00:00:01 | | |
| 10 | JOIN FILTER CREATE | :BF0003 | 31882 | 716K| 97 (27)| 00:00:01 | | |
|* 11 | TABLE ACCESS INMEMORY FULL | PART | 31882 | 716K| 97 (27)| 00:00:01 | | |
| 12 | JOIN FILTER USE | :BF0001 | 41M| 955M| 4928 (35)| 00:00:01 | | |
| 13 | JOIN FILTER USE | :BF0002 | 41M| 955M| 4928 (35)| 00:00:01 | | |
| 14 | JOIN FILTER USE | :BF0003 | 41M| 955M| 4928 (35)| 00:00:01 | | |
| 15 | PARTITION RANGE JOIN-FILTER| | 41M| 955M| 4928 (35)| 00:00:01 |:BF0000|:BF0000|
|* 16 | TABLE ACCESS INMEMORY FULL| LINEORDER | 41M| 955M| 4928 (35)| 00:00:01 |:BF0000|:BF0000|
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("L"."LO_ORDERDATE"="D"."D_DATEKEY")
5 - inmemory("D"."D_YEAR"=1997)
filter("D"."D_YEAR"=1997)
6 - access("L"."LO_SUPPKEY"="S"."S_SUPPKEY")
8 - inmemory("S"."S_REGION"='AMERICA')
filter("S"."S_REGION"='AMERICA')
9 - access("L"."LO_PARTKEY"="P"."P_PARTKEY")
11 - inmemory("P"."P_CATEGORY"='MFGR#12')
filter("P"."P_CATEGORY"='MFGR#12')
16 - inmemory(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0003,"L"."LO_PARTKEY"),SYS_OP_BLOOM_FILT
ER(:BF0002,"L"."LO_SUPPKEY"),SYS_OP_BLOOM_FILTER(:BF0001,"L"."LO_ORDERDATE")))
filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0003,"L"."LO_PARTKEY"),SYS_OP_BLOOM_FILTER
(:BF0002,"L"."LO_SUPPKEY"),SYS_OP_BLOOM_FILTER(:BF0001,"L"."LO_ORDERDATE")))
并行，多表联结查询。
SQL> @07_parallel_join.sql
-- 耗时
Elapsed: 00:00:00.09
-- 执行计划
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1464 (100)| | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1000 | 77000 | 1464 (38)| 00:00:01 | | | Q1,02 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 1000 | 77000 | 1464 (38)| 00:00:01 | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 1000 | 77000 | 1464 (38)| 00:00:01 | | | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | 1000 | 77000 | 1464 (38)| 00:00:01 | | | Q1,01 | P->P | HASH |
| 6 | HASH GROUP BY | | 1000 | 77000 | 1464 (38)| 00:00:01 | | | Q1,01 | PCWP | |
|* 7 | HASH JOIN | | 97986 | 7368K| 1462 (38)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | JOIN FILTER CREATE | :BF0001 | 365 | 4380 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 9 | PART JOIN FILTER CREATE | :BF0000 | 365 | 4380 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 10 | PX RECEIVE | | 365 | 4380 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10000 | 365 | 4380 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | BROADCAST |
| 12 | PX BLOCK ITERATOR | | 365 | 4380 | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
|* 13 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 365 | 4380 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 14 | HASH JOIN | | 449K| 27M| 1459 (38)| 00:00:01 | | | Q1,01 | PCWP | |
| 15 | JOIN FILTER CREATE | :BF0002 | 4102 | 73836 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
|* 16 | TABLE ACCESS INMEMORY FULL | SUPPLIER | 4102 | 73836 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
|* 17 | HASH JOIN | | 2206K| 98M| 1454 (38)| 00:00:01 | | | Q1,01 | PCWP | |
| 18 | JOIN FILTER CREATE | :BF0003 | 31738 | 712K| 27 (26)| 00:00:01 | | | Q1,01 | PCWP | |
|* 19 | TABLE ACCESS INMEMORY FULL | PART | 31738 | 712K| 27 (26)| 00:00:01 | | | Q1,01 | PCWP | |
| 20 | JOIN FILTER USE | :BF0001 | 41M| 955M| 1369 (35)| 00:00:01 | | | Q1,01 | PCWP | |
| 21 | JOIN FILTER USE | :BF0002 | 41M| 955M| 1369 (35)| 00:00:01 | | | Q1,01 | PCWP | |
| 22 | JOIN FILTER USE | :BF0003 | 41M| 955M| 1369 (35)| 00:00:01 | | | Q1,01 | PCWP | |
| 23 | PX BLOCK ITERATOR ADAPTIVE | | 41M| 955M| 1369 (35)| 00:00:01 |:BF0000|:BF0000| Q1,01 | PCWC | |
|* 24 | TABLE ACCESS INMEMORY FULL| LINEORDER | 41M| 955M| 1369 (35)| 00:00:01 |:BF0000|:BF0000| Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("L"."LO_ORDERDATE"="D"."D_DATEKEY")
13 - inmemory(:Z>=:Z AND :Z<=:Z AND "D"."D_YEAR"=1997)
filter("D"."D_YEAR"=1997)
14 - access("L"."LO_SUPPKEY"="S"."S_SUPPKEY")
16 - inmemory("S"."S_REGION"='AMERICA')
filter("S"."S_REGION"='AMERICA')
17 - access("L"."LO_PARTKEY"="P"."P_PARTKEY")
19 - inmemory("P"."P_CATEGORY"='MFGR#12')
filter("P"."P_CATEGORY"='MFGR#12')
24 - inmemory(:Z>=:Z AND :Z<=:Z AND SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0003,"L"."LO_PARTKEY"),SYS_OP_BLOOM_FILTER(:BF0002,"
L"."LO_SUPPKEY"),SYS_OP_BLOOM_FILTER(:BF0001,"L"."LO_ORDERDATE")))
filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0003,"L"."LO_PARTKEY"),SYS_OP_BLOOM_FILTER(:BF0002,"L"."LO_SUPPKEY"),SYS_OP_
BLOOM_FILTER(:BF0001,"L"."LO_ORDERDATE")))
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))
- automatic DOP: Computed Degree of Parallelism is 4 because of degree limit
执行计划显示启用了4并行。
Cost确实降低了。执行时间有微小提升。
串行，多表联结，使用向量转换，即Vector Group By, 之前都是Bloom filter：
SQL> @08_serial_vgb.sql
-- 耗时
Elapsed: 00:00:00.12
-- 执行计划
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5043 (100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6649_53CF1A | | | | | | |
| 3 | HASH GROUP BY | | 1 | 16 | 2 (50)| 00:00:01 | | |
| 4 | KEY VECTOR CREATE BUFFERED | :KV0000 | 1 | 16 | 1 (0)| 00:00:01 | | |
|* 5 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 365 | 4380 | 1 (0)| 00:00:01 | | |
| 6 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6647_53CF1A | | | | | | |
| 7 | HASH GROUP BY | | 1000 | 27000 | 100 (29)| 00:00:01 | | |
| 8 | KEY VECTOR CREATE BUFFERED | :KV0001 | 1000 | 27000 | 98 (28)| 00:00:01 | | |
|* 9 | TABLE ACCESS INMEMORY FULL | PART | 31882 | 716K| 97 (27)| 00:00:01 | | |
| 10 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6648_53CF1A | | | | | | |
| 11 | HASH GROUP BY | | 1 | 22 | 5 (40)| 00:00:01 | | |
| 12 | KEY VECTOR CREATE BUFFERED | :KV0002 | 1 | 22 | 4 (25)| 00:00:01 | | |
|* 13 | TABLE ACCESS INMEMORY FULL | SUPPLIER | 4102 | 73836 | 4 (25)| 00:00:01 | | |
| 14 | HASH GROUP BY | | 500 | 49500 | 4937 (35)| 00:00:01 | | |
|* 15 | HASH JOIN | | 500 | 49500 | 4936 (35)| 00:00:01 | | |
| 16 | VIEW | VW_VT_80F21617 | 500 | 19000 | 4929 (35)| 00:00:01 | | |
| 17 | VECTOR GROUP BY | | 500 | 18000 | 4929 (35)| 00:00:01 | | |
| 18 | HASH GROUP BY | | 500 | 18000 | 4929 (35)| 00:00:01 | | |
| 19 | KEY VECTOR USE | :KV0000 | 98430 | 3460K| 4929 (35)| 00:00:01 | | |
| 20 | KEY VECTOR USE | :KV0002 | 451K| 13M| 4929 (35)| 00:00:01 | | |
| 21 | KEY VECTOR USE | :KV0001 | 2216K| 59M| 4928 (35)| 00:00:01 | | |
| 22 | PARTITION RANGE ITERATOR | | 41M| 955M| 4928 (35)| 00:00:01 |:KV0000|:KV0000|
|* 23 | TABLE ACCESS INMEMORY FULL | LINEORDER | 41M| 955M| 4928 (35)| 00:00:01 |:KV0000|:KV0000|
| 24 | MERGE JOIN CARTESIAN | | 1000 | 61000 | 7 (0)| 00:00:01 | | |
| 25 | MERGE JOIN CARTESIAN | | 1 | 38 | 4 (0)| 00:00:01 | | |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6649_53CF1A | 1 | 16 | 2 (0)| 00:00:01 | | |
| 27 | BUFFER SORT | | 1 | 22 | 2 (0)| 00:00:01 | | |
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6648_53CF1A | 1 | 22 | 2 (0)| 00:00:01 | | |
| 29 | BUFFER SORT | | 1000 | 23000 | 5 (0)| 00:00:01 | | |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_53CF1A | 1000 | 23000 | 3 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - inmemory("D"."D_YEAR"=1997)
filter("D"."D_YEAR"=1997)
9 - inmemory("P"."P_CATEGORY"='MFGR#12')
filter("P"."P_CATEGORY"='MFGR#12')
13 - inmemory("S"."S_REGION"='AMERICA')
filter("S"."S_REGION"='AMERICA')
15 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_12"=INTERNAL_FUNCTION("C0") AND "ITEM_11"=INTERNAL_FUNCTION("C0"))
23 - inmemory((SYS_OP_KEY_VECTOR_FILTER("L"."LO_PARTKEY",:KV0001) AND SYS_OP_KEY_VECTOR_FILTER("L"."LO_SUPPKEY",:KV0002)
AND SYS_OP_KEY_VECTOR_FILTER("L"."LO_ORDERDATE",:KV0000)))
filter((SYS_OP_KEY_VECTOR_FILTER("L"."LO_PARTKEY",:KV0001) AND SYS_OP_KEY_VECTOR_FILTER("L"."LO_SUPPKEY",:KV0002) AND
SYS_OP_KEY_VECTOR_FILTER("L"."LO_ORDERDATE",:KV0000)))
Note
-----
- vector transformation used for this statement
注意执行计划中的Note部分，表示使用了vector transformation。比Bloom filter快一点点。
并行，多表联结，使用向量转换：
SQL> @09_parallel_vgb.sql
SQL> alter session set parallel_degree_policy=auto;
SQL> alter session set parallel_min_time_threshold=0;
SQL>
-- 耗时
Elapsed: 00:00:00.11
-- 执行计划
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1411 (100)| | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6655_53CF1A | | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 16 | 3 (34)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 5 | HASH GROUP BY | | 1 | 16 | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX RECEIVE | | 1 | 16 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 1 | 16 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 8 | KEY VECTOR CREATE BUFFERED | :KV0000 | 1 | 16 | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
| 9 | PX BLOCK ITERATOR | | 365 | 4380 | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
|* 10 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 365 | 4380 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 11 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6653_53CF1A | | | | | | | | | |
| 12 | PX COORDINATOR | | | | | | | | | | |
| 13 | PX SEND QC (RANDOM) | :TQ20001 | 1000 | 27000 | 28 (29)| 00:00:01 | | | Q2,01 | P->S | QC (RAND) |
| 14 | HASH GROUP BY | | 1000 | 27000 | 28 (29)| 00:00:01 | | | Q2,01 | PCWP | |
| 15 | PX RECEIVE | | 1000 | 27000 | 27 (26)| 00:00:01 | | | Q2,01 | PCWP | |
| 16 | PX SEND HASH | :TQ20000 | 1000 | 27000 | 27 (26)| 00:00:01 | | | Q2,00 | P->P | HASH |
| 17 | KEY VECTOR CREATE BUFFERED | :KV0001 | 1000 | 27000 | 27 (26)| 00:00:01 | | | Q2,00 | PCWC | |
| 18 | PX BLOCK ITERATOR | | 31738 | 712K| 27 (26)| 00:00:01 | | | Q2,00 | PCWC | |
|* 19 | TABLE ACCESS INMEMORY FULL | PART | 31738 | 712K| 27 (26)| 00:00:01 | | | Q2,00 | PCWP | |
| 20 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D6654_53CF1A | | | | | | | | | |
| 21 | PX COORDINATOR | | | | | | | | | | |
| 22 | PX SEND QC (RANDOM) | :TQ30001 | 1 | 22 | 3 (34)| 00:00:01 | | | Q3,01 | P->S | QC (RAND) |
| 23 | HASH GROUP BY | | 1 | 22 | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 24 | PX RECEIVE | | 1 | 22 | 2 (0)| 00:00:01 | | | Q3,01 | PCWP | |
| 25 | PX SEND HASH | :TQ30000 | 1 | 22 | 2 (0)| 00:00:01 | | | Q3,00 | P->P | HASH |
| 26 | KEY VECTOR CREATE BUFFERED | :KV0002 | 1 | 22 | 2 (0)| 00:00:01 | | | Q3,00 | PCWC | |
| 27 | PX BLOCK ITERATOR | | 4102 | 73836 | 2 (0)| 00:00:01 | | | Q3,00 | PCWC | |
|* 28 | TABLE ACCESS INMEMORY FULL | SUPPLIER | 4102 | 73836 | 2 (0)| 00:00:01 | | | Q3,00 | PCWP | |
| 29 | PX COORDINATOR | | | | | | | | | | |
| 30 | PX SEND QC (RANDOM) | :TQ40003 | 500 | 49500 | 1375 (35)| 00:00:01 | | | Q4,03 | P->S | QC (RAND) |
|* 31 | HASH JOIN BUFFERED | | 500 | 49500 | 1375 (35)| 00:00:01 | | | Q4,03 | PCWP | |
| 32 | PX RECEIVE | | 500 | 38000 | 1373 (35)| 00:00:01 | | | Q4,03 | PCWP | |
| 33 | PX SEND HYBRID HASH | :TQ40001 | 500 | 38000 | 1373 (35)| 00:00:01 | | | Q4,01 | P->P | HYBRID HASH|
| 34 | STATISTICS COLLECTOR | | | | | | | | Q4,01 | PCWC | |
|* 35 | HASH JOIN | | 500 | 38000 | 1373 (35)| 00:00:01 | | | Q4,01 | PCWP | |
| 36 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6654_53CF1A | 1 | 22 | 2 (0)| 00:00:01 | | | Q4,01 | PCWP | |
|* 37 | HASH JOIN | | 500 | 27000 | 1371 (35)| 00:00:01 | | | Q4,01 | PCWP | |
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6655_53CF1A | 1 | 16 | 2 (0)| 00:00:01 | | | Q4,01 | PCWP | |
| 39 | VIEW | VW_VT_80F21617 | 500 | 19000 | 1369 (35)| 00:00:01 | | | Q4,01 | PCWP | |
| 40 | HASH GROUP BY | | 500 | 18000 | 1369 (35)| 00:00:01 | | | Q4,01 | PCWP | |
| 41 | PX RECEIVE | | 500 | 18000 | 1369 (35)| 00:00:01 | | | Q4,01 | PCWP | |
| 42 | PX SEND HASH | :TQ40000 | 500 | 18000 | 1369 (35)| 00:00:01 | | | Q4,00 | P->P | HASH |
| 43 | VECTOR GROUP BY | | 500 | 18000 | 1369 (35)| 00:00:01 | | | Q4,00 | PCWP | |
| 44 | HASH GROUP BY | | 500 | 18000 | 1369 (35)| 00:00:01 | | | Q4,00 | PCWP | |
| 45 | KEY VECTOR USE | :KV0000 | 98430 | 3460K| 1369 (35)| 00:00:01 | | | Q4,00 | PCWC | |
| 46 | KEY VECTOR USE | :KV0002 | 451K| 13M| 1369 (35)| 00:00:01 | | | Q4,00 | PCWC | |
| 47 | KEY VECTOR USE | :KV0001 | 2216K| 59M| 1369 (35)| 00:00:01 | | | Q4,00 | PCWC | |
| 48 | PX BLOCK ITERATOR | | 41M| 955M| 1369 (35)| 00:00:01 |:KV0000|:KV0000| Q4,00 | PCWC | |
|* 49 | TABLE ACCESS INMEMORY FULL| LINEORDER | 41M| 955M| 1369 (35)| 00:00:01 |:KV0000|:KV0000| Q4,00 | PCWP | |
| 50 | PX RECEIVE | | 1000 | 23000 | 2 (0)| 00:00:01 | | | Q4,03 | PCWP | |
| 51 | PX SEND HYBRID HASH | :TQ40002 | 1000 | 23000 | 2 (0)| 00:00:01 | | | Q4,02 | P->P | HYBRID HASH|
| 52 | PX BLOCK ITERATOR | | 1000 | 23000 | 2 (0)| 00:00:01 | | | Q4,02 | PCWC | |
|* 53 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6653_53CF1A | 1000 | 23000 | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - inmemory(:Z>=:Z AND :Z<=:Z AND "D"."D_YEAR"=1997)
filter("D"."D_YEAR"=1997)
19 - inmemory(:Z>=:Z AND :Z<=:Z AND "P"."P_CATEGORY"='MFGR#12')
filter("P"."P_CATEGORY"='MFGR#12')
28 - inmemory(:Z>=:Z AND :Z<=:Z AND "S"."S_REGION"='AMERICA')
filter("S"."S_REGION"='AMERICA')
31 - access("ITEM_12"=INTERNAL_FUNCTION("C0"))
35 - access("ITEM_11"=INTERNAL_FUNCTION("C0"))
37 - access("ITEM_10"=INTERNAL_FUNCTION("C0"))
49 - inmemory(:Z>=:Z AND :Z<=:Z AND (SYS_OP_KEY_VECTOR_FILTER("L"."LO_PARTKEY",:KV0001) AND SYS_OP_KEY_VECTOR_FILTER("L"."LO_SUPPKEY",:KV0002) AND
SYS_OP_KEY_VECTOR_FILTER("L"."LO_ORDERDATE",:KV0000)))
filter((SYS_OP_KEY_VECTOR_FILTER("L"."LO_PARTKEY",:KV0001) AND SYS_OP_KEY_VECTOR_FILTER("L"."LO_SUPPKEY",:KV0002) AND
SYS_OP_KEY_VECTOR_FILTER("L"."LO_ORDERDATE",:KV0000)))
53 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))
- automatic DOP: Computed Degree of Parallelism is 4 because of degree limit
- vector transformation used for this statement
执行计划Note部分显示并行和vector transformation都用了。性能提升不明显。
In-Memory Dynamic Scan，这里是通过隐含参数强制执行的：
SQL> @10_imds.sql
SQL> -- IMDS requires at least CPU_COUNT>=24 and a RESOURCE_MANAGER_PLAN
SQL> --
SQL> alter session set "_inmemory_dynamic_scans"=force;
-- 耗时
Elapsed: 00:00:00.00
-- 执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4376 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE ALL | | 41M| 358M| 4376 (27)| 00:00:01 | 1 | 5 |
| 3 | TABLE ACCESS INMEMORY FULL| LINEORDER | 41M| 358M| 4376 (27)| 00:00:01 | 1 | 5 |
----------------------------------------------------------------------------------------------------------
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 4
IM scan (dynamic) multi-threaded scans 1
IM scan (dynamic) rows 41760941
IM scan CUs columns accessed 156
IM scan CUs memcompress for query low 78
IM scan CUs pcode aggregation pushdown 156
IM scan rows 41760941
IM scan rows pcode aggregated 41760941
IM scan rows projected 7
IM scan rows valid 41760941
session logical reads 315588
session logical reads - IM 315483
session pga memory 18090288
table scans (IM) 5
14 rows selected.
注意会话统计信息中的IM scan (dynamic) 关键字。
ADO属于Oracle数据库生命周期管理。ADO在DBIM之前就有了。详见这个实验。
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1
进入ado目录，登录数据库：
cd /home/oracle/labs/inmemory/ado
sqlplus ssb/Ora_DB4U@localhost:1521/pdb1
此实验的主要脚本执行序列如下，后面会详述：
01_ado_setup.sql
02_hm_stats.sql
03_compression_policy.sql
04_im_populated.sql
05_evaluate_policy.sql
06_part_default.sql
07_im_populated.sql
08_gen_hm_stats.sql
09_hm_stats.sql
10_evict_policy.sql
11_evaluate_policy.sql
12_ado_cleanup.sql
首先，启用ADO：
SQL> @01_ado_setup.sql
Connected.
SQL>
SQL> show parameters heat_map
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
heat_map string ON
SQL>
SQL> col name format a20;
SQL> select * from dba_ilmparameters;
NAME VALUE
-------------------- ----------
ENABLED 1
RETENTION TIME 30
JOB LIMIT 2
EXECUTION MODE 2
EXECUTION INTERVAL 15
TBS PERCENT USED 85
TBS PERCENT FREE 25
POLICY TIME 0
8 rows selected.
SQL>
SQL> exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME, dbms_ilm_admin.ILM_POLICY_IN_SECONDS);
PL/SQL procedure successfully completed.
SQL>
SQL> select * from dba_ilmparameters;
NAME VALUE
-------------------- ----------
ENABLED 1
RETENTION TIME 30
JOB LIMIT 2
EXECUTION MODE 2
EXECUTION INTERVAL 15
TBS PERCENT USED 85
TBS PERCENT FREE 25
POLICY TIME 1
8 rows selected.
查看热图(Heat Map)的信息：
SQL> @02_hm_stats.sql
Connected.
SEG SEG FULL LOOKUP NUM FULL NUM LOOKUP NUM SEG
OWNER OBJECT_NAME SUBOBJECT_NAME TRACK_TIME WRITE READ SCAN SCAN SCAN SCAN WRITE
---------- -------------------- --------------- ---------------- ---------- ---------- ---------- ---------- --------- ---------- ---------
SSB CUSTOMER 11/09/2022 10:25 NO NO YES NO 4 0 0
SSB DATE_DIM 11/09/2022 10:25 NO NO YES NO 4 0 0
SSB LINEORDER PART_1994 11/09/2022 10:25 NO NO YES NO 13 0 0
SSB LINEORDER PART_1995 11/09/2022 10:25 NO NO YES NO 13 0 0
SSB LINEORDER PART_1996 11/09/2022 10:25 NO NO YES NO 17 0 0
SSB LINEORDER PART_1997 11/09/2022 10:25 NO NO YES NO 13 0 0
SSB LINEORDER PART_1998 11/09/2022 10:25 NO NO YES NO 15 0 0
SSB PART 11/09/2022 10:25 NO NO YES NO 4 0 0
SSB SUPPLIER 11/09/2022 10:25 NO NO YES NO 4 0 0
SSB DATE_DIM 11/09/2022 11:23 NO YES YES NO 13 0 0
SSB LINEORDER PART_1994 11/09/2022 11:23 NO YES YES NO 7 0 0
SSB LINEORDER PART_1995 11/09/2022 11:23 NO YES YES NO 7 0 0
SSB LINEORDER PART_1996 11/09/2022 11:23 NO YES YES NO 7 0 0
SSB LINEORDER PART_1997 11/09/2022 11:23 NO YES YES NO 20 0 0
SSB LINEORDER PART_1998 11/09/2022 11:23 NO YES YES NO 7 0 0
SSB PART 11/09/2022 11:23 NO YES YES NO 13 0 0
SSB SUPPLIER 11/09/2022 11:23 NO YES YES NO 13 0 0
17 rows selected.
为supplier表设置压缩ILM策略，如果5天没有修改就提高压缩级：
SQL> @03_compression_policy.sql
Connected.
SQL>
SQL> alter table supplier ilm delete_all;
Table altered.
SQL> alter table supplier inmemory memcompress for query low;
Table altered.
SQL> -- exec dbms_inmemory.populate(USER, 'SUPPLIER');
SQL> select count(*) from supplier;
COUNT(*)
----------
20000
SQL> alter table supplier ilm add policy modify inmemory memcompress for capacity high after 5 days of no modification;
Table altered.
SQL>
SQL> set echo off
Hit enter ...
SQL>
SQL> select policy_name, object_owner, object_name, object_type, inherited_from, enabled, deleted
2 from user_ilmobjects;
POLICY_NAM OBJECT_OWN OBJECT_NAME OBJECT_TYP INHERITED_FROM ENABLED DELETED
---------- ---------- -------------------- ---------- -------------------- -------- --------
P1 SSB SUPPLIER TABLE POLICY NOT INHERITED YES NO
SQL>
SQL> pause Hit enter ...
Hit enter ...
SQL>
SQL> select policy_name, action_type, scope, compression_level, condition_type, condition_days,
2 policy_subtype, action_clause
3 from user_ilmdatamovementpolicies;
POLICY_NAM ACTION_TYPE SCOPE COMPRESSION_LEVEL CONDITION_TYPE CONDITION_DAYS POLICY_SUB
---------- ----------- ------- ------------------------------ ---------------------- -------------- ----------
ACTION_CLAUSE
--------------------------------------------------------------------------------
P1 COMPRESSION SEGMENT MEMCOMPRESS FOR CAPACITY HIGH LAST MODIFICATION TIME 5 INMEMORY
inmemory memcompress for capacity high
查看发布状态：
SQL> @04_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB CUSTOMER COMPLETED 24,928,256 23,199,744 0
SSB DATE_DIM COMPLETED 122,880 1,179,648 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
SSB PART COMPLETED 56,893,440 16,973,824 0
SSB SUPPLIER COMPLETED 1,769,472 2,228,224 0
9 rows selected.
执行ILM策略，发现表的压缩级确实变了。
SQL> @05_evaluate_policy.sql
Connected.
-- 将热图统计信息从内存冲刷到磁盘
SQL> exec dbms_ilm.flush_all_segments;
PL/SQL procedure successfully completed.
SQL>
SQL> select table_name, inmemory, inmemory_priority, inmemory_compression
2 from user_tables where table_name = 'SUPPLIER';
TABLE_NAME INMEMORY priority compression
--------------- -------- -------- -----------------
SUPPLIER ENABLED NONE FOR QUERY LOW
SQL>
SQL> set echo off
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB CUSTOMER COMPLETED 24,928,256 23,199,744 0
SSB DATE_DIM COMPLETED 122,880 1,179,648 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
SSB PART COMPLETED 56,893,440 16,973,824 0
SSB SUPPLIER COMPLETED 1,769,472 2,228,224 0
9 rows selected.
Hit enter ...
SQL> col policy_name new_value pnam format a10;
SQL> select policy_name from user_ilmobjects
2 where object_name = 'SUPPLIER' and object_type = 'TABLE';
POLICY_NAM
----------
P1
-- 默认是在维护窗口评估的，但这里我们主动进行评估
SQL>
SQL> variable v_execid number;
SQL>
SQL> declare
2 v_execid number;
3 begin
4 DBMS_ILM.EXECUTE_ILM (
5 owner => 'SSB',
6 object_name => 'SUPPLIER',
7 task_id => :v_execid,
8 policy_name => '&pnam',
9 execution_mode => dbms_ilm.ilm_execution_online);
10 end;
11 /
old 8: policy_name => '&pnam',
new 8: policy_name => 'P1',
PL/SQL procedure successfully completed.
SQL> set echo off
Hit enter ...
TASK_ID STATE START_TIME COMPLETION_TIME
---------- --------- ------------------------------ ------------------------------
1 COMPLETED 09-NOV-22 11.27.07.107466 AM 09-NOV-22 11.27.09.177734 AM
TASK_ID POLICY_NAM SELECTED_FOR_EXECUTION
---------- ---------- ------------------------------------------
1 P1 SELECTED FOR EXECUTION
TABLE_NAME INMEMORY priority compression
--------------- -------- -------- -----------------
SUPPLIER ENABLED NONE FOR CAPACITY HIGH
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB CUSTOMER COMPLETED 24,928,256 23,199,744 0
SSB DATE_DIM COMPLETED 122,880 1,179,648 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
SSB PART COMPLETED 56,893,440 16,973,824 0
SSB SUPPLIER COMPLETED 1,769,472 1,179,648 0
9 rows selected.
发布LINEORDER表，这是一个RANGE分区表：
SQL> @06_part_default.sql
Connected.
SQL> alter table lineorder inmemory;
Table altered.
SQL> set echo off
SQL> select pt.TABLE_NAME, pt.PARTITIONING_TYPE, pk.column_name, pt.PARTITION_COUNT, pt.STATUS, pt.DEF_INMEMORY
2 from user_part_tables pt, user_part_key_columns pk
3 where pt.table_name = pk.name
4 and pt.table_name = 'LINEORDER';
TABLE_NAME PART TYPE PART KEY COUNT STATUS DEF_INMEMORY
---------- --------------- --------------- ------ ---------- --------------------
LINEORDER RANGE LO_ORDERDATE 5 VALID ENABLED
SQL>
SQL> exec dbms_inmemory.populate(USER, 'LINEORDER');
PL/SQL procedure successfully completed.
确认已完全发布：
SQL> @07_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB CUSTOMER COMPLETED 24,928,256 23,199,744 0
SSB DATE_DIM COMPLETED 122,880 1,179,648 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
SSB PART COMPLETED 56,893,440 16,973,824 0
SSB SUPPLIER COMPLETED 1,769,472 1,179,648 0
9 rows selected.
执行针对lineorder表各分区的查询，以产生热图统计信息：
SQL> @08_gen_hm_stats.sql
Connected.
j: 1
data between: 01/01/1995 and 12/31/1995
Query count h: 1 for 01/01/1995
Query count h: 2 for 01/01/1995
Query count h: 3 for 01/01/1995
Query count h: 4 for 01/01/1995
Query count h: 5 for 01/01/1995
j: 2
data between: 01/01/1996 and 12/31/1996
Query count h: 1 for 01/01/1996
Query count h: 2 for 01/01/1996
Query count h: 3 for 01/01/1996
Query count h: 4 for 01/01/1996
Query count h: 5 for 01/01/1996
Query count h: 6 for 01/01/1996
Query count h: 7 for 01/01/1996
Query count h: 8 for 01/01/1996
Query count h: 9 for 01/01/1996
Query count h: 10 for 01/01/1996
j: 3
data between: 01/01/1997 and 12/31/1997
Query count h: 1 for 01/01/1997
Query count h: 2 for 01/01/1997
Query count h: 3 for 01/01/1997
Query count h: 4 for 01/01/1997
Query count h: 5 for 01/01/1997
Query count h: 6 for 01/01/1997
Query count h: 7 for 01/01/1997
Query count h: 8 for 01/01/1997
Query count h: 9 for 01/01/1997
Query count h: 10 for 01/01/1997
Query count h: 11 for 01/01/1997
Query count h: 12 for 01/01/1997
Query count h: 13 for 01/01/1997
Query count h: 14 for 01/01/1997
Query count h: 15 for 01/01/1997
j: 4
data between: 01/01/1998 and 12/31/1998
Query count h: 1 for 01/01/1998
Query count h: 2 for 01/01/1998
Query count h: 3 for 01/01/1998
Query count h: 4 for 01/01/1998
Query count h: 5 for 01/01/1998
Query count h: 6 for 01/01/1998
Query count h: 7 for 01/01/1998
Query count h: 8 for 01/01/1998
Query count h: 9 for 01/01/1998
Query count h: 10 for 01/01/1998
Query count h: 11 for 01/01/1998
Query count h: 12 for 01/01/1998
Query count h: 13 for 01/01/1998
Query count h: 14 for 01/01/1998
Query count h: 15 for 01/01/1998
Query count h: 16 for 01/01/1998
Query count h: 17 for 01/01/1998
Query count h: 18 for 01/01/1998
Query count h: 19 for 01/01/1998
Query count h: 20 for 01/01/1998
PL/SQL procedure successfully completed.
SQL> exec dbms_ilm.flush_all_segments;
PL/SQL procedure successfully completed.
这个生成查询的脚本如下，唯独没有查询1994年的分区：
declare
v_part_date date := to_date('01/01/1995','MM/DD/YYYY');
v_part_count number := 4;
v_increment pls_integer := 12;
v_monthcnt pls_integer := 0;
v_query_cnt pls_integer := 5;
v_totprice number;
begin
for j in 1..v_part_count loop
dbms_output.put_line('j: ' || j);
dbms_output.put_line('data between: ' || to_char(add_months( v_part_date, v_monthcnt), 'MM/DD/YYYY') || ' and ' ||
to_char( add_months( v_part_date + 30, v_monthcnt + 11), 'MM/DD/YYYY') );
--
for h in 1..(j * v_query_cnt) loop
dbms_output.put_line('Query count h: ' || h || ' for '|| to_char( add_months( v_part_date, v_monthcnt ), 'MM/DD/YYYY' ) );
select sum(lo_ordtotalprice) into v_totprice
from lineorder lo
where lo_orderdate between add_months( v_part_date, v_monthcnt ) and add_months( v_part_date + 30, v_monthcnt + 11 );
end loop;
--
v_monthcnt := v_monthcnt + v_increment;
end loop;
end;
/
--
set echo on
exec dbms_ilm.flush_all_segments;
set echo off
查看热图：
SQL> @09_hm_stats.sql
Connected.
SEG SEG FULL LOOKUP NUM FULL NUM LOOKUP NUM SEG
OWNER OBJECT_NAME SUBOBJECT_NAME TRACK_TIME WRITE READ SCAN SCAN SCAN SCAN WRITE
---------- -------------------- --------------- ---------------- ---------- ---------- ---------- ---------- --------- ---------- ---------
SSB CUSTOMER 11/09/2022 10:25 NO NO YES NO 4 0 0
SSB DATE_DIM 11/09/2022 10:25 NO NO YES NO 17 0 0
SSB LINEORDER PART_1994 11/09/2022 10:25 NO NO YES NO 20 0 0
SSB LINEORDER PART_1995 11/09/2022 10:25 NO NO YES NO 21 0 0
SSB LINEORDER PART_1996 11/09/2022 10:25 NO NO YES NO 25 0 0
SSB LINEORDER PART_1997 11/09/2022 10:25 NO NO YES NO 34 0 0
SSB LINEORDER PART_1998 11/09/2022 10:25 NO NO YES NO 23 0 0
SSB PART 11/09/2022 10:25 NO NO YES NO 17 0 0
SSB SUPPLIER 11/09/2022 10:25 NO NO YES NO 18 0 0
SSB LINEORDER PART_1995 11/09/2022 11:41 NO YES YES NO 4 0 0
SSB LINEORDER PART_1996 11/09/2022 11:41 NO YES YES NO 9 0 0
SSB LINEORDER PART_1997 11/09/2022 11:41 NO YES YES NO 14 0 0
SSB LINEORDER PART_1998 11/09/2022 11:41 NO YES YES NO 19 0 0
13 rows selected.
为lineorder表定义evict策略，分区LINEORDER(PART_1994)被从IMCS中清除。
SQL> @10_evict_policy.sql
Connected.
SQL>
SQL> -- This example assumes that the LINEORDER partition PART_1994 has not been accessed in the last 30 seconds
SQL>
SQL> alter table lineorder ilm delete_all;
Table altered.
SQL> --
SQL> -- NOTE: This policy uses 30 days to represent 30 seconds
SQL> --
SQL> alter table lineorder ilm add policy no inmemory after 30 days of no access;
Table altered.
SQL>
SQL> set echo off
Hit enter ...
SQL>
SQL> select policy_name, object_owner, object_name, object_type, inherited_from, enabled, deleted
2 from user_ilmobjects;
POLICY_NAME OWNER OBJECT_NAME OBJECT_TYPE INHERITED_FROM ENABLED DELETED
----------- ---------- --------------- --------------- -------------------- -------- --------
P1 SSB SUPPLIER TABLE POLICY NOT INHERITED NO NO
P21 SSB LINEORDER TABLE POLICY NOT INHERITED YES NO
P21 SSB LINEORDER TABLE PARTITION TABLE YES NO
P21 SSB LINEORDER TABLE PARTITION TABLE YES NO
P21 SSB LINEORDER TABLE PARTITION TABLE YES NO
P21 SSB LINEORDER TABLE PARTITION TABLE YES NO
P21 SSB LINEORDER TABLE PARTITION TABLE YES NO
7 rows selected.
SQL>
SQL> pause Hit enter ...
Hit enter ...
SQL>
SQL> select policy_name, action_type, scope, compression_level, condition_type, condition_days,
2 policy_subtype, action_clause
3 from user_ilmdatamovementpolicies;
POLICY_NAME ACTION_TYPE SCOPE COMPRESSION_LEVEL CONDITION_TYPE CONDITION_DAYS POLICY_SUB ACTION_CLAUSE
----------- ----------- ------- ------------------------------ ---------------------- -------------- ---------- --------------------
P1 COMPRESSION SEGMENT MEMCOMPRESS FOR CAPACITY HIGH LAST MODIFICATION TIME 5 INMEMORY inmemory memcompress
for capacity high
P21 EVICT SEGMENT LAST ACCESS TIME 30 INMEMORY no inmemory
评估evict策略：
SQL> @11_evaluate_policy.sql
Connected.
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB CUSTOMER COMPLETED 24,928,256 23,199,744 0
SSB DATE_DIM COMPLETED 122,880 1,179,648 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
SSB PART COMPLETED 56,893,440 16,973,824 0
SSB SUPPLIER COMPLETED 1,769,472 1,179,648 0
9 rows selected.
Hit enter ...
POLICY_NAM
----------
P21
SQL>
SQL> variable v_execid number;
SQL>
SQL> declare
2 v_execid number;
3 begin
4 DBMS_ILM.EXECUTE_ILM (
5 owner => 'SSB',
6 object_name => 'LINEORDER',
7 task_id => :v_execid,
8 policy_name => '&pnam',
9 execution_mode => dbms_ilm.ilm_execution_online);
10 end;
11 /
old 8: policy_name => '&pnam',
new 8: policy_name => 'P21',
PL/SQL procedure successfully completed.
SQL> set echo off
Hit enter ...
TASK_ID STATE START_TIME COMPLETION_TIME
---------- --------- ------------------------------ ------------------------------
2 COMPLETED 09-NOV-22 11.46.09.099598 AM 09-NOV-22 11.46.09.329048 AM
TASK_ID POLICY_NAM SELECTED_FOR_EXECUTION
---------- ---------- ------------------------------------------
2 P21 SELECTED FOR EXECUTION
2 P21 PRECONDITION NOT SATISFIED
2 P21 PRECONDITION NOT SATISFIED
2 P21 PRECONDITION NOT SATISFIED
2 P21 PRECONDITION NOT SATISFIED
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB CUSTOMER COMPLETED 24,928,256 23,199,744 0
SSB DATE_DIM COMPLETED 122,880 1,179,648 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
SSB PART COMPLETED 56,893,440 16,973,824 0
SSB SUPPLIER COMPLETED 1,769,472 1,179,648 0
8 rows selected.
最后，执行清理：
@12_ado_cleanup.sql
exec dbms_ilm_admin.CLEAR_HEAT_MAP_ALL;
背景知识：
The following values can be set:
LOW: When this value is set, the database evicts cold segments from the IM column store when it is under memory pressure.
MEDIUM: When this value is set, the database evicts cold segments from the IM column store when it is under memory pressure. This level includes an additional optimization that ensures that any hot segment that was not populated because of memory pressure is populated first.
OFF: When this value is set, Automatic In-Memory is disabled. This value returns the IM column store to the behavior that existed prior to Oracle Database 18c. If you do not expect a stable working data set, set the parameter to OFF. This is the default value.
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1
进入aim目录，登录数据库：
cd /home/oracle/labs/inmemory/aim
sqlplus ssb/Ora_DB4U@localhost:1521/pdb1
此实验的主要脚本执行序列如下，后面会详述：
01_aim_status.sql
02_disable_tables.sql
03_aim_attributes.sql
04_pop_ssb_tables.sql
05_im_populated.sql
06_aim_low.sql
07_aim_im_enable.sql
08_pop_aim_tables.sql
09_im_populated.sql
10_pop2_aim_tables.sql
11_im_populated.sql
12_hm_stats.sql
13_aimtasks.sql
14_aimtaskdetails.sql
15_aim_medium.sql
16_pop_aim_tables.sql
17_im_populated.sql
18_hm_stats.sql
19_aimtasks.sql
20_aimtaskdetails.sql
21_aim_cleanup.sql
查看AIM状态：
SQL> @01_aim_status.sql
Connected.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_automatic_level string OFF
SSB schema所有表禁止inmemory：
SQL> @02_disable_tables.sql
Connected.
alter table SSB.DATE_DIM no inmemory
alter table SSB.PART no inmemory
alter table SSB.CUSTOMER no inmemory
alter table SSB.LINEORDER modify partition PART_1996 no inmemory
alter table SSB.LINEORDER modify partition PART_1998 no inmemory
alter table SSB.LINEORDER modify partition PART_1995 no inmemory
alter table SSB.LINEORDER modify partition PART_1997 no inmemory
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
查看表的inmemory属性：
SQL> @03_aim_attributes.sql
Connected.
SQL>
SQL> -- Show table attributes
SQL>
SQL> select owner, table_name, NULL as partition_name, inmemory,
2 inmemory_priority, inmemory_distribute, inmemory_compression
3 from dba_tables
4 where owner in ('AIM','SSB')
5 UNION ALL
6 select table_owner as owner, table_name, partition_name, inmemory,
7 inmemory_priority, inmemory_distribute, inmemory_compression
8 from dba_tab_partitions
9 where table_owner in ('AIM','SSB')
10 order by owner, table_name, partition_name;
INMEMORY INMEMORY INMEMORY
OWNER TABLE_NAME PARTITION_NAME INMEMORY PRIORITY DISTRIBUTE COMPRESSION
---------- -------------------- --------------- ---------- ---------- ------------ --------------
AIM LRGTAB1 DISABLED
AIM LRGTAB2 DISABLED
AIM LRGTAB3 DISABLED
AIM LRGTAB4 DISABLED
AIM MEDTAB1 DISABLED
AIM MEDTAB2 DISABLED
AIM MEDTAB3 DISABLED
AIM SMTAB1 DISABLED
AIM SMTAB2 DISABLED
AIM SMTAB3 DISABLED
SSB CHICAGO_DATA DISABLED
SSB CUSTOMER DISABLED
SSB DATE_DIM DISABLED
SSB EXT_CUST_BULGARIA DISABLED
SSB EXT_CUST_NORWAY DISABLED
SSB JSON_PURCHASEORDER DISABLED
SSB J_PURCHASEORDER DISABLED
SSB LINEORDER PART_1994 DISABLED
SSB LINEORDER PART_1995 DISABLED
SSB LINEORDER PART_1996 DISABLED
SSB LINEORDER PART_1997 DISABLED
SSB LINEORDER PART_1998 DISABLED
SSB LINEORDER
SSB PART DISABLED
SSB SUPPLIER DISABLED
25 rows selected.
发布LINEORDER表：
SQL> @04_pop_ssb_tables.sql
Connected.
SQL>
SQL> -- Enable tables for in-memory
SQL>
SQL> alter table LINEORDER inmemory priority high;
Table altered.
SQL>
SQL> exec dbms_inmemory.populate('SSB','LINEORDER');
PL/SQL procedure successfully completed.
确认发布完成：
SQL> @05_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 where owner not in ('AUDSYS','SYS')
5 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
SQL>
SQL> select * from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------------- ----------- ---------- --------------- ----------
1MB POOL 3252682752 2191523840 DONE 3
64KB POOL 201326592 5439488 DONE 3
AIM级别设置为LOW：
SQL> @06_aim_low.sql
Connected.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_automatic_level string OFF
System altered.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_automatic_level string LOW
AIM中所有表启用inmemory：
SQL> @07_aim_im_enable.sql
Connected.
SQL>
SQL> alter table MEDTAB1 inmemory;
Table altered.
SQL> alter table MEDTAB2 inmemory;
Table altered.
SQL> alter table MEDTAB3 inmemory;
Table altered.
SQL> alter table LRGTAB1 inmemory;
Table altered.
SQL> alter table LRGTAB2 inmemory;
Table altered.
SQL> alter table LRGTAB3 inmemory;
Table altered.
SQL> alter table LRGTAB4 inmemory;
Table altered.
SQL> alter table SMTAB1 inmemory;
Table altered.
SQL> alter table SMTAB2 inmemory;
Table altered.
SQL> alter table SMTAB3 inmemory;
Table altered.
发布这些表，lrgtab4除外：
SQL> @08_pop_aim_tables.sql
Connected.
SQL>
SQL> select count(*) from lrgtab1;
COUNT(*)
----------
5000000
SQL> select count(*) from lrgtab1;
COUNT(*)
----------
5000000
SQL>
SQL> select count(*) from lrgtab2;
COUNT(*)
----------
5000000
SQL> select count(*) from lrgtab2;
COUNT(*)
----------
5000000
SQL> select count(*) from lrgtab2;
COUNT(*)
----------
5000000
SQL>
SQL> select count(*) from lrgtab3;
COUNT(*)
----------
5000000
SQL> select count(*) from medtab1;
COUNT(*)
----------
300000
SQL> select count(*) from medtab1;
COUNT(*)
----------
300000
SQL> select count(*) from medtab1;
COUNT(*)
----------
300000
SQL> select count(*) from medtab2;
COUNT(*)
----------
300000
SQL> select count(*) from medtab2;
COUNT(*)
----------
300000
SQL> select count(*) from medtab2;
COUNT(*)
----------
300000
确认发布完成，此时内存快满了（但没有满）：
SQL> @09_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 where owner not in ('AUDSYS','SYS')
5 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
AIM LRGTAB1 COMPLETED 575,168,512 269,156,352 0
AIM LRGTAB2 COMPLETED 575,168,512 269,156,352 0
AIM LRGTAB3 COMPLETED 575,168,512 269,156,352 0
AIM MEDTAB1 COMPLETED 38,322,176 23,199,744 0
AIM MEDTAB2 COMPLETED 38,322,176 23,199,744 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
10 rows selected.
SQL>
SQL> select * from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------------- ----------- ---------- --------------- ----------
1MB POOL 3252682752 3042967552 DONE 3
64KB POOL 201326592 7864320 DONE 3
此时发布lrgtab4表：
SQL> @10_pop2_aim_tables.sql
Connected.
SQL>
SQL> select count(*) from lrgtab4;
COUNT(*)
----------
5000000
SQL> select count(*) from lrgtab4;
COUNT(*)
----------
5000000
SQL> select count(*) from lrgtab4;
COUNT(*)
----------
5000000
SQL> select count(*) from lrgtab4;
COUNT(*)
----------
5000000
查看发布状态，此时有OUT OF INMEMORY现象：
SQL> @11_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 where owner not in ('AUDSYS','SYS')
5 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
AIM LRGTAB1 COMPLETED 575,168,512 269,156,352 0
AIM LRGTAB2 COMPLETED 575,168,512 269,156,352 0
AIM LRGTAB3 COMPLETED 575,168,512 269,156,352 0
AIM LRGTAB4 OUT OF MEMORY 575,168,512 187,170,816 175,489,024
AIM MEDTAB1 COMPLETED 38,322,176 23,199,744 0
AIM MEDTAB2 COMPLETED 38,322,176 23,199,744 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
11 rows selected.
SQL>
SQL> select * from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------------- ----------- ---------- --------------- ----------
1MB POOL 3252682752 3229614080 DONE 3
64KB POOL 201326592 8388608 DONE 3
查看热图，LRGTAB4比较热，而LRGTAB3较冷：
SQL> @12_hm_stats.sql
Connected.
PL/SQL procedure successfully completed.
SEG SEG FULL LOOKUP NUM FULL NUM LOOKUP NUM SEG
OWNER OBJECT_NAME SUBOBJECT_NAME TRACK_TIME WRITE READ SCAN SCAN SCAN SCAN WRITE
---------- -------------------- --------------- ---------------- ---------- ---------- ---------- ---------- --------- ---------- ---------
AIM LRGTAB1 11/09/2022 12:38 NO NO YES NO 2 0 0
AIM LRGTAB2 11/09/2022 12:38 NO NO YES NO 3 0 0
AIM LRGTAB3 11/09/2022 12:38 NO NO YES NO 1 0 0
AIM LRGTAB4 11/09/2022 12:38 NO NO YES NO 4 0 0
AIM MEDTAB1 11/09/2022 12:38 NO NO YES NO 3 0 0
AIM MEDTAB2 11/09/2022 12:38 NO NO YES NO 3 0 0
6 rows selected.
查看AIM任务：
SQL> @13_aimtasks.sql
Connected.
TASK_ID CREATE_TIME STATE
---------- --------------------------- -------
2 09-NOV-22 12:38:30 DONE
查看AIM任务详情，LRGTAB3被清除出去了：
SQL> @14_aimtaskdetails.sql
Connected.
Enter value for 1: 2
old 1: select * from dba_inmemory_aimtaskdetails where task_id = &1
new 1: select * from dba_inmemory_aimtaskdetails where task_id = 2
TASK_ID OBJECT_OWNER OBJECT_NAME SUBOBJECT_NAME ACTION STATE
---------- --------------- ------------------------------ ------------------------------ ---------------- ----------
2 AIM LRGTAB1 NO ACTION DONE
2 AIM LRGTAB2 NO ACTION DONE
2 AIM LRGTAB3 EVICT DONE
2 AIM LRGTAB4 NO ACTION DONE
2 AIM MEDTAB1 NO ACTION DONE
2 AIM MEDTAB2 NO ACTION DONE
2 AIM MEDTAB3 NO ACTION DONE
2 AIM SMTAB1 NO ACTION DONE
2 AIM SMTAB2 NO ACTION DONE
2 AIM SMTAB3 NO ACTION DONE
2 SSB LINEORDER PART_1994 NO ACTION DONE
2 SSB LINEORDER PART_1995 NO ACTION DONE
2 SSB LINEORDER PART_1996 NO ACTION DONE
2 SSB LINEORDER PART_1997 NO ACTION DONE
2 SSB LINEORDER PART_1998 NO ACTION DONE
15 rows selected.
确认：
SQL> @11_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 where owner not in ('AUDSYS','SYS')
5 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
AIM LRGTAB1 COMPLETED 575,168,512 269,156,352 0
AIM LRGTAB2 COMPLETED 575,168,512 269,156,352 0
AIM LRGTAB4 COMPLETED 575,168,512 269,156,352 0
AIM MEDTAB1 COMPLETED 38,322,176 23,199,744 0
AIM MEDTAB2 COMPLETED 38,322,176 23,199,744 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
10 rows selected.
SQL>
SQL> select * from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------------- ----------- ---------- --------------- ----------
1MB POOL 3252682752 3042967552 DONE 3
64KB POOL 201326592 7864320 DONE 3
设置AIM级别中级：
SQL> @15_aim_medium.sql
Connected.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_automatic_level string LOW
System altered.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_automatic_level string MEDIUM
多次全表扫描lrgtab3，让其变热：
SQL> @16_pop_aim_tables.sql
Connected.
SQL>
SQL> select count(*) from lrgtab3;
COUNT(*)
----------
5000000
SQL> select count(*) from lrgtab3;
COUNT(*)
----------
5000000
SQL> select count(*) from lrgtab3;
COUNT(*)
----------
5000000
SQL> select count(*) from lrgtab3;
COUNT(*)
----------
5000000
SQL> select count(*) from lrgtab3;
COUNT(*)
----------
5000000
SQL> select count(*) from lrgtab3;
COUNT(*)
----------
5000000
由于内存不够，所以lrgtab3出现OUT OF INMEMORY现象：
SQL> @05_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 where owner not in ('AUDSYS','SYS')
5 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
AIM LRGTAB1 COMPLETED 575,168,512 269,156,352 0
AIM LRGTAB2 COMPLETED 575,168,512 269,156,352 0
AIM LRGTAB3 OUT OF MEMORY 575,168,512 187,170,816 175,472,640
AIM LRGTAB4 COMPLETED 575,168,512 272,302,080 0
AIM MEDTAB1 COMPLETED 38,322,176 23,199,744 0
AIM MEDTAB2 COMPLETED 38,322,176 23,199,744 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
11 rows selected.
SQL>
SQL> select * from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------------- ----------- ---------- --------------- ----------
1MB POOL 3252682752 3232759808 DONE 3
64KB POOL 201326592 8388608 DONE 3
这地方，你要有点耐心。多执行几次16_pop_aim_tables.sql，最终lrgtab3表就能全部进入In-Memory：
SQL> @05_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 where owner not in ('AUDSYS','SYS')
5 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
AIM LRGTAB1 OUT OF MEMORY 575,168,512 187,170,816 175,472,640
AIM LRGTAB2 COMPLETED 575,168,512 269,156,352 0
AIM LRGTAB3 COMPLETED 575,168,512 269,156,352 0
AIM LRGTAB4 COMPLETED 575,168,512 272,302,080 0
AIM MEDTAB1 COMPLETED 38,322,176 23,199,744 0
AIM MEDTAB2 COMPLETED 38,322,176 23,199,744 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 478,281,728 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 478,281,728 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 480,378,880 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 479,330,304 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 280,690,688 0
11 rows selected.
SQL>
SQL> select * from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-------------------------- ----------- ---------- --------------- ----------
1MB POOL 3252682752 3232759808 DONE 3
64KB POOL 201326592 8388608 DONE 3
查看热图信息，目前LRGTAB3最热，LRGTAB1最冷：
SQL> @18_hm_stats.sql
Connected.
PL/SQL procedure successfully completed.
SEG SEG FULL LOOKUP NUM FULL NUM LOOKUP NUM SEG
OWNER OBJECT_NAME SUBOBJECT_NAME TRACK_TIME WRITE READ SCAN SCAN SCAN SCAN WRITE
---------- -------------------- --------------- ---------------- ---------- ---------- ---------- ---------- --------- ---------- ---------
AIM LRGTAB1 11/09/2022 12:38 NO NO YES NO 2 0 0
AIM LRGTAB2 11/09/2022 12:38 NO NO YES NO 3 0 0
AIM LRGTAB3 11/09/2022 12:38 NO NO YES NO 7 0 0
AIM LRGTAB4 11/09/2022 12:38 NO NO YES NO 4 0 0
AIM MEDTAB1 11/09/2022 12:38 NO NO YES NO 3 0 0
AIM MEDTAB2 11/09/2022 12:38 NO NO YES NO 3 0 0
6 rows selected.
查看AIM任务及详情，发现LRGTAB3发布成功了，因为LRGTAB1被清除出去了：
SQL> @19_aimtasks.sql
Connected.
TASK_ID CREATE_TIME STATE
---------- --------------------------- -------
2 09-NOV-22 12:38:30 DONE
3 09-NOV-22 12:44:31 DONE
4 09-NOV-22 12:46:32 DONE
5 09-NOV-22 12:48:32 DONE
6 09-NOV-22 12:50:32 DONE
SQL> @20_aimtaskdetails.sql
Connected.
Enter value for 1: 6
old 1: select * from dba_inmemory_aimtaskdetails where task_id = &1
new 1: select * from dba_inmemory_aimtaskdetails where task_id = 6
TASK_ID OBJECT_OWNER OBJECT_NAME SUBOBJECT_NAME ACTION STATE
---------- --------------- ------------------------------ ------------------------------ ---------------- ----------
6 AIM LRGTAB1 NO ACTION DONE
6 AIM LRGTAB2 POPULATE DONE
6 AIM LRGTAB3 POPULATE DONE
6 AIM LRGTAB4 POPULATE DONE
6 AIM MEDTAB1 POPULATE DONE
6 AIM MEDTAB2 POPULATE DONE
6 AIM MEDTAB3 NO ACTION DONE
6 AIM SMTAB1 NO ACTION DONE
6 AIM SMTAB2 NO ACTION DONE
6 AIM SMTAB3 NO ACTION DONE
6 SSB LINEORDER PART_1994 POPULATE DONE
6 SSB LINEORDER PART_1995 POPULATE DONE
6 SSB LINEORDER PART_1996 POPULATE DONE
6 SSB LINEORDER PART_1997 POPULATE DONE
6 SSB LINEORDER PART_1998 POPULATE DONE
15 rows selected.
最后，清理：
SQL> @21_aim_cleanup.sql
alter system set inmemory_automatic_level=off;
alter table lineorder no inmemory;
alter table AIM.MEDTAB2 no inmemory
alter table AIM.SMTAB3 no inmemory
alter table AIM.LRGTAB3 no inmemory
alter table AIM.LRGTAB2 no inmemory
alter table AIM.LRGTAB1 no inmemory
alter table AIM.SMTAB1 no inmemory
alter table AIM.MEDTAB1 no inmemory
alter table AIM.MEDTAB3 no inmemory
alter table AIM.LRGTAB4 no inmemory
alter table AIM.SMTAB2 no inmemory
PL/SQL procedure successfully completed.
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1
进入join-groups目录，登录数据库：
cd /home/oracle/labs/inmemory/join-groups
sqlplus ssb/Ora_DB4U@localhost:1521/pdb1
此实验的主要脚本执行序列如下，后面会详述：
01_use_nojg.sql
02_create_jg.sql
03_im_populated.sql
04_query_jg.sql
05_use_jg.sql
05_use_jg_xml.sql
06_jg_cleanup.sql
sqlmon_joingroup.html
在实验前，请确保SSB schema的5张表均已发布。若没有，请执行setup目录中的以下脚本：
@04_im_alter_table.sql
@06_im_start_pop.sql
@08_im_populated.sql
没有Join Group前的查询：
SQL> @01_use_nojg.sql
-- 耗时
Elapsed: 00:00:05.68
-- 执行计划
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 80042 (100)| | | |
| 1 | HASH GROUP BY | | 7 | 231 | | 80042 (7)| 00:00:04 | | |
|* 2 | HASH JOIN | | 1675 | 55275 | | 80041 (7)| 00:00:04 | | |
| 3 | VIEW | VW_GBC_17 | 1675 | 35175 | | 80040 (7)| 00:00:04 | | |
| 4 | HASH GROUP BY | | 1675 | 56950 | | 80040 (7)| 00:00:04 | | |
|* 5 | HASH JOIN | | 41M| 1345M| | 77432 (4)| 00:00:04 | | |
| 6 | TABLE ACCESS INMEMORY FULL | SUPPLIER | 20000 | 97K| | 3 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 41M| 1154M| 12M| 77198 (3)| 00:00:04 | | |
| 8 | TABLE ACCESS INMEMORY FULL | PART | 800K| 3906K| | 77 (8)| 00:00:01 | | |
| 9 | PARTITION RANGE ALL | | 41M| 955M| | 4928 (35)| 00:00:01 | 1 | 5 |
| 10 | TABLE ACCESS INMEMORY FULL| LINEORDER | 41M| 955M| | 4928 (35)| 00:00:01 | 1 | 5 |
| 11 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 2556 | 30672 | | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="D"."D_DATEKEY")
5 - access("L"."LO_SUPPKEY"="S"."S_SUPPKEY")
7 - access("L"."LO_PARTKEY"="P"."P_PARTKEY")
33 rows selected.
Join Group Usage:
-----------------
2 - join group was observed on 1 process(es)
5 - join group was leveraged on 1 process(es)
7 - join group was leveraged on 1 process(es)
--
创建Join Group，并重新发布表：
SQL> @02_create_jg.sql
Connected.
SQL>
SQL> -- This script will create Join Groups in the In-Memory Column Store
SQL>
SQL> alter table lineorder no inmemory;
Table altered.
SQL> alter table part no inmemory;
Table altered.
SQL> alter table supplier no inmemory;
Table altered.
SQL> alter table date_dim no inmemory;
Table altered.
SQL>
SQL> CREATE INMEMORY JOIN GROUP lineorder_jg1 ( lineorder(lo_orderdate), date_dim(d_datekey));
Join group created.
SQL> CREATE INMEMORY JOIN GROUP lineorder_jg2 ( lineorder(lo_partkey), part(p_partkey));
Join group created.
SQL> CREATE INMEMORY JOIN GROUP lineorder_jg3 ( lineorder(lo_suppkey), supplier(s_suppkey));
Join group created.
SQL>
SQL> alter table LINEORDER inmemory;
Table altered.
SQL> alter table PART inmemory;
Table altered.
SQL> alter table SUPPLIER inmemory;
Table altered.
SQL> alter table DATE_DIM inmemory;
Table altered.
SQL>
SQL> select /*+ full(LINEORDER) noparallel(LINEORDER) */ count(*) from LINEORDER;
COUNT(*)
----------------
41760941
SQL> select /*+ full(PART) noparallel(PART) */ count(*) from PART;
COUNT(*)
----------------
800000
SQL> select /*+ full(CUSTOMER) noparallel(CUSTOMER) */ count(*) from CUSTOMER;
COUNT(*)
----------------
300000
SQL> select /*+ full(SUPPLIER) noparallel(SUPPLIER) */ count(*) from SUPPLIER;
COUNT(*)
----------------
20000
SQL> select /*+ full(DATE_DIM) noparallel(DATE_DIM) */ count(*) from DATE_DIM;
COUNT(*)
----------------
2556
确认发布完成：
SQL> @03_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB CUSTOMER COMPLETED 24,928,256 23,199,744 0
SSB DATE_DIM COMPLETED 122,880 1,179,648 0
SSB LINEORDER PART_1994 COMPLETED 563,609,600 496,107,520 0
SSB LINEORDER PART_1995 COMPLETED 563,470,336 496,107,520 0
SSB LINEORDER PART_1996 COMPLETED 565,018,624 497,156,096 0
SSB LINEORDER PART_1997 COMPLETED 563,322,880 496,107,520 0
SSB LINEORDER PART_1998 COMPLETED 329,015,296 289,079,296 0
SSB PART COMPLETED 56,893,440 20,119,552 0
SSB SUPPLIER COMPLETED 1,769,472 2,228,224 0
9 rows selected.
显示Join Group信息：
SQL> @04_query_jg.sql
Connected.
USER_JOINGROUP QUERY
JOINGROUP_NAME TABLE_NAME COLUMN_NAME GD_ADDRESS
------------------ --------------- --------------- ----------------
LINEORDER_JG1 DATE_DIM D_DATEKEY 000000024BEFFFE0
LINEORDER_JG1 LINEORDER LO_ORDERDATE 000000024BEFFFE0
LINEORDER_JG2 LINEORDER LO_PARTKEY 000000024AFFFFE0
LINEORDER_JG2 PART P_PARTKEY 000000024AFFFFE0
LINEORDER_JG3 SUPPLIER S_SUPPKEY 000000024BCFFFE0
LINEORDER_JG3 LINEORDER LO_SUPPKEY 000000024BCFFFE0
6 rows selected.
启用Join Group后的查询：
SQL>
@05_use_jg.sql
-- 耗时
Elapsed: 00:00:06.58
-- 执行计划
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 80042 (100)| | | |
| 1 | HASH GROUP BY | | 7 | 231 | | 80042 (7)| 00:00:04 | | |
|* 2 | HASH JOIN | | 1675 | 55275 | | 80041 (7)| 00:00:04 | | |
| 3 | VIEW | VW_GBC_17 | 1675 | 35175 | | 80040 (7)| 00:00:04 | | |
| 4 | HASH GROUP BY | | 1675 | 56950 | | 80040 (7)| 00:00:04 | | |
|* 5 | HASH JOIN | | 41M| 1345M| | 77432 (4)| 00:00:04 | | |
| 6 | TABLE ACCESS INMEMORY FULL | SUPPLIER | 20000 | 97K| | 3 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 41M| 1154M| 12M| 77198 (3)| 00:00:04 | | |
| 8 | TABLE ACCESS INMEMORY FULL | PART | 800K| 3906K| | 77 (8)| 00:00:01 | | |
| 9 | PARTITION RANGE ALL | | 41M| 955M| | 4928 (35)| 00:00:01 | 1 | 5 |
| 10 | TABLE ACCESS INMEMORY FULL| LINEORDER | 41M| 955M| | 4928 (35)| 00:00:01 | 1 | 5 |
| 11 | TABLE ACCESS INMEMORY FULL | DATE_DIM | 2556 | 30672 | | 1 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="D"."D_DATEKEY")
5 - access("L"."LO_SUPPKEY"="S"."S_SUPPKEY")
7 - access("L"."LO_PARTKEY"="P"."P_PARTKEY")
33 rows selected.
Join Group Usage:
-----------------
2 - join group was observed on 1 process(es)
5 - join group was leveraged on 1 process(es)
7 - join group was leveraged on 1 process(es)
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 696
IM scan CUs columns accessed 317
IM scan CUs memcompress for query low 82
IM scan rows 42583497
IM scan rows projected 42583497
IM scan rows valid 42583497
session logical reads 326976
session logical reads - IM 322659
session pga memory 35505800
table scans (IM) 8
10 rows selected.
执行计划中的Join Group Usage中的信息表明Join Group已使用。
在SQL Monitor报告中的以下信息也可以证明Join Group已使用：
Columnar Encodings Leveraged: 1
删除Join Group：
SQL> @06_jg_cleanup.sql
Connected.
Table altered.
Join group deleted.
Join group deleted.
Join group deleted.
PL/SQL procedure successfully completed.
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1
进入ext-tab目录，登录数据库：
cd /home/oracle/labs/inmemory/ext-tab
sqlplus ssb/Ora_DB4U@localhost:1521/pdb1
此实验的主要脚本执行序列如下，后面会详述：
01_ext_dir.sql
02_create_ext.sql
03_ext_query.sql
04_pop_ext.sql
05_im_populated.sql
06_ext_query.sql
07_create_ext_part.sql
08_pop_ext_part.sql
09_im_populated.sql
10_ext_part_def.sql
11_ext_part_query.sql
12_create_hybrid_part.sql
13_pop_hybrid_part.sql
14_im_populated.sql
15_hybrid_part_def.sql
16_hybrid_part_query.sql
17_ext_cleanup.sql
确认目录对象EXT_DIR不存在：
SQL> @01_ext_dir.sql
Connected.
SQL>
SQL> -- External table query
SQL> col owner format a10;
SQL> col directory_name format a10;
SQL> col directory_path format a30;
SQL> select * from all_directories where directory_name = 'EXT_DIR';
no rows selected
然后创建目录对象EXT_DIR：
SQL> create directory ext_dir as '/home/oracle/labs/inmemory/ext-tab/ext_tables';
Directory created.
创建外部表ext_cust：
SQL> @02_create_ext.sql
Connected.
SQL>
SQL> -- Create external table
SQL>
SQL> drop table ext_cust;
drop table ext_cust
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> create table ext_cust
2 ( custkey number, name varchar2(25), address varchar2(26), city varchar2(24), nation varchar2(19) )
3 organization external
4 ( type oracle_loader
5 default directory ext_dir
6 access parameters (
7 records delimited by newline
8 fields terminated by '%'
9 missing field values are null
10 ( custkey, name, address, city, nation ) )
11 location ('ext_cust.csv') )
12 reject limit unlimited;
Table created.
SQL>
SQL> set lines 80
SQL> desc ext_cust
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTKEY NUMBER
NAME VARCHAR2(25)
ADDRESS VARCHAR2(26)
CITY VARCHAR2(24)
NATION VARCHAR2(19)
查询外部表：
SQL> @03_ext_query.sql
-- 耗时
Elapsed: 00:00:00.05
-- 执行计划
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 347 (100)| |
| 1 | HASH GROUP BY | | 102K| 1096K| 347 (3)| 00:00:01 |
| 2 | EXTERNAL TABLE ACCESS FULL| EXT_CUST | 102K| 1096K| 342 (1)| 00:00:01 |
----------------------------------------------------------------------------------------
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 8
session logical reads 651
session pga memory 20777264
发布外部表：
SQL> @04_pop_ext.sql
SQL> alter table ext_cust inmemory;
SQL> exec dbms_inmemory.populate(USER,'EXT_CUST');
确认发布完成：
SQL> @05_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB EXT_CUST COMPLETED 0 1,179,648 0
发布后，再次查询：
SQL> @06_ext_query.sql
-- 耗时
Elapsed: 00:00:00.11
-- 执行计划
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30 (100)| |
| 1 | HASH GROUP BY | | 8168 | 89848 | 30 (4)| 00:00:01 |
| 2 | EXTERNAL TABLE ACCESS INMEMORY FULL| EXT_CUST | 8168 | 89848 | 29 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 23
IM scan CUs columns accessed 1
IM scan CUs memcompress for query low 1
IM scan CUs pcode aggregation pushdown 1
IM scan rows 12007
IM scan rows pcode aggregated 12007
IM scan rows projected 5
IM scan rows valid 12007
session logical reads 2580
session pga memory 19401008
table scans (IM) 1
11 rows selected.
接下来看外部分区表，为列表分区：
SQL> @07_create_ext_part.sql
Connected.
SQL>
SQL> -- Create external table
SQL>
SQL> drop table ext_cust_part;
drop table ext_cust_part
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> create table ext_cust_part
2 (
3 custkey number,
4 name varchar2(25),
5 address varchar2(26),
6 city varchar2(24),
7 nation varchar2(19)
8 )
9 organization external
10 (
11 type oracle_loader
12 default directory ext_dir
13 access parameters (
14 records delimited by newline
15 fields terminated by '%'
16 missing field values are null
17 (
18 custkey,
19 name,
20 address,
21 city,
22 nation
23 )
24 )
25 )
26 reject limit unlimited
27 partition by list (nation)
28 (
29 partition n1 values('RUSSIA') location ('ext_cust_russia.csv'),
30 partition n2 values('JAPAN') location ('ext_cust_japan.csv'),
31 partition n3 values('VIETNAM') location ('ext_cust_vietnam.csv'),
32 partition n4 values('ALGERIA') location ('ext_cust_algeria.csv'),
33 partition n5 values('CHINA') location ('ext_cust_china.csv')
34 );
Table created.
SQL>
SQL> set lines 100
SQL> col table_name format a20;
SQL> col partition_name format a20;
SQL> col high_value format a15;
SQL> select TABLE_NAME, PARTITION_NAME, high_value, PARTITION_POSITION, INMEMORY
2 from user_tab_partitions where table_name = 'EXT_CUST_PART';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION INMEMORY
-------------------- -------------------- --------------- -------------------- --------
EXT_CUST_PART N1 'RUSSIA' 1
EXT_CUST_PART N2 'JAPAN' 2
EXT_CUST_PART N3 'VIETNAM' 3
EXT_CUST_PART N4 'ALGERIA' 4
EXT_CUST_PART N5 'CHINA' 5
发布外部分区表：
SQL> @08_pop_ext_part.sql
Connected.
SQL>
SQL> alter table ext_cust no inmemory;
Table altered.
SQL>
SQL> alter table ext_cust_part inmemory;
Table altered.
SQL>
SQL> exec dbms_inmemory.populate(USER,'EXT_CUST_PART');
PL/SQL procedure successfully completed.
确认已发布：
SQL> @09_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB EXT_CUST_PART N1 COMPLETED 0 1,179,648 0
SSB EXT_CUST_PART N2 COMPLETED 0 1,179,648 0
SSB EXT_CUST_PART N3 COMPLETED 0 1,179,648 0
SSB EXT_CUST_PART N4 COMPLETED 0 1,179,648 0
SSB EXT_CUST_PART N5 COMPLETED 0 1,179,648 0
查看外部表的定义：
SQL> @10_ext_part_def.sql
Connected.
SQL>
SQL> col table_name format a30;
SQL> col partition_name format a20;
SQL> col high_value format a10;
SQL> --
SQL> select
2 TABLE_NAME,
3 PARTITION_NAME,
4 high_value,
5 PARTITION_POSITION,
6 INMEMORY
7 from
8 user_tab_partitions
9 where
10 table_name = 'EXT_CUST_PART';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION INMEMORY
------------------------------ -------------------- ---------- -------------------- --------
EXT_CUST_PART N1 'RUSSIA' 1
EXT_CUST_PART N2 'JAPAN' 2
EXT_CUST_PART N3 'VIETNAM' 3
EXT_CUST_PART N4 'ALGERIA' 4
EXT_CUST_PART N5 'CHINA' 5
Elapsed: 00:00:00.04
SQL>
SQL> pause Hit enter ...
Hit enter ...
SQL>
SQL> select
2 tp.TABLE_NAME,
3 tp.PARTITION_NAME,
4 tp.HIGH_VALUE,
5 tp.PARTITION_POSITION,
6 DECODE(tp.INMEMORY,null,xtp.INMEMORY,tp.INMEMORY) INMEMORY
7 from
8 user_tab_partitions tp,
9 user_xternal_tab_partitions xtp
10 where
11 tp.table_name = xtp.table_name(+)
12 and tp.partition_name = xtp.partition_name(+)
13 and tp.table_name = 'EXT_CUST_PART';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION INMEMORY
------------------------------ -------------------- ---------- -------------------- --------
EXT_CUST_PART N2 'JAPAN' 2 ENABLED
EXT_CUST_PART N3 'VIETNAM' 3 ENABLED
EXT_CUST_PART N4 'ALGERIA' 4 ENABLED
EXT_CUST_PART N5 'CHINA' 5 ENABLED
EXT_CUST_PART N1 'RUSSIA' 1 ENABLED
外部分区表在inmemory中的查询：
SQL> @11_ext_part_query.sql
-- 耗时
Elapsed: 00:00:00.12
-- 执行计划
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1728 (100)| | | |
| 1 | PARTITION LIST ALL | | 510K| 5483K| 1728 (2)| 00:00:01 | 1 | 5 |
| 2 | HASH GROUP BY | | 510K| 5483K| 1728 (2)| 00:00:01 | | |
| 3 | EXTERNAL TABLE ACCESS INMEMORY FULL| EXT_CUST_PART | 510K| 5483K| 1703 (1)| 00:00:01 | 1 | 5 |
-----------------------------------------------------------------------------------------------------------------------
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 17
IM scan CUs columns accessed 5
IM scan CUs memcompress for query low 5
IM scan CUs pcode aggregation pushdown 5
IM scan rows 12007
IM scan rows pcode aggregated 12007
IM scan rows projected 5
IM scan rows valid 12007
session logical reads 1538
session pga memory 18811184
table scans (IM) 5
11 rows selected.
接下来创建混合分区表，为列表分区。其中N1和N2为外部分区：
SQL> @12_create_hybrid_part.sql
Connected.
SQL>
SQL> -- Create external table
SQL>
SQL> drop table ext_cust_hybrid_part;
drop table ext_cust_hybrid_part
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> create table ext_cust_hybrid_part
2 (
3 custkey number,
4 name varchar2(25),
5 address varchar2(26),
6 city varchar2(24),
7 nation varchar2(19)
8 )
9 external partition attributes
10 (
11 type oracle_loader
12 default directory ext_dir
13 access parameters (
14 records delimited by newline
15 fields terminated by '%'
16 missing field values are null
17 (
18 custkey,
19 name,
20 address,
21 city,
22 nation
23 )
24 )
25 reject limit unlimited
26 )
27 partition by list (nation)
28 (
29 partition n1 values('RUSSIA') external location ('ext_cust_russia.csv'),
30 partition n2 values('JAPAN') external location ('ext_cust_japan.csv'),
31 partition n3 values('BULGARIA'),
32 partition n4 values('NORWAY')
33 );
Table created.
SQL> select nation, count(*) from EXT_CUST_HYBRID_PART group by nation;
NATION COUNT(*)
------------------- --------------------
RUSSIA 2463
JAPAN 2413
SQL> insert into ext_cust_hybrid_part select * from ext_cust_bulgaria;
2360 rows created.
SQL> insert into ext_cust_hybrid_part select * from ext_cust_norway;
2360 rows created.
SQL> commit;
SQL> select nation, count(*) from EXT_CUST_HYBRID_PART group by nation;
NATION COUNT(*)
------------------- --------------------
RUSSIA 2463
JAPAN 2413
BULGARIA 2360
NORWAY 2360
设置inmemory属性并发布混合分区表：
SQL> @13_pop_hybrid_part.sql
Connected.
SQL>
SQL> alter table ext_cust_part no inmemory;
Table altered.
Elapsed: 00:00:00.02
SQL>
SQL> alter table EXT_CUST_HYBRID_PART inmemory;
Table altered.
Elapsed: 00:00:00.00
SQL>
SQL> exec dbms_inmemory.populate(USER,'EXT_CUST_HYBRID_PART');
PL/SQL procedure successfully completed.
查看发布状态：
SQL> @14_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB EXT_CUST_HYBRID_PART N1 COMPLETED 0 1,179,648 0
SSB EXT_CUST_HYBRID_PART N2 COMPLETED 0 1,179,648 0
SSB EXT_CUST_HYBRID_PART N3 COMPLETED 8,241,152 1,179,648 0
SSB EXT_CUST_HYBRID_PART N4 COMPLETED 8,241,152 1,179,648 0
查看混合分区表的定义：
SQL> @15_hybrid_part_def.sql
Connected.
SQL> --
SQL> select
2 TABLE_NAME,
3 PARTITION_NAME,
4 high_value,
5 PARTITION_POSITION,
6 INMEMORY
7 from
8 user_tab_partitions
9 where
10 table_name = 'EXT_CUST_HYBRID_PART';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION INMEMORY
------------------------------ -------------------- ---------- -------------------- --------
EXT_CUST_HYBRID_PART N1 'RUSSIA' 1
EXT_CUST_HYBRID_PART N2 'JAPAN' 2
EXT_CUST_HYBRID_PART N3 'BULGARIA' 3 ENABLED
EXT_CUST_HYBRID_PART N4 'NORWAY' 4 ENABLED
Elapsed: 00:00:00.04
SQL>
SQL> pause Hit enter ...
Hit enter ...
SQL>
SQL> select
2 tp.TABLE_NAME,
3 tp.PARTITION_NAME,
4 tp.HIGH_VALUE,
5 tp.PARTITION_POSITION,
6 DECODE(tp.INMEMORY,null,xtp.INMEMORY,tp.INMEMORY) INMEMORY
7 from
8 user_tab_partitions tp,
9 user_xternal_tab_partitions xtp
10 where
11 tp.table_name = xtp.table_name(+)
12 and tp.partition_name = xtp.partition_name(+)
13 and tp.table_name = 'EXT_CUST_HYBRID_PART';
TABLE_NAME PARTITION_NAME HIGH_VALUE PARTITION_POSITION INMEMORY
------------------------------ -------------------- ---------- -------------------- --------
EXT_CUST_HYBRID_PART N1 'RUSSIA' 1 ENABLED
EXT_CUST_HYBRID_PART N2 'JAPAN' 2 ENABLED
EXT_CUST_HYBRID_PART N3 'BULGARIA' 3 ENABLED
EXT_CUST_HYBRID_PART N4 'NORWAY' 4 ENABLED
针对混合分区表的inmemory查询，注意执行计划中的HYBRID 关键字：
SQL>
@16_hybrid_part_query.sql
-- 耗时
Elapsed: 00:00:00.06
-- 执行计划
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 81 (100)| | | |
| 1 | PARTITION LIST ALL | | 180K| 1940K| 81 (5)| 00:00:01 | 1 | 4 |
| 2 | HASH GROUP BY | | 180K| 1940K| 81 (5)| 00:00:01 | | |
| 3 | TABLE ACCESS HYBRID PART INMEMORY FULL| EXT_CUST_HYBRID_PART | 180K| 1940K| 79 (3)| 00:00:01 | 1 | 4 |
| 4 | TABLE ACCESS INMEMORY FULL | EXT_CUST_HYBRID_PART | | | | | 1 | 4 |
---------------------------------------------------------------------------------------------------------------------------------
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 9
IM scan CUs columns accessed 4
IM scan CUs memcompress for query low 4
IM scan CUs pcode aggregation pushdown 4
IM scan rows 9596
IM scan rows pcode aggregated 9596
IM scan rows projected 4
IM scan rows valid 9596
IM scan segments minmax eligible 2
session logical reads 3315
session logical reads - IM 2012
session pga memory 18680112
table scans (IM) 4
13 rows selected.
清理：
SQL> @17_ext_cleanup.sql
Connected.
SQL>
SQL> alter table ext_cust no inmemory;
Table altered.
SQL> alter table ext_cust_part no inmemory;
Table altered.
SQL> alter table EXT_CUST_HYBRID_PART no inmemory;
Table altered.
SQL>
SQL> drop table ext_cust purge;
Table dropped.
SQL> drop table ext_cust_part purge;
Table dropped.
SQL> drop table EXT_CUST_HYBRID_PART purge;
Table dropped.
本实验还可以参考：Create and Load Partitions in In-Memory Hybrid Partitioned Tables
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1
进入im-spatial目录，登录数据库：
cd /home/oracle/labs/inmemory/im-spatial
sqlplus ssb/Ora_DB4U@localhost:1521/pdb1
此实验的主要脚本执行序列如下，后面会详述：
01_city_points.sql
02_desc_city_points.sql
03_query.sql
04_spatial_index.sql
05_im_pop.sql
06_im_populated.sql
07_im_query.sql
08_im_spatial.sql
09_im_populated.sql
10_spatial_ime.sql
11_spatial_query.sql
12_spatial_cleanup.sql
创建Spatial表并插入数据。此表只有4条数据，所以目的不在比较性能：
SQL> @01_city_points.sql
Connected.
SQL>
SQL> CREATE TABLE city_points (
2 city_id NUMBER PRIMARY KEY,
3 city_name VARCHAR2(25),
4 latitude NUMBER,
5 longitude NUMBER);
Table created.
SQL>
SQL> -- Original data for the table.
SQL> -- (The sample coordinates are for a random point in or near the city.)
SQL> INSERT INTO city_points (city_id, city_name, latitude, longitude)
2 VALUES (1, 'Boston', 42.207905, -71.015625);
1 row created.
SQL> INSERT INTO city_points (city_id, city_name, latitude, longitude)
2 VALUES (2, 'Raleigh', 35.634679, -78.618164);
1 row created.
SQL> INSERT INTO city_points (city_id, city_name, latitude, longitude)
2 VALUES (3, 'San Francisco', 37.661791, -122.453613);
1 row created.
SQL> INSERT INTO city_points (city_id, city_name, latitude, longitude)
2 VALUES (4, 'Memphis', 35.097140, -90.065918);
1 row created.
SQL>
SQL> -- Add a spatial geometry column.
SQL> ALTER TABLE city_points ADD (shape SDO_GEOMETRY);
Table altered.
SQL>
SQL> -- Update the table to populate geometry objects using existing
SQL> -- latutide and longitude coordinates.
SQL> UPDATE city_points SET shape =
2 SDO_GEOMETRY(
3 2001,
4 8307,
5 SDO_POINT_TYPE(LONGITUDE, LATITUDE, NULL),
6 NULL,
7 NULL
8 );
4 rows updated.
SQL>
SQL> -- Update the spatial metadata.
SQL> INSERT INTO user_sdo_geom_metadata VALUES (
2 'city_points',
3 'SHAPE',
4 SDO_DIM_ARRAY(
5 SDO_DIM_ELEMENT('Longitude',-180,180,0.5),
6 SDO_DIM_ELEMENT('Latitude',-90,90,0.5)
7 ),
8 8307
9 );
1 row created.
SQL> commit;
Commit complete.
显示Spatial表的定义：
SQL> @02_desc_city_points.sql
Connected.
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT
-------------------- ------------------------------ ------------------------------ ----------- ------------------------------
CITY_POINTS CITY_ID NUMBER 22
CITY_POINTS CITY_NAME VARCHAR2 25
CITY_POINTS LATITUDE NUMBER 22
CITY_POINTS LONGITUDE NUMBER 22
CITY_POINTS SHAPE SDO_GEOMETRY 1
CITY_POINTS SYS_NC00010$ NUMBER 22
CITY_POINTS SYS_NC00012$ SDO_ORDINATE_ARRAY 3752
CITY_POINTS SYS_NC00007$ NUMBER 22
CITY_POINTS SYS_NC00008$ NUMBER 22
CITY_POINTS SYS_NC00009$ NUMBER 22
CITY_POINTS SYS_NC00011$ SDO_ELEM_INFO_ARRAY 3752
CITY_POINTS SYS_NC00006$ NUMBER 22
12 rows selected.
未启用in-memory时的查询：
SQL> @03_query.sql
-- 耗时
Elapsed: 00:00:00.24
-- 执行计划
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 25 (100)| |
|* 1 | TABLE ACCESS FULL| CITY_POINTS | 1 | 3849 | 25 (88)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MDSYS"."SDO_FILTER"("C"."SHAPE","MDSYS"."SDO_GEOMETRY"(200
1,8307,"SDO_POINT_TYPE"((-122.453613),37.661791,NULL),NULL,NULL))='TRUE')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 17
physical reads 316
session logical reads 11416
session pga memory 19073328
创建Spatial Index并通过其查询：
SQL> @04_spatial_index.sql
SQL>
SQL> -- Spatial query
SQL>
SQL> SELECT city_name
2 FROM city_points c
3 where
4 sdo_filter(c.shape,
5 sdo_geometry(2001,8307,sdo_point_type(-122.453613,37.661791,null),null,null)
6 ) = 'TRUE';
CITY_NAME
-------------------------
San Francisco
-- 耗时
Elapsed: 00:00:00.50
-- 执行计划
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | CITY_POINTS | 1 | 3849 | 1 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX (SEL: 0.000000 %)| CITY_POINTS_I1 | | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MDSYS"."SDO_FILTER"("C"."SHAPE","MDSYS"."SDO_GEOMETRY"(2001,8307,"SDO_POINT
_TYPE"((-122.453613),37.661791,NULL),NULL,NULL))='TRUE')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
26 rows selected.
Index dropped.
为表设置inmemory属性并发布：
SQL> @05_im_pop.sql
Connected.
SQL>
SQL> alter table CITY_POINTS inmemory;
Table altered.
SQL>
SQL> exec dbms_inmemory.populate(USER, 'CITY_POINTS');
PL/SQL procedure successfully completed.
确认发布完成：
SQL> @06_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB CITY_POINTS COMPLETED 40,960 1,179,648 0
发布后的查询：
SQL> @07_im_query.sql
-- 耗时
Elapsed: 00:00:00.01
-- 执行计划
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23 (100)| |
|* 1 | TABLE ACCESS INMEMORY FULL| CITY_POINTS | 1 | 3849 | 23 (96)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MDSYS"."SDO_FILTER"("C"."SHAPE","MDSYS"."SDO_GEOMETRY"(2001,8307,"S
DO_POINT_TYPE"((-122.453613),37.661791,NULL),NULL,NULL))='TRUE')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 3
IM scan CUs columns accessed 9
IM scan CUs memcompress for query low 1
IM scan rows 4
IM scan rows projected 1
IM scan rows valid 4
session logical reads 983
session logical reads - IM 5
session pga memory 18745648
table scans (IM) 1
10 rows selected.
设置inmemory spatial：
SQL> @08_im_spatial.sql
Connected.
SQL>
SQL> alter table CITY_POINTS no inmemory;
Table altered.
SQL>
SQL> alter table CITY_POINTS inmemory priority high inmemory spatial (shape);
Table altered.
SQL>
SQL> exec dbms_inmemory.populate(USER, 'CITY_POINTS');
PL/SQL procedure successfully completed.
用新的spatial特性发布：
SQL> @09_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB CITY_POINTS COMPLETED 40,960 2,228,224 0
查看和spatial相关的In-Memory Expression：
SQL> @10_spatial_ime.sql
Connected.
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT
--------------- --------------------------------------------- -------------- ------------- ---------------------------------------------
CITY_POINTS CITY_ID NUMBER 22
CITY_POINTS CITY_NAME VARCHAR2 25
CITY_POINTS LATITUDE NUMBER 22
CITY_POINTS LONGITUDE NUMBER 22
CITY_POINTS SYS_NC00012$ SDO_ORDINATE_A 3752
RRAY
CITY_POINTS SHAPE SDO_GEOMETRY 1
CITY_POINTS SYS_NC00011$ SDO_ELEM_INFO_ 3752
ARRAY
CITY_POINTS SYS_NC00010$ NUMBER 22
CITY_POINTS SYS_NC00009$ NUMBER 22
CITY_POINTS SYS_NC00008$ NUMBER 22
CITY_POINTS SYS_NC00007$ NUMBER 22
CITY_POINTS SYS_NC00006$ NUMBER 22
CITY_POINTS SYS_IME_SDO_7799A95C12CE4FFBBF7207242041ED04 BINARY_DOUBLE 8 SDO_GEOM_MIN_Z(SYS_OP_NOEXPAND("SHAPE"))
CITY_POINTS SYS_IME_SDO_FA243DF402924F2BBF6283ACF16B4C33 BINARY_DOUBLE 8 SDO_GEOM_MAX_Y(SYS_OP_NOEXPAND("SHAPE"))
CITY_POINTS SYS_IME_SDO_A4F636A0090F4F89BFB36B60AE1F873E BINARY_DOUBLE 8 SDO_GEOM_MIN_Y(SYS_OP_NOEXPAND("SHAPE"))
CITY_POINTS SYS_IME_SDO_0665D2EF074D4F8EBF3A434B7C1E746D BINARY_DOUBLE 8 SDO_GEOM_MAX_X(SYS_OP_NOEXPAND("SHAPE"))
CITY_POINTS SYS_IME_SDO_8D17E854B7BD4FC6BF4AACBB68ECD5CD BINARY_DOUBLE 8 SDO_GEOM_MIN_X(SYS_OP_NOEXPAND("SHAPE"))
CITY_POINTS SYS_IME_SDO_097B377849D14F2EBF8C88E7B808AF44 BINARY_DOUBLE 8 SDO_GEOM_MAX_Z(SYS_OP_NOEXPAND("SHAPE"))
18 rows selected.
再次执行查询：
SQL> @11_spatial_query.sql
-- 耗时
Elapsed: 00:00:00.01
-- 执行计划
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23 (100)| |
|* 1 | TABLE ACCESS INMEMORY FULL| CITY_POINTS | 1 | 3849 | 23 (96)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((SDO_GEOM_MAX_X("SHAPE")>=SDO_GEOM_MIN_X("MDSYS"."SDO_GEOMETRY"(2001
,8307,"SDO_POINT_TYPE"((-122.453613),37.661791,NULL),NULL,NULL))-7.848052667402416
6E-008D AND SDO_GEOM_MIN_X("SHAPE")<=SDO_GEOM_MAX_X("MDSYS"."SDO_GEOMETRY"(2001,83
07,"SDO_POINT_TYPE"((-122.453613),37.661791,NULL),NULL,NULL))+7.8480526674024166E-
008D AND SDO_GEOM_MAX_Y("SHAPE")>=SDO_GEOM_MIN_Y("MDSYS"."SDO_GEOMETRY"(2001,8307,
"SDO_POINT_TYPE"((-122.453613),37.661791,NULL),NULL,NULL))-7.8480526674024166E-008
D AND SDO_GEOM_MIN_Y("SHAPE")<=SDO_GEOM_MAX_Y("MDSYS"."SDO_GEOMETRY"(2001,8307,"SD
O_POINT_TYPE"((-122.453613),37.661791,NULL),NULL,NULL))+7.8480526674024166E-008D
AND SDO_GEOM_MAX_Z("SHAPE")>=SDO_GEOM_MIN_Z("MDSYS"."SDO_GEOMETRY"(2001,8307,"SDO_
POINT_TYPE"((-122.453613),37.661791,NULL),NULL,NULL))-7.8480526674024166E-008D
AND SDO_GEOM_MIN_Z("SHAPE")<=SDO_GEOM_MAX_Z("MDSYS"."SDO_GEOMETRY"(2001,8307,"SDO_
POINT_TYPE"((-122.453613),37.661791,NULL),NULL,NULL))+7.8480526674024166E-008D))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 5
IM scan CUs columns accessed 9
IM scan CUs memcompress for query low 1
IM scan EU rows 4
IM scan EUs columns accessed 6
IM scan EUs memcompress for query low 1
IM scan rows 4
IM scan rows pcode aggregated 4
IM scan rows projected 1
IM scan rows valid 4
IM scan segments minmax eligible 1
session logical reads 1052
session logical reads - IM 5
session pga memory 18286896
table scans (IM) 1
15 rows selected.
最后，清理环境：
SQL> @12_spatial_cleanup.sql
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1
进入im-text目录，登录数据库：
cd /home/oracle/labs/inmemory/im-text
sqlplus ssb/Ora_DB4U@localhost:1521/pdb1
此实验的主要脚本执行序列如下，后面会详述：
01_chicago_data.sql
02_text_query.sql
03_text_pop.sql
04_im_populated.sql
05_im_text_query.sql
06_fulltext_pop.sql
07_im_populated.sql
08_fulltext_ime.sql
09_im_fulltext_query.sql
10_text_cleanup.sql
查看表定义，此表将近700万行：
SQL> @01_chicago_data.sql
Connected.
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT
-------------------- ------------------------------ ------------------------------ ----------- ------------------------------
CHICAGO_DATA ID NUMBER 22
CHICAGO_DATA CASE_NUMBER VARCHAR2 8
CHICAGO_DATA C_DATE VARCHAR2 30
CHICAGO_DATA BLOCK VARCHAR2 35
CHICAGO_DATA IUCR VARCHAR2 10
CHICAGO_DATA PRIMARY_TYPE VARCHAR2 40
CHICAGO_DATA DESCRIPTION VARCHAR2 100
CHICAGO_DATA LOCATION_DESC VARCHAR2 100
CHICAGO_DATA ARREST VARCHAR2 20
CHICAGO_DATA DOMESTIC VARCHAR2 20
CHICAGO_DATA BEAT VARCHAR2 20
CHICAGO_DATA DISTRICT VARCHAR2 20
CHICAGO_DATA WARD NUMBER 22
CHICAGO_DATA COMMUNITY VARCHAR2 20
CHICAGO_DATA FBI_CODE VARCHAR2 20
CHICAGO_DATA X_COORD NUMBER 22
CHICAGO_DATA Y_COORD NUMBER 22
CHICAGO_DATA C_YEAR NUMBER 22
CHICAGO_DATA UPDATED_ON VARCHAR2 30
CHICAGO_DATA LATTITUDE NUMBER 22
CHICAGO_DATA LONGITUDE NUMBER 22
CHICAGO_DATA LOCATION VARCHAR2 40
22 rows selected.
执行查询：
SQL> @02_text_query.sql
-- 耗时
Elapsed: 00:00:00.52
-- 执行计划
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 57944 (100)| |
| 1 | HASH GROUP BY | | 380 | 8360 | 57944 (1)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| CHICAGO_DATA | 16930 | 363K| 57942 (1)| 00:00:03 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DISTRICT"='009' AND "DESCRIPTION" LIKE '%BATTERY%' AND
"DESCRIPTION" IS NOT NULL))
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 85
physical reads 212246
session logical reads 212466
session pga memory 18352432
启用In-Memory并发布：
SQL> @03_text_pop.sql
Connected.
SQL>
SQL> alter table CHICAGO_DATA inmemory;
Table altered.
SQL>
SQL> exec dbms_inmemory.populate(USER, 'CHICAGO_DATA');
PL/SQL procedure successfully completed.
确认发布完成：
SQL> @04_im_populated.sql
Connected.
SQL>
SQL> -- Query the view v$IM_SEGMENTS to shows what objects are in the column store
SQL> -- and how much of the objects were populated. When the BYTES_NOT_POPULATED is 0
SQL> -- it indicates the entire table was populated.
SQL>
SQL> select owner, segment_name, partition_name, populate_status, bytes,
2 inmemory_size, bytes_not_populated
3 from v$im_segments
4 order by owner, segment_name, partition_name;
In-Memory Bytes
OWNER SEGMENT_NAME PARTITION_NAME POPULATE_STATUS Disk Size Size Not Populated
---------- -------------------- --------------- --------------- ---------------- ---------------- ----------------
SSB CHICAGO_DATA COMPLETED 1,738,686,464 627,965,952 0
内存中的查询还是要快些，快了26倍。注意查询中有模式匹配（like），这正是inmemory的强项，因为索引是用不上的：
SQL> @05_im_text_query.sql
-- 耗时
Elapsed: 00:00:00.02
-- 执行计划
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2314 (100)| |
| 1 | HASH GROUP BY | | 380 | 8360 | 2314 (7)| 00:00:01 |
|* 2 | TABLE ACCESS INMEMORY FULL| CHICAGO_DATA | 16930 | 363K| 2312 (7)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - inmemory(("DISTRICT"='009' AND "DESCRIPTION" LIKE '%BATTERY%' AND
"DESCRIPTION" IS NOT NULL))
filter(("DISTRICT"='009' AND "DESCRIPTION" LIKE '%BATTERY%' AND
"DESCRIPTION" IS NOT NULL))
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 4
IM scan CUs columns accessed 26
IM scan CUs memcompress for query low 13
IM scan CUs pcode aggregation pushdown 13
IM scan rows 6821896
IM scan rows pcode aggregated 29099
IM scan rows projected 62
IM scan rows valid 6821896
IM scan segments minmax eligible 13
session logical reads 212331
session logical reads - IM 212242
session pga memory 18024752
table scans (IM) 1
13 rows selected.
设置inmemory text并发布：
SQL> @06_fulltext_pop.sql
Connected.
SQL>
SQL> alter table CHICAGO_DATA no inmemory;
Table altered.
SQL>
SQL> show parameter max_string_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string EXTENDED
SQL>
SQL> show parameter inmemory_expression
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_expressions_usage string ENABLE
SQL>
SQL> show parameter inmemory_virtual_columns
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_virtual_columns string ENABLE
SQL>
SQL> pause Hit enter ...
Hit enter ...
SQL>
SQL> ALTER TABLE CHICAGO_DATA INMEMORY TEXT (description);
Table altered.
SQL>
SQL> alter table CHICAGO_DATA inmemory;
Table altered.
SQL>
SQL> exec dbms_inmemory.populate(USER, 'CHICAGO_DATA');
PL/SQL procedure successfully completed.
查询全文列信息：
SQL> @08_fulltext_ime.sql
Connected.
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT
--------------- ----------------------------------- ---------- ------------- ----------------------------------------
CHICAGO_DATA SYS_IME_IVDX_975420CBCBA94F95BFA576 RAW 32767 SYS_CTX_MKIVIDX("DESCRIPTION" RETURNING
DB5D3C60A5 RAW(32767))
Elapsed: 00:00:00.00
Hit enter ...
Connected.
SQL>
SQL> -- This query displays IMEUs populated in the In-Memory Column Store
SQL>
SQL> select
2 o.owner,
3 o.object_name,
4 o.subobject_name as partition_name,
5 i.column_name,
6 count(*) t_imeu,
7 sum(i.length)/1024/1024 space
8 from
9 v$im_imecol_cu i,
10 dba_objects o
11 where
12 i.objd = o.object_id
13 group by
14 o.owner,
15 o.object_name,
16 o.subobject_name,
17 i.column_name;
Partition Column Total Used
Owner Object Name Name IMEUs Space(MB)
---------- -------------------- -------------------- --------------- ------- ------------
SSB CHICAGO_DATA SYS_IME_IVDX_97 7 44
5420CBCBA94F95B
FA576DB5D3C60A5
全文本查询，查询用了CONTAINS函数，而非LIKE：
SQL> @09_im_fulltext_query.sql
-- 耗时
Elapsed: 00:00:00.12
-- 执行计划
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2344 (100)| |
| 1 | HASH GROUP BY | | 380 | 82460 | 2344 (8)| 00:00:01 |
|* 2 | TABLE ACCESS INMEMORY FULL| CHICAGO_DATA | 16930 | 3587K| 2343 (8)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - inmemory(("DISTRICT"='009' AND SYS_CTX_CONTAINS2("DESCRIPTION" , 'BATTERY' ,
SYS_CTX_MKIVIDX("DESCRIPTION" RETURNING RAW(32767)))>0))
filter(("DISTRICT"='009' AND SYS_CTX_CONTAINS2("DESCRIPTION" , 'BATTERY' ,
SYS_CTX_MKIVIDX("DESCRIPTION" RETURNING RAW(32767)))>0))
-- 会话统计信息
NAME VALUE
-------------------------------------------------- --------------------
CPU used by this session 9
IM scan CUs columns accessed 14
IM scan CUs memcompress for query low 7
IM scan EU rows 6821896
IM scan EUs columns accessed 7
IM scan EUs memcompress for query low 7
IM scan rows 6821896
IM scan rows projected 29099
IM scan rows valid 6821896
IM scan segments minmax eligible 7
physical reads 57
session logical reads 213612
session logical reads - IM 212242
session pga memory 18680112
table scans (IM) 1
15 rows selected.
最后，清理：
SQL> @10_text_cleanup.sql
文章浏览阅读9.7k次，点赞2次，收藏8次。在上一篇文章里面的官方demo建立索引方式使用的是最基本的索引，这里在介绍两种基础索引更多索引类别1--更快的检索IndexIVFFlat官方介绍：为了加快搜索速度，可以将数据集分割成几部分。我们在d维空间中定义Voronoi单元格，并且每个数据库矢量都落入其中一个单元格中。在搜索时，只有查询x所在单元中包含的数据库向量y与少数几个相邻查询向量进行比较。(划分搜索空间)这是通过Inde..._indexivfflat
文章浏览阅读1.7w次，点赞5次，收藏14次。IAR注释中文显示乱码，在Tools&gt;&gt;Options&gt;&gt;Editor&gt;&gt;Default character 选择正确的编码比如UTF-8，然后关闭文件，再次打开。_iar中文乱码
文章浏览阅读1.6k次。本文主要讲述过去一年内可观测平台 SLS 的改进和迭代。_数据中台 告警系统
文章浏览阅读854次。昨天在win7上装SQLServer数据库，写一下体会。首先，如果以前安装的话，要删除干净。我也找了半天的网络资料。1.把原来SQLServer的安装目录 C:\Program Files\Microsoft SQL Server 删除2.所有SQLServer相关的注册表全部删除，保证系统是没有装过SQLServer的状态注册表打开方式：开始-->运行-->rege_ghost封装sql 2000
文章浏览阅读235次。关于android开发设置View Pager的直接跳转页set CurrentItem会阻塞主线程ANR。根据网上解决的说法，分析源码：if (mFirstLayout) { // We don't have any idea how big we are yet and shouldn't have any pages either. // Just..._viewpager#mfirstlayout
文章浏览阅读1.1k次。Windows下Nginx负载均衡配置和优化方案方案配置的环境：安装环境：Windows系统Nginx版本：nginx-1.18.0代理网站服务器：Windows server系统1.Nginx下载进入下载地址：http://nginx.org/en/download.html选择Windows版本进行下载。2.Nginx安装2.1解压安装将下载好的Nginx压缩文件解压在相关的安装位置，建议放在非C盘的根目录下，原因是网站访问量大时，产生的日志容量会很大，从而占据C盘的存储空间，增加_windows nginx优化
文章浏览阅读737次，点赞9次，收藏8次。检测到hyper-v 时，勾选自动安装。_vm17pro密钥许可证
文章浏览阅读1.5k次。修改/etc/my.cnfcharacter-set-server=utf8[client]default-character-set=utf8注释掉 sql_mode重新新建mysql表在hive的元数据库中执行以下SQL语句，然后重新创建刚才的表即可 。alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;alter table TABLE_PARAMS modify column PAR_sqoop --default-character-set=utf8
文章浏览阅读845次。一、简介 在用python写脚本时，通常需要处理xml格式的文件或字符串。由于json格式处理的方便性， 我们可将其转为json格式进行处理。 二、步骤 1、安装工具包xmltodict 在命令行输入：pip install xmltodict 2、在代码使用xmltodict.parse(xml_str)进行处理 如 def load_json(xml_path): #获取xml文件 xml_f..._python xml 转json 格式
文章浏览阅读1.6k次。1111_mybatis sql 标签里面写入参
文章浏览阅读661次。比如说：模板书写:{'param1'|functionName:'param2':'param3'}php函数原型:echo functionName('param1','param2','param3');实例:{'1234567'|substr_smarty中的内置函数可以实现邮件链接的是
文章浏览阅读2.4k次。转载：https://towardsdatascience.com/ensemble-methods-bagging-boosting-and-stacking-c9214a10a205作者：Joseph Rocca Ensemble methods: bagging, boosting andstackingIntroduction“Unity is ..._bagging方法结合深度学习模型