当前位置：首页 > news >正文

Oracle 19C In-Memory 列存储技术测试

news 2025/8/8 10:45:10

测试时间：20250807
数据库版本：Oracle 19.27
表数据量：1000万行
==========================================================================================
结论：
1、未启用In-Memory和启用In-Memory，以下查询时间如下：
SELECT region, SUM(amount)
FROM sales
GROUP BY region;

未启用：2.94
启用：00.54

SELECT product_id,
TO_CHAR(sale_date, 'YYYY-MM'),
SUM(quantity),
SUM(amount)
FROM sales
WHERE sale_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
AND TO_DATE('2023-12-31', 'YYYY-MM-DD')
GROUP BY product_id, TO_CHAR(sale_date, 'YYYY-MM')
ORDER BY product_id, TO_CHAR(sale_date, 'YYYY-MM');

未启用：3.14
启用：1.15s

这是1000万行表的测试，如果表更大，效果会更明显；启用IM后，执行计划TABLE ACCESS INMEMORY FULL，物理读为0。

2、启用压缩
CAPACITY HIGH级别：(ALTER TABLE sales INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;)
POOL ALLOC_BYTES USED_BYTES
-------------------------- ----------- ----------
1MB POOL 3001024512 74448896 <<<<<<<<<<<<<<<<<<<
64KB POOL 1275068416 3604480

FOR QUERY LOW级别：(ALTER TABLE sales INMEMORY MEMCOMPRESS FOR QUERY LOW;)
POOL ALLOC_BYTES USED_BYTES
-------------------------- ----------- ----------
1MB POOL 3001024512 294649856 <<<<<<<<<<<<<<<<<<<
64KB POOL 1275068416 3604480

NO MEMCOMPRESS（不压缩）:(ALTER TABLE sales INMEMORY NO MEMCOMPRESS;)
POOL ALLOC_BYTES USED_BYTES
-------------------------- ----------- ----------
1MB POOL 3001024512 415236096 <<<<<<<<<<<<<<<<<<<
64KB POOL 1275068416 19333120

说明：CAPACITY HIGH 74M左右；FOR QUERY LOW 294M左右;NO MEMCOMPRESS 415M左右；最高压缩和不压缩相差6倍左右。

==========================================================================================
检查是否有In-Memory Column Store组件。
SELECT * FROM v$option WHERE parameter = 'In-Memory Column Store';

1. 检查并配置In-Memory参数

-- 1.1 检查In-Memory是否启用
SHOW PARAMETER INMEMORY_SIZE;

-- 如果未启用或需要调整大小（需要重启数据库）
ALTER SYSTEM SET INMEMORY_SIZE=4G SCOPE=SPFILE;

-- 1.2 确认In-Memory Area状态
SELECT POOL, ALLOC_BYTES, USED_BYTES FROM V$INMEMORY_AREA;

2. 创建测试表并生成测试数据
-- 创建sales表
CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
product_id NUMBER,
sale_date DATE,
quantity NUMBER,
amount NUMBER,
region VARCHAR2(50)
);

-- 生成测试数据（约1000万条）
DECLARE
v_start_date DATE := TO_DATE('2020-01-01', 'YYYY-MM-DD');
v_end_date DATE := TO_DATE('2023-12-31', 'YYYY-MM-DD');
v_days NUMBER := v_end_date - v_start_date;
v_regions VARCHAR2(200) := 'North,South,East,West,Central,Northeast,Northwest,Southeast,Southwest';
BEGIN
FOR i IN 1..10000000 LOOP
INSERT INTO sales VALUES (
i,
MOD(i, 1000) + 1,
v_start_date + MOD(i, v_days),
MOD(i, 10) + 1,
ROUND(DBMS_RANDOM.VALUE(10, 1000), 2),
REGEXP_SUBSTR(v_regions, '[^,]+', 1, MOD(i, 9) + 1)
);

IF MOD(i, 10000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/

-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SALES');

3. 启用表的In-Memory特性
-- 3.1 将表sales加载到In-Memory列存储
ALTER TABLE sales INMEMORY MEMCOMPRESS FOR QUERY LOW;

-- 3.2 可选：指定关键列使用不同压缩率
ALTER TABLE sales
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (amount, sale_date)
NO INMEMORY (region);

-- 3.3 验证In-Memory配置
SELECT TABLE_NAME, INMEMORY_COMPRESSION, INMEMORY_PRIORITY
FROM DBA_TABLES
WHERE TABLE_NAME = 'SALES';

TABLE_NAME INMEMORY_COMPRESS INMEMORY
---------- ----------------- --------
SALES FOR QUERY LOW NONE

-- 查看列级In-Memory设置
col owner for a10
col TABLE_NAME for a10
col COLUMN_NAME for a22
select * from v$im_column_level where TABLE_NAME= 'SALES';

INST_ID OWNER OBJ_NUM TABLE_NAME SEGMENT_COLUMN_ID COLUMN_NAME INMEMORY_COMPRESSION CON_ID
---------- ---------- ---------- ---------- ----------------- ---------------------- -------------------------- ----------
1 JOE 100882 SALES 1 SALE_ID DEFAULT 0
1 JOE 100882 SALES 2 PRODUCT_ID DEFAULT 0
1 JOE 100882 SALES 3 SALE_DATE DEFAULT 0
1 JOE 100882 SALES 4 QUANTITY DEFAULT 0
1 JOE 100882 SALES 5 AMOUNT DEFAULT 0
1 JOE 100882 SALES 6 REGION DEFAULT 0

4. 手动加载数据到内存并监控进度

-- 4.1 触发全表加载
SELECT /*+ FULL(sales) NO_PARALLEL(sales) */ COUNT(*) FROM sales;

-- 4.2 监控加载进度
SELECT SEGMENT_NAME, BYTES_NOT_POPULATED, POPULATE_STATUS
FROM V$IM_SEGMENTS
WHERE SEGMENT_NAME = 'SALES';

SEGMENT_NA BYTES_NOT_POPULATED POPULATE_STAT
---------- ------------------- -------------
SALES 0 COMPLETED

-- 4.3 查看内存使用情况
SELECT POOL, ALLOC_BYTES, USED_BYTES FROM V$INMEMORY_AREA;
POOL ALLOC_BYTES USED_BYTES
-------------------------- ----------- ----------
1MB POOL 3001024512 294649856
64KB POOL 1275068416 3604480

5. 性能测试比较
5.1 禁用In-Memory时的查询性能
-- 临时禁用In-Memory
ALTER TABLE sales NO INMEMORY;

-- 执行查询并记录时间
SET TIMING ON
SET AUTOTRACE TRACE STATISTICS

-- 查询1：按区域汇总销售额
SELECT region, SUM(amount)
FROM sales
GROUP BY region;

9 rows selected.

Elapsed: 00:00:02.94

Statistics
----------------------------------------------------------
140 recursive calls
11 db block gets
53396 consistent gets
53196 physical reads
1944 redo size
854 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
20 sorts (memory)
0 sorts (disk)
9 rows processed

-- 查询2：按产品和日期范围汇总
SELECT product_id,
TO_CHAR(sale_date, 'YYYY-MM'),
SUM(quantity),
SUM(amount)
FROM sales
WHERE sale_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
AND TO_DATE('2023-12-31', 'YYYY-MM-DD')
GROUP BY product_id, TO_CHAR(sale_date, 'YYYY-MM')
ORDER BY product_id, TO_CHAR(sale_date, 'YYYY-MM');

12000 rows selected.

Elapsed: 00:00:03.14

Statistics
----------------------------------------------------------
9 recursive calls
20 db block gets
53222 consistent gets
53194 physical reads
3868 redo size
430985 bytes sent via SQL*Net to client
9489 bytes received via SQL*Net from client
801 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12000 rows processed

SET TIMING OFF
SET AUTOTRACE OFF

5.2 启用In-Memory时的查询性能
-- 重新启用In-Memory
ALTER TABLE sales INMEMORY;

-- 确保数据已加载
SELECT /*+ FULL(sales) NO_PARALLEL(sales) */ COUNT(*) FROM sales;

SELECT SEGMENT_NAME, BYTES_NOT_POPULATED, POPULATE_STATUS
FROM V$IM_SEGMENTS
WHERE SEGMENT_NAME = 'SALES';

-- 执行相同的查询并记录时间
SET TIMING ON
SET AUTOTRACE TRACE STATISTICS

-- 查询1：按区域汇总销售额
SELECT region, SUM(amount)
FROM sales
GROUP BY region;

9 rows selected.

Elapsed: 00:00:00.54

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
0 redo size
854 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

-- 查询2：按产品和日期范围汇总
SELECT product_id,
TO_CHAR(sale_date, 'YYYY-MM'),
SUM(quantity),
SUM(amount)
FROM sales
WHERE sale_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
AND TO_DATE('2023-12-31', 'YYYY-MM-DD')
GROUP BY product_id, TO_CHAR(sale_date, 'YYYY-MM')
ORDER BY product_id, TO_CHAR(sale_date, 'YYYY-MM');

12000 rows selected.

Elapsed: 00:00:01.15

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
430985 bytes sent via SQL*Net to client
9489 bytes received via SQL*Net from client
801 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12000 rows processed

SET TIMING OFF
SET AUTOTRACE OFF

5.3 验证执行计划
-- 验证查询是否使用了In-Memory
EXPLAIN PLAN FOR
SELECT region, SUM(amount) FROM sales GROUP BY region;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 2895541888

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 117 | 712 (23)| 00:00:01 |
| 1 | HASH GROUP BY | | 9 | 117 | 712 (23)| 00:00:01 |
| 2 | TABLE ACCESS INMEMORY FULL| SALES | 10M| 123M| 711 (23)| 00:00:01 |
-------------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT product_id,
TO_CHAR(sale_date, 'YYYY-MM'),
SUM(quantity),
SUM(amount)
FROM sales
WHERE sale_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')
AND TO_DATE('2023-12-31', 'YYYY-MM-DD')
GROUP BY product_id, TO_CHAR(sale_date, 'YYYY-MM')
ORDER BY product_id, TO_CHAR(sale_date, 'YYYY-MM');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 3229864837

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 257K| 5027K| | 7395 (8)| 00:00:01 |
| 1 | SORT GROUP BY | | 257K| 5027K| 76M| 7395 (8)| 00:00:01 |
|* 2 | TABLE ACCESS INMEMORY FULL| SALES | 2494K| 47M| | 911 (40)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

2 - inmemory("SALE_DATE">=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "SALE_DATE"<=TO_DATE(' 2023-12-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
filter("SALE_DATE">=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "SALE_DATE"<=TO_DATE(' 2023-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

18 rows selected.

6. 自动In-Memory管理(AIM) 说明
-- 6.1 启用AIM
ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = ADVANCED;

--OFF, HIGH, MEDIUM, LOW

1. 自动管理 In-Memory 对象的生命周期
自动加载/卸载： AIM 会根据 SQL 查询的访问频率，自动将频繁访问的表/列加载到 In-Memory Column Store，并淘汰不常用的数据。
动态优先级调整：自动调整 INMEMORY_PRIORITY（如将热点数据设为 HIGH，冷数据设为 LOW）。
2. 优化内存利用率
智能内存分配：在 INMEMORY_SIZE 有限的情况下，AIM 会优先保留高价值数据，避免内存浪费。
自动压缩策略：根据访问模式动态选择压缩级别（如 FOR QUERY LOW 或 FOR CAPACITY HIGH）。
3. 减少手动干预
无需手动指定 INMEMORY 属性，AIM 会自动识别适合列式存储的对象。

可选参数：
--OFF, HIGH, MEDIUM, LOW

LOW:当Inmemory内存存在压力时，数据库将从IM列存储中清除冷数据。
MEDIUM:当Inmemory内存存在压力时，能保证任何热数据不被首先清除出去。
OFF:这是默认值。当设置此值时，将禁用自动内存功能。

在19c中一键搞定
INMEMORY_AUTOMATIC_LEVEL增加了一个新的值HIGH，我们只需设置一个大小，设置一个级别HIGH，就全搞定了。

7. 测试不同压缩级别的影响
-- 7.1 更改压缩级别为MEMCOMPRESS FOR CAPACITY HIGH
ALTER TABLE sales INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

-- 重新加载数据
SELECT /*+ FULL(sales) NO_PARALLEL(sales) */ COUNT(*) FROM sales;

-- 7.2 检查内存使用变化
SELECT POOL, ALLOC_BYTES, USED_BYTES
FROM V$INMEMORY_AREA;

SELECT TABLE_NAME, INMEMORY_COMPRESSION, INMEMORY_PRIORITY
FROM DBA_TABLES
WHERE TABLE_NAME = 'SALES';

CAPACITY HIGH：(ALTER TABLE sales INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;)
POOL ALLOC_BYTES USED_BYTES
-------------------------- ----------- ----------
1MB POOL 3001024512 74448896 <<<<<<<<<<<<<<<<<<<
64KB POOL 1275068416 3604480

FOR QUERY LOW：(ALTER TABLE sales INMEMORY MEMCOMPRESS FOR QUERY LOW;)
POOL ALLOC_BYTES USED_BYTES
-------------------------- ----------- ----------
1MB POOL 3001024512 294649856 <<<<<<<<<<<<<<<<<<<
64KB POOL 1275068416 3604480

NO MEMCOMPRESS:(ALTER TABLE sales INMEMORY NO MEMCOMPRESS;)
POOL ALLOC_BYTES USED_BYTES
-------------------------- ----------- ----------
1MB POOL 3001024512 415236096 <<<<<<<<<<<<<<<<<<<
64KB POOL 1275068416 19333120

说明：CAPACITY HIGH 74M左右；FOR QUERY LOW 294M左右;NO MEMCOMPRESS 415M左右；最高压缩和不压缩相差6倍左右。

===============================================================================================
其他：

- 清理缓冲区缓存
ALTER SYSTEM FLUSH BUFFER_CACHE;

-- 清理共享池
ALTER SYSTEM FLUSH SHARED_POOL;

==========================================================================================================================
单列缓存测试：

ALTER TABLE sales INMEMORY MEMCOMPRESS FOR QUERY LOW;

ALTER TABLE sales INMEMORY MEMCOMPRESS FOR QUERY LOW (amount) NO INMEMORY (sale_id, product_id, sale_date, quantity, region);

SELECT /*+ FULL(sales) NO_PARALLEL(sales) */ COUNT(*) FROM sales;

SELECT POOL, ALLOC_BYTES, USED_BYTES
FROM V$INMEMORY_AREA;

-- 确认只有amount列被加载
SELECT segment_name, bytes_not_populated
FROM v$im_segments
WHERE segment_name = 'SALES';

-- 检查列压缩状态
col owner for a10
col TABLE_NAME for a10
col COLUMN_NAME for a22
select * from v$im_column_level where TABLE_NAME= 'SALES';

EXPLAIN PLAN FOR
SELECT SUM(amount) FROM sales;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT SUM(quantity) FROM sales;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

查看全文

http://www.lryc.cn/news/613230.html