oracle最快访问行,平均提速20倍！Oracle 12c In-Memory最佳实践

来源：三墩IT人 订阅号

作者：唐小丹(浙江移动数据库管理员)

马力行(新炬网络数据库工程师)

一、IM特性简介

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

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

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

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

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

NO MEMCOMPRESS

MEMCOMPRESS FOR DML

MEMCOMPRESS FOR QUERY LOW

MEMCOMPRESS FOR QUERY HIGH

MEMCOMPRESS FOR CAPACIT LOW

MEMCOMPRESS FOR CAPACITY HIGH

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

二、IM特性测试

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出来的，数据分布有一定的代表性，但在实际操作中，压缩效果要根据具体情况具体分析。

三、IM生产实践

目前，浙江移动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特性是需要不定期维护的，不仅需要关注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.维护常用视图

>>>>

v$inmemory_area

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

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

>>>>

v$im_segments

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

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

>>>>

v$im_column_level

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

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

3.其他注意事项

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

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

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