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

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

ai 2025/8/13 20:04:43

测试时间：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.xdnf.cn/news/17173.html

相关文章：

  • Numpy科学计算与数据分析：Numpy数组创建与应用入门
  • TypeScript 中高频出现的类型结构与用法
  • C++模板知识点6『拆分模板参数』
  • 任务进度状态同步 万能版 参考 工厂+策略+观察者设计模式 +锁设计 springboot+redission
  • C++ 类和对象(2)
  • 顺序表——C语言
  • C++之队列浅析
  • SpringBoot学习日记 Day5：解锁企业级开发核心技能
  • 亚马逊采购风控突围：构建深度隐匿的环境安全体系
  • 剧本杀小程序系统开发：推动社交娱乐产业创新发展
  • TikTok Shop冷启动破局战：亚矩阵云手机打造爆款账号矩阵
  • 项目构想｜文生图小程序
  • 人工智能2.0时代的人才培养和通识教育
  • 动手学深度学习（pytorch版）：第一节——引言
  • Redis学习总结（持续更新）
  • 【45】C++函数重载是什么？函数重载需要注意什么？为什么C++支持函数重载，C语言不支持函数重载？C++和C语言代码之间如何相互调用？
  • 仓库管理系统-20-前端之记录管理的联表查询
  • 2025最新国内服务器可用docker源仓库地址大全（2025年8月更新）
  • 深入剖析Java线程：从基础到实战（上）
  • 上海一家机器人IPO核心零部件依赖外购， 募投计划频繁修改引疑
  • AI绘画：生成唐初李世民全身像提示词
  • idea工具maven下载报错：PKIX path building failed，配置忽略SSL检查
  • 打造交互界面 —— Popup 的艺术
  • 使用萤石云播放视频及主题模版配置
  • 设计模式 观察者模式
  • 软件测试中，pytest 的 yield 有什么作用？
  • Day32--动态规划--509. 斐波那契数，70. 爬楼梯，746. 使用最小花费爬楼梯
  • 第一个vue应用
  • 【性能测试】---测试工具篇
  • JavaSE---异常的经典面试题