Oracle高并发索引争用问题解决方法探讨

对于sequence 生成的主键索引，高并发时会出现严重的争用情况，下面AWR的前TOP4 等待事件，都是index contention相关的等待事件，非常严重：

为什么高并发会产生索引争用？

索引争用一般在字段值顺序递增的情况下表现最为严重，比如上面的由sequence生成的主键索引，因为索引值需要顺序存放，多个并发session都在争用一个index block，导致buffer busy waits和TX- index contention。这种情况在RAC环境会更加严重：同一个block又需要在节点间传来传去，gc buffer busy acquire和gc buffer busy release就是RAC环境下多出来的等待事件。

为什么data block没有那么严重的争用？

data block没有顺序存放的要求，ASSM管理的表空间，多个session 可以插入数据到不同的 block。

有很多文章介绍过索引争用的解决方法，大致如下：

1、反向键索引

2、将索引进行hash分区

3、增大PCTFREE

上面几种方法都有一些缺点（下面还有性能对比图）：

反向键索引：表相对小的时候性能尚可，表很大的时候，TPS会下降，响应时间也会变长，而且这种索引，只支持等值查询，不支持范围查询（>=,< ,between and等都不支持）

Hash分区索引：通过hash方式将字段值分散到不同的block，在RAC环境下，虽然可以提高TPS，但是响应时间会稍稍变长。

增大PCTFREE值：会占用更多的存储空间，更重要的是会占用更多的buffer cache内存，而且对缓解索引争用的效果一般。

针对这种情况，老虎刘原来所在的研发部门Real-World Performance（RWP）提供了一个最佳的解决方案：

原来插入记录的代码：

INSERT INTO log_big_clever（id,......） VALUES (big_clever_seq.nextval,......);

修改后的代码：在sequence前增加前缀（称之为smart index）

prefix1:=1E+23 * USERENV('INSTANCE');

prefix2:=1E+20 * mod(USERENV('SID'),100);

INSERT INTO log_big_clever (id,......)VALUES (prefix1+prefix2+ big_clever_seq.nextval,......);

注：本例定义的sequence的最大值为10^19-1

注意红色部分代码，因为使用了instance和sid的前缀，生成的字段值就不是单纯的递增，多个并发session可以插入到多达200个不同的index block，而且这些index block也不会在RAC的节点间传来传去，避免了gc等待。可以彻底的解决index contention的问题。

下面是RWP做的一个性能对比，对应的各柱状图分别是：

普通索引

反向键索引-小表

反向键索引-大表

hash索引-大表

hash索引-大表两节点RAC

smart索引（索引还是普通索引，只是插入的值增加了前缀）

由上图对比可知，smart index的TPS最高，响应时间最低，是一种完美的解决sequence主键索引争用的方法。这种方法的缺点就是需要改代码。

如果不想改代码，那么hash index是解决索引争用的一个很好方法，此外，可以考虑：减少并发、单节点连接、增大sequence的cache size（可以增大到10000，本文第一张图AWR显示的top 5th等待事件，就是cache size小所致）、或适当增大pctfree等几种方法。

如果你的英文听力还不错，可以听听我原来RWP的老板 Andrew Holdsworth关于index contention的一段视频（youtube，需翻墙，一共大概有20个关于性能方面的视频），虽然这些视频都是2年多以前录制的，但基本原理是不变的，地址：

https://www.youtube.com/watch?v=wQNPQGUwjbs