-
Oracle 21版Database In-Memory LivaLabs实验（下）
本文为Oracle Database In-Memory实验的下篇，上篇参见这里。
下篇的实验属于附加实验，并没有对应的实验手册，但这些实验还是很有用的，因此我自己做了一遍。
实验 11: In-Memory 并行执行
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1- 1
进入im-parallel目录，登录数据库：
cd /home/oracle/labs/inmemory/im-parallel sqlplus ssb/Ora_DB4U@localhost:1521/pdb1- 1
- 2
此实验的主要脚本执行序列如下，后面会详述：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
首先查看并行的参数设置：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
发布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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
查看发布状态：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
串行，单表查询：
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 | ----------------------------------------------------------------------------------------------------------- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
并行(通过设置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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
执行计划显示启用了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")))- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
并行，多表联结查询。
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
执行计划显示启用了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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
注意执行计划中的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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
执行计划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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
注意会话统计信息中的
IM scan (dynamic)关键字。实验 12: In-Memory ADO (Automatic Data Optimization)
ADO属于Oracle数据库生命周期管理。ADO在DBIM之前就有了。详见这个实验。
实验前，需要执行以下：. ~/.set-env-db.sh CDB1- 1
进入ado目录，登录数据库：
cd /home/oracle/labs/inmemory/ado sqlplus ssb/Ora_DB4U@localhost:1521/pdb1- 1
- 2
此实验的主要脚本执行序列如下，后面会详述：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
首先，启用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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
查看热图(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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
为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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
查看发布状态：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
执行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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
发布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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
确认已完全发布：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
执行针对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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
这个生成查询的脚本如下，唯独没有查询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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
查看热图：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
为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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
评估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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
最后，执行清理：
@12_ado_cleanup.sql exec dbms_ilm_admin.CLEAR_HEAT_MAP_ALL;- 1
- 2
实验 13: Automatic In-Memory (21之前版本)
背景知识：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1- 1
进入aim目录，登录数据库：
cd /home/oracle/labs/inmemory/aim sqlplus ssb/Ora_DB4U@localhost:1521/pdb1- 1
- 2
此实验的主要脚本执行序列如下，后面会详述：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
查看AIM状态：
SQL> @01_aim_status.sql Connected. NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_automatic_level string OFF- 1
- 2
- 3
- 4
- 5
- 6
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
查看表的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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
发布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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
确认发布完成：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
发布这些表，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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
确认发布完成，此时内存快满了（但没有满）：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
此时发布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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
查看发布状态，此时有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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
查看热图，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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
查看AIM任务：
SQL> @13_aimtasks.sql Connected. TASK_ID CREATE_TIME STATE ---------- --------------------------- ------- 2 09-NOV-22 12:38:30 DONE- 1
- 2
- 3
- 4
- 5
- 6
查看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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
确认：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
设置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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
多次全表扫描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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
由于内存不够，所以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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
这地方，你要有点耐心。多执行几次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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
查看热图信息，目前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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
查看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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
最后，清理：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
实验 14: In-Memory Join Group
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1- 1
进入join-groups目录，登录数据库：
cd /home/oracle/labs/inmemory/join-groups sqlplus ssb/Ora_DB4U@localhost:1521/pdb1- 1
- 2
此实验的主要脚本执行序列如下，后面会详述：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
在实验前，请确保SSB schema的5张表均已发布。若没有，请执行setup目录中的以下脚本：
@04_im_alter_table.sql @06_im_start_pop.sql @08_im_populated.sql- 1
- 2
- 3
没有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) --- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
创建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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
确认发布完成：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
显示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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
启用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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
执行计划中的Join Group Usage中的信息表明Join Group已使用。
在SQL Monitor报告中的以下信息也可以证明Join Group已使用：
Columnar Encodings Leveraged: 1- 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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
实验 15: In-Memory 外部表
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1- 1
进入ext-tab目录，登录数据库：
cd /home/oracle/labs/inmemory/ext-tab sqlplus ssb/Ora_DB4U@localhost:1521/pdb1- 1
- 2
此实验的主要脚本执行序列如下，后面会详述：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
确认目录对象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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
然后创建目录对象EXT_DIR：
SQL> create directory ext_dir as '/home/oracle/labs/inmemory/ext-tab/ext_tables'; Directory created.- 1
- 2
- 3
创建外部表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)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
查询外部表：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
发布外部表：
SQL> @04_pop_ext.sql SQL> alter table ext_cust inmemory; SQL> exec dbms_inmemory.populate(USER,'EXT_CUST');- 1
- 2
- 3
确认发布完成：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
发布后，再次查询：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
接下来看外部分区表，为列表分区：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
发布外部分区表：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
确认已发布：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
查看外部表的定义：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
外部分区表在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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
接下来创建混合分区表，为列表分区。其中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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
设置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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
查看发布状态：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
查看混合分区表的定义：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
针对混合分区表的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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
清理：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
本实验还可以参考：Create and Load Partitions in In-Memory Hybrid Partitioned Tables
实验 16: In-Memory Spatial
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1- 1
进入im-spatial目录，登录数据库：
cd /home/oracle/labs/inmemory/im-spatial sqlplus ssb/Ora_DB4U@localhost:1521/pdb1- 1
- 2
此实验的主要脚本执行序列如下，后面会详述：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
创建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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
显示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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
未启用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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
创建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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
为表设置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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
确认发布完成：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
发布后的查询：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
设置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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
用新的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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
查看和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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
再次执行查询：
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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
最后，清理环境：
SQL> @12_spatial_cleanup.sql- 1
实验 17: In-Memory Text
实验前，需要执行以下：
. ~/.set-env-db.sh CDB1- 1
进入im-text目录，登录数据库：
cd /home/oracle/labs/inmemory/im-text sqlplus ssb/Ora_DB4U@localhost:1521/pdb1- 1
- 2
此实验的主要脚本执行序列如下，后面会详述：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
查看表定义，此表将近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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
执行查询：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
启用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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
确认发布完成：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
内存中的查询还是要快些，快了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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
设置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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
查询全文列信息：
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
全文本查询，查询用了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.- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
最后，清理：
SQL> @10_text_cleanup.sql- 1
-
相关阅读:
用长tree方式做等长线
SpringBoot缓存使用方式@EnableCaching、@Cacheable
leetcode11-盛最多水的容器
分布式的理解
github pages 部署单页面
【性能测试】jmeter连接数据库jdbc
《对比Excel，轻松学习Python数据分析》读书笔记------多表拼接
[英雄星球六月集训LeetCode解题日报] 第24日 线段树
PostgreSQL 管理PG 的 4个 自制小脚本
Vue3 script setup 语法糖（升级版）
- 原文地址：https://blog.csdn.net/stevensxiao/article/details/127777635
