In-Memory测试

来源：这里教程网时间：2026-03-03 21:46:16 作者：

In-Memory测试测试要使用普通用户，不要用sysaux和system表空间 2.1 全字段查询通过CTAS方式创建表TEST,普通行式扫描与开启inmemory特性分别测试，比对执行计划。 create table test as select * from dba_objects; no inmemory查询测试 set timing on set autotrace traceonly set pagesize 200 linesize 200 col TABLE_NAME for a20 col INMEMORY_PRIORITY for a20 col INMEMORY_DISTRIBUTE for a20 col INMEMORY_COMPRESSION for a20 全字段查询，执行计划 SQL> select count(*) from test; COUNT(*) ---------- 74329 Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1950795681 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 403 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TEST | 74329 | 403 (1)| 00:00:01 | ------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 5 db block gets 1460 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 开启in memory查询测试 SQL> alter table test inmemory; 1 SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from user_tables; TABLE_NAME INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION -------------------- -------------------- -------------------- -------------------- TEST NONE AUTO FOR QUERY LOW 已开启inmemory SQL> select pool,ALLOC_BYTES/1024/1024,USED_BYTES/1024/1024,POPULATE_STATUS,con_id from V$INMEMORY_AREA; POOL ALLOC_BYTES/1024/1024 USED_BYTES/1024/1024 POPULATE_STATUS CON_ID ------------ ------------ --------------------- ---------- ------------------ 1MB POOL 815 0 DONE 3 64KB POOL 192 0 DONE 3 --因为只是把该表设置了INMEMORY,但是未查询过,所以查询V$INMEMORY_AREA中未使用相关内存-- 全字段查询，执行计划第一次执行： SQL> select count(*) from test; COUNT(*) ---------- 74329 Elapsed: 00:00:00.05 Execution Plan ---------------------------------------------------------- Plan hash value: 1950795681 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 403 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS INMEMORY FULL| TEST | 74329 | 403 (1)| 00:00:01 | ---------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 5 recursive calls 5 db block gets 1467 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed 第二次执行： Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 1950795681 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 (7)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS INMEMORY FULL| TEST | 74329 | 16 (7)| 00:00:01 | ---------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 3 db block gets 9 consistent gets 0 physical reads 0 redo size 544 bytes sent via SQL*Net to client 608 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 查询inmemory内存使用： SQL> select pool,ALLOC_BYTES/1024/1024,USED_BYTES/1024/1024,POPULATE_STATUS,con_id from V$INMEMORY_AREA; POOL ALLOC_BYTES/1024/1024 USED_BYTES/1024/1024 POPULATE_STATUS CON_ID -------------------------- --------------------- -------------------- -------------------------- ---------- 1MB POOL 815 4 DONE 3 64KB POOL 192 .25 DONE 3 --再次查看，已经使用了分配的In-Memory中内存性能比对： sql plan no-inmemory inmemory consistent gets 1460 9 physical reads 0 0 Cost 403 16 结果：开启inmemory之后性能提升162倍 2.2 索引字段比较测试在已开启IMO特性条件下，通过给表test增加列索引，比较IM与索引执行计划 1、在object_name上创建索引 SQL> create index idx_test_OBname on test(OBJECT_NAME); 1 2、查看表TEST是否加载到IM中 col owner for a20 col SEGMENT_NAME for a20 col POPULATE_STATUS for a20 col BYTES_NOT_POPULATED for 99999 SELECT OWNER, SEGMENT_NAME, POPULATE_STATUS, BYTES_NOT_POPULATED FROM V$IM_SEGMENTS; OWNER SEGMENT_NAME POPULATE_STATUS BYTES_NOT_POPULATED -------------------- -------------------- -------------------- ------------------- CS TEST COMPLETED 0 表已被加入到IM中 3、使用索引简单列查询 SQL> set autotrace traceonly SQL> select count(*) from test where object_name='TEST'; Execution Plan ---------------------------------------------------------- Plan hash value: 3197243274 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 35 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 35 | | | |* 2 | INDEX RANGE SCAN| IDX_TEST_OBNAME | 10 | 350 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_NAME"='TEST') Statistics ---------------------------------------------------------- 2 recursive calls 0 db block gets 6 consistent gets 2 physical reads 0 redo size 542 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 逻辑读：6 物理：2 4、强制全表扫描查询 SQL> select /*+full(s)*/count(*) from test s where object_name='test'; Execution Plan ---------------------------------------------------------- Plan hash value: 1950795681 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 35 | 124 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 35 | | | |* 2 | TABLE ACCESS INMEMORY FULL| TEST | 9 | 315 | 124 (1)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - inmemory("OBJECT_NAME"='test') filter("OBJECT_NAME"='test') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 541 bytes sent via SQL*Net to client 607 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 逻辑读：10 物理读：0 总结：在数据离散度较高，且通过索引条件过滤的扫描场景中，IM特性对性能并没有提升，传统的索引+行式存储的执行计划已经足够，在默认情况下还是会根据查询索引返回rowid的方式查找数据。 2.3 批量update测试 1、创建测试表disk、emp，分别为im表以及rows表，总数相同 SQL> create table disk as select * from dba_objects; SQL> insert into disk select * from disk; 74360 rows created. SQL> / 148720 rows created. SQL> / 297440 rows created. SQL> / 594880 rows created. SQL> / 1189760 rows created. SQL> commit; Commit complete. SQL> select count(*) from disk; COUNT(*) ---------- 2379520 SQL> create table mem as select * from disk inmemory; 更新磁盘表 update disk set owner=‘tom’; 更新内存表 update mem set owner=‘tom’; 普通表：时间：14.49 逻辑读：3202294+894128 物理读：5602 cost：403 IM表：时间：18.94 逻辑读：3231179+254 物理读：92476 cost：483 总结：IM特性在update性能有所下降 2.4 大批量insert测试从磁盘插到磁盘 insert into disk select * from cs; 从磁盘插到内存 insert into mem select * from cs; 普通表：时间：0.13 逻辑读：9332+2903 物理读：2895 cost：404 IM表：时间：0.17 逻辑读：3231179+254 物理读：92476 cost：483 2.5 大量delete测试 delete from disk where rownum <10000; delete from mem where rownum <10000; 普通表：时间：0.08 逻辑读：11470+202 物理读：253 cost：12986 IM表：时间：0.07 逻辑读：11472+9 物理读：0 cost：493 2.6 压缩方式测试压缩方式描述 NO MEMCOMPRESS IMO 中存储无压缩 MEMCOMPRESS FOR DML 最小化压缩，优化 DML 操作 MEMCOMPRESS FOR QUERY LOW 缺省方式：查询性能最优、空间压缩效果好于DML方式 MEMCOMPRESS FOR QUERY HIGH 查询性能次优(excellent)、空间压缩效果好于 QUERY LOW MEMCOMPRESS FOR CAPACITY LOW 查询性能良好(good)、空间压缩效果好于 QUERY HIGH MEMCOMPRESS FOR CAPACITY HIGH 缺省设置、空间压缩效果最优查看压缩比 col owner for a5 col SEGMENT_NAME for a10 col POPULATE_STATUS for a10 col INMEMORY_COMPRESSION for a10 SQL> SELECT V.OWNER, V.SEGMENT_NAME,V.BYTES/1024/1024 ORIG_SIZE_MB,V.INMEMORY_SIZE/1024/1024 IN_MEM_SIZE_MB,BYTES_NOT_POPULATED,POPULATE_STATUS status,INMEMORY_COMPRESSION,V.BYTES/V.INMEMORY_SIZE COMP_RATIO FROM V$IM_SEGMENTS V WHERE SEGMENT_NAME = 'TEST'; OWNER SEGMENT_NAME ORIG_SIZE_MB IN_MEM_SIZE_MB BYTES_NOT_POPULATED STATUS INMEMORY_COMPRESS COMP_RATIO ----- -------------------- ------------ -------------- ------------------- -------------------- ----------------- ---------- CS TEST 1466.78125 110.625 0 COMPLETED FOR QUERY LOW 13.2590395 表TEST在磁盘上占用1466MB,采用默认压缩方式，内存中占用110.625MB，压缩比为13.26：1 压缩比测试：表TEST大小1466.78125MB，数据行9514112 1、NO MEMCOMPRESS： alter table test inmemory MEMCOMPRESS NO MEMCOMPRESS; 逻辑读：14 物理读：0 COST: 126 时间： 0.01 压缩比：1.19 2、MEMCOMPRESS FOR DML： alter table test inmemory MEMCOMPRESS FOR DML; 逻辑读：14 物理读：0 COST: 126 时间：0.01 压缩比：1.22 3、MEMCOMPRESS FOR QUERY LOW： alter table test inmemory MEMCOMPRESS FOR QUERY LOW; 逻辑读：14 物理读：0 COST:125 时间：0.01 压缩比：7.20 4、MEMCOMPRESS FOR QUERY HIGH： alter table test inmemory MEMCOMPRESS FOR QUERY high; 逻辑读：14 物理读：0 COST: 125 时间：0.01 压缩比：9.67 5、MEMCOMPRESS FOR CAPACITY LOW： alter table test inmemory MEMCOMPRESS FOR CAPACITY LOW; 逻辑读：14 物理读：0 COST: 125 时间：0.01 压缩比：12.91 6、MEMCOMPRESS FOR CAPACITY HIGH： alter table test inmemory MEMCOMPRESS FOR CAPACITY HIGH; 逻辑读：14 物理读：0 COST: 25 时间：0.01 压缩比：19.14 测试结果汇总：压缩方式压缩比 NO MEMCOMPRESS 1.19 MEMCOMPRESS FOR DML 1.22 MEMCOMPRESS FOR QUERY LOW 7.20 MEMCOMPRESS FOR QUERY HIGH 9.67 MEMCOMPRESS FOR CAPACITY LOW 12.91 MEMCOMPRESS FOR CAPACITY HIGH 19.14 总结：压缩方式不同，表加载到IM中的时间也会不一样。压缩比越大加载到内存中的时间越长。而对于select查询消耗时间不影响。 2.7 查询大量列性能测试三、评估对象在IMO大小 DBMS_COMPRESSION.GET_COMPRESSION_RATIO 在对一张表使用COMPRESSION clause进行IM压缩级别设置之前，我们可以通过Oracle的COMPRESSION ADVISOR对表放入到IM中的大小进行提前计算。 SET SERVEROUTPUT ON DECLARE l_blkcnt_cmp PLS_INTEGER; l_blkcnt_uncmp PLS_INTEGER; l_row_cmp PLS_INTEGER; l_row_uncmp PLS_INTEGER; l_cmp_ratio PLS_INTEGER; l_comptype_str VARCHAR2(100); BEGIN dbms_compression.get_compression_ratio ( -- Input parameters scratchtbsname => 'CS', ownname => 'CS', objname => 'TEST', subobjname => NULL, comptype => dbms_compression.comp_inmemory_QUERY_LOW, -- Output parameter blkcnt_cmp => l_blkcnt_cmp, blkcnt_uncmp => l_blkcnt_uncmp, row_cmp => l_row_cmp, row_uncmp => l_row_uncmp, cmp_ratio => l_cmp_ratio, comptype_str => l_comptype_str, subset_numrows => dbms_compression.comp_ratio_allrows); dbms_output.put_line('Comp. ratio (QUERY LOW):'||l_cmp_ratio); END; / Comp. ratio (QUERY LOW):7 估计结果压缩比7，实际压缩比为7.20 #五、RAC环境测试 rac环境独有参数DUPLICATE clause、DISTRIBUTE clause： DUPLICATE clause：此参数为EXADATA一体机专用，在RAC环境中，每个节点拥有自己的IM Area。一个objects根据DUPLICATE clause的设置将一样的数据加载到多个IM Area中。默认是NO DUPLICATE设置，表示在数据库的IM中对一个objects在所有节点中合起来只保存一份。举例说明，比如三节点的RAC中，对于分区表SALES来讲可能2012年份的数据在1节点，2013年份的数据在2节点，2014年份的数据在3节点，每个分区只保存在一个节点上。为了提升可用性，也可以设置为 DUPLICAET ALL，在每个节点上都保存一份。举例说明，还是刚才那个SALES表的请款下，1，2，3三个节点各保存一份完整sales表数据到各自的IM中。在任意一个节点上都可以获取查询需要的数据。在设置为DUPLICATE ALL的情况下 DISTRIBUTE clause：如果一个objects因为太大无法被加载到一个IM Area中，还可以通过DISTRIBUTE clause的设置将它分成几个数据片分别加载到不同的节点中。默认情况下 DISTRIBUTE clause 的默认值为AUTO-DISTRIBUTE，这时候是否将objects分布式分布在不同的节点上由Oracle内部算法决定。这个参数对于单实例没有影响，在RAC环境中，默认存在IM中的表会分布在各个节点之中。 RAC环境并不像单实例一样只需修改表的IM属性即可启用，如果要使用IMO，必须在系统层面修改“并行度策略”为自动。下面对参数“parallel_degree_policy”，分别测试2个场景 1、先查看要测试的表TEST是否已加载到IM STORE col SEGMENT_NAME for a5 col POPULATE_STATUS for a10 col INMEMORY_DISTRIBUTE for a10 col INMEMORY_DUPLICATE for a20 SQL> select INST_ID,SEGMENT_NAME,INMEMORY_SIZE/1024/1024,BYTES/1024/1024,BYTES_NOT_POPULATED/1024/1024,INMEMORY_DISTRIBUTE,INMEMORY_DUPLICATE,POPULATE_STATUS from gv$im_segments; INST_ID SEGME INMEMORY_SIZE/1024/1024 BYTES/1024/1024 BYTES_NOT_POPULATED/1024/1024 INMEMORY_D INMEMORY_DUPLICATE POPULATE_S ---------- ----- ----------------------- --------------- ----------------------------- ---------- -------------------- ---------- 2 TEST 9.125 192 123.53125 AUTO NO DUPLICATE COMPLETED 1 TEST 16.1875 192 66.9140625 AUTO NO DUPLICATE COMPLETED 1、开启AUTO DOP alter system set parallel_degree_policy=AUTO sid='*'; alter system set parallel_force_local=false sid='*'; alter system flush buffer_cache; alter system flush shared_pool; 2、查看执行计划 SQL> select /*+ parallel */count(*) from test; COUNT(*) ---------- 1455168 Execution Plan ---------------------------------------------------------- Plan hash value: 2661943167 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 | | | | | 1 | SORT AGGREGATE | | 1 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWC | | | 6 | TABLE ACCESS INMEMORY FULL| TEST | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWP | | ----------------------------------------------------------------------------------------------------------------- Note ----- - automatic DOP: Computed Degree of Parallelism is 2 - parallel scans affinitized for inmemory Statistics ---------------------------------------------------------- 232 recursive calls 4 db block gets 163 consistent gets 15 physical reads 0 redo size 545 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 16 sorts (memory) 0 sorts (disk) 1 rows processed 逻辑读：163 物理读：15 、不使用并行查询查询 SQL> select count(*) from test; COUNT(*) ---------- 1455168 Execution Plan ---------------------------------------------------------- Plan hash value: 1950795681 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 16 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS INMEMORY FULL| TEST | 90948 | 16 (0)| 00:00:01 | ---------------------------------------------------------------------------- Note ----- - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation Statistics ---------------------------------------------------------- 3 recursive calls 0 db block gets 9081 consistent gets 9077 physical reads 0 redo size 545 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 逻辑读：9081 物理读：9077 4、关闭AUTO DOP alter system set parallel_degree_policy=MANUAL sid='*'; alter system set parallel_force_local=false sid='*'; alter system flush buffer_cache; alter system flush shared_pool; 5、查看执行计划 SQL> select /*+ parallel */count(*) from test; COUNT(*) ---------- 1455168 Execution Plan ---------------------------------------------------------- Plan hash value: 2661943167 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 | | | | | 1 | SORT AGGREGATE | | 1 | | | | | | | 2 | PX COORDINATOR | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) | | 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | | | 5 | PX BLOCK ITERATOR | | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWC | | | 6 | TABLE ACCESS INMEMORY FULL| TEST | 90948 | 9 (0)| 00:00:01 | Q1,00 | PCWP | | ----------------------------------------------------------------------------------------------------------------- Note ----- - automatic DOP: Computed Degree of Parallelism is 2 - parallel scans affinitized for inmemory Statistics ---------------------------------------------------------- 209 recursive calls 4 db block gets 17909 consistent gets 17766 physical reads 0 redo size 545 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 16 sorts (memory) 0 sorts (disk) 1 rows processed 逻辑读：17909 物理读：17766 总结： 1、调整系统参数(parallel_degree_policy)之后，rac环境下的IM列查询大幅降低了逻辑读与物理读。 2、在多实例的并发查询中实例之间传输的并不是IMCU，而是每个节点都会对本节点的数据运行相同的sql语句，之后把自己的结果集发送给发起sql语句的实例，组成最终的结果返回给用户。 parallel_degree_policy AUTO MANUAL 逻辑读 163 17909 物理读 15 17766 备注：在没有显式使用并行sql时，rac环境im全表扫描并没有使用并行。oracle的优化器会通过一系列的计算比较cost。 SQL> select count(*) from test; 执行计划Note ----- automatic DOP: Computed Degree of Parallelism is 1 because of no expensive parallel operation In some cases, even with Auto DOP set correctly, the optimizer may calculate the cost of a serial access to be less than the cost of a parallel access. This has been identified as bug 18960760, and will usually only happen when very smalltables in the IM column store are involved in the query. 来自Oracle Blog https://blogs.oracle.com/in-memory/oracle-database-in-memory-on-rac-part-i 四、参考文献 1、Oracle Database In-Memory on RAC - Part I https://blogs.oracle.com/in-memory/oracle-database-in-memory-on-rac-part-i 2、Oracle 12c DB In-Memory入门实验手册 https://blog.csdn.net/badly9/article/details/49777993 3、Oracle12c IMO 测试 https://www.jianshu.com/p/966ee0182e1c 4、rac并行查询 http://blog.sina.com.cn/s/blog_74a7d3390102wegl.html 5、Oracle In-Memory白皮书 http://www.oracle.com/technetwork/cn/database/in-memory/overview/twp-oracle-database-in-memory-2245633-zhs.pdf

编辑推荐：

返回列表

In-Memory测试

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推