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

唐小丹、马力行 2016-12-08 10:04:15

 

来源:三墩IT人 订阅号

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

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

 

一、IM特性简介

 

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,该级别在有效压缩表的同时提供最佳的查询性能,数据库不对数据进行解压读取,而是采用数据字典压缩方式,即删除重复数据来减少内存使用。此外,其它更高的级别的压缩方式需要对数据进行压缩,会增加额外消耗。

 

二、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,以免挤掉其他子池的空间。

最新评论
访客 2017年06月29日

优化前后的执行计划cost值一致,图贴错了?

访客 2017年06月27日

感觉写着写着上厕所了。。突然停止

访客 2017年06月26日

<div class="content"><span class="blue f12">访…

访客 2017年06月23日

为什么小表驱动大表比较快呢?

访客 2017年06月20日

学习

活动预告