平均提速20倍！Oracle 12c In-Memory最佳实践

Oracle 12.1.0.2 引入了In-Memory Column Store（以下简称IM）新特性，该特性开启后会在数据库启动阶段在SGA中分配一块静态的内存池In-Memory Area，用于存放以列式存储的用户表。

列式存储的优点是在访问数据时只需要访问数据的部分列，而不像行式存储，需要访问数据的所有列。列式存储可以避免大量不必要I/O，且每一列的列值即为索引，可以显著提高查询性能。

IM列式存储并不会替换传统的buffer cache行式存储，而是作为补充，Oracle优化器会根据两种方式的特点自行选择适合的方式来取数据。

下图展示Oracle以两种方式存储数据：

IM可以对存入的表进行压缩，压缩级由低到高分别为：

1. NO MEMCOMPRESS  

2. MEMCOMPRESS FOR DML

3. MEMCOMPRESS FOR QUERY LOW 

4. MEMCOMPRESS FOR QUERY HIGH

5. MEMCOMPRESS FOR CAPACIT LOW

6. MEMCOMPRESS FOR CAPACITY HIGH

默认级别为MEMCOMPRESS FOR QUERY LOW，该级别在有效压缩表的同时提供最佳的查询性能，数据库不对数据进行解压读取，而是采用数据字典压缩方式，即删除重复数据来减少内存使用。此外，其它更高的级别的压缩方式需要对数据进行压缩，会增加额外消耗。

1.开启IM特性

IM特性由inmemory_size 参数控制，只要参数值大于0，该特性即被开启，注意，inmemory_size 至少设置100M，否则无法启动实例，报错ORA-64353：

以设置inmemory_size=10g 为例：

Alter system set inmemory_size=10g sid=‘db12c1’ scope=spfile；

重启实例后，SGA分配时会多出一项IN-Memory Area，说明IM特性已被打开。

2.IM性能测试

以一张36万行的表进行全表扫描为例：

未开启IM：

开启IM后：

可以看到，开启IM后TABLE ACCESS FULL变为TABLE ACCESS INMEMORY FULL，逻辑读从6130降为6，CPU cost由427降为17，性能有成百上千倍的提升。

im_tab_ja表有10万条记录，im_tab_jb表有2000多条记录，两表做关联查询测试。

未开启IM特性：

开启IM特性：

可以看到，IM对表关联查询的提升也非常明显。

im_tab_ja有10万条数据，在im_tab_ja表的table_name列加索引，数据离散度很高，对应的索引选择性也就较好，此时进行全字段查询，过滤字段为table_name。

IM 特性开启后默认情况下执行计划并没有走IM的扫描：

指定执行计划走IM：

虽然强制执行计划走了IM，但是逻辑读是59，远高于默认的走索引+行式存储的执行计划，可见在数据离散度较高，且通过索引条件过滤的扫描场景中，IM特性对性能并没有提升，传统的索引+行式存储的执行计划已经足够，在默认情况下还是会根据查询索引返回rowid的方式查找数据。

3.IM压缩比测试

由于IM压缩是基于重复数据删除的压缩， 对300000条数据但不同数据只有两条的im_gender表和同样有300000条数据但每条数据都不重复的im_table表进行压缩测试。

im_gender 表压缩比达3.56，而im_phone的压缩比只有0.98，反而增大了。由此可知，重复值高有助于提高压缩比，而几乎无重复值的表压缩效果就很差。

为了方便操作和对比，这里调用了DBMS_COMPRESSION.GET_COMPRESSION_RATIO对压缩比进行预估：

可见，压缩比随着压缩等级的提升而提升，这里选择被压缩的表是根据dba_objects create as出来的，数据分布有一定的代表性，但在实际操作中，压缩效果要根据具体情况具体分析。

目前，浙江移动X系统已经启用了IM特性，在启用特性之前，业务反馈查询缓慢，业务页面将超时设置由30s调整至60s的情况下，仍然频繁出现页面超时问题，业务高峰期的超时概率高达60%。经过深度分析发现业务SQL本身并无有效的过滤条件，最终只能定位到分区全扫查询，也就是无法单纯从优化SQL的方式上进行解决。同时数据库资源池上本身存在较多的pdb，大量的全表扫描消耗较多的IO资源，对其他的pdb也造成影响。

由于SQL承载的是分析类业务，通过分析该类表的统计信息发现数据离散度较低，比较符合IM的适用场景。在进行为期3周的严格测试和运维场景模拟实验后，最终通过变更完成Oracle12C的IM特性在生产中的首次应用。

优化效果：查询效率由几十秒至数分钟不等的查询时间，降低至200ms完成目标数据的扫描。目前使用1个月以上，应用反馈页面响应速度良好，页面最终的响应速度在3s左右，平均提速20倍以上，业务成功率高达100%。

相关SQL的执行计划前后对比：

启用IM前：

启用IM后：

物理读由168357多降为0，逻辑由168493降为24，cpu cost由29840降为2741，执行时间由6.65s降为0.23s, 效果显著。

压缩比情况：

压缩比在默认压缩级别下已经达到了5-12倍的压缩比例。

综合上述表现，IM特性在适用的场景下对性能的提升是非常明显的，在消耗相对少量额外内存的条件下实现了巨大的性能提升。

IM特性是需要不定期维护的，不仅需要关注IM中表是否加载成功，是否出现In-Memory Area不足的情况，在内存资源有限的情况下，还要优化IM中存放的表，及时添加需要的表，删除不需要的表。此外，还可以添加或删除IM表中的部分字段或分区，达到优化内存使用的目的。

1.IM添加和删除表

• 当IM特性开启后，往in memory列式存储中加表，命令如下：Alter table tab_name inmemory;

  注意，当执行以上命令时，Oracle并不会将表马上存入in memory中，而是需要通过一次查询操作触发。

  删除表的操作为：Alter table tab_name no inmemory;

• 假如一张表有许多字段，但查询的时候只用到其中的某些字段，那么，就可以只存储相关字段：Alter table im_table inmemory no inmemory(object_name);  --除去表的object_name字段

• 假如一张表有多个分区，可以除去其中的某些分区：Alter table im_table modify partition P_201601 no inmemory;  --除去表的P_201601分区

2.维护常用视图

该视图用来查看In-Memory Area内存区域的使用情况：

In-Memory Area 分两个子池，1MB POOL 用来存放列式数据，64KB POOL用来存放元数据和事务信息。POPULATE_STATUS表明当前子池加载状态，DONE表示所有数据已经加载完毕，POPULATING表示正在加载，而OUT OF MEMORY表示分配的空间不足，需要删表或增大In-Memory Area。

该视图用来查看IM中存放段的情况：

可以用来查看inmemory中添加了多少表，占用多大内存空间等。

该试图用来查看IM中存放的字段情况：

可以看到object_name字段的inmemory_compression 显示为no inmemory，表明该字段并未加入IM。

3.其他注意事项

由于inmemory特性的开启是以实例为单位的，在RAC环境中，可以在不同的节点设置不同的inmemory_size, 加载不同的表，以适应不同节点访问不同数据的业务分配机制。

Inmemory_size参数可以在线修改，但是必须重启实例后才能生效，所以在启用初期就应该规划好，避免设置后出现内存不足、无法加表的情况。

Inmemory area是SGA中的一个静态子池，占用SGA，所以在加inmemory_size 的时候应该相应调大SGA，以免挤掉其他子池的空间。

原文发布时间为：2016-12-08

本文来自云栖社区合作伙伴DBAplus