Oracle高并发系列1：DML引起的常见问题及优化思路

[url]http://click.aliyun.com/m/21918/[/url]

作者介绍

王鹏冲，平安科技数据库技术专家，浸淫数据库行业十多年，对Oracle数据库有浓厚兴趣，也对MySQL、MongoDB、Redis等数据库有一定架构和运维经验，目前正沉迷在PostgreSQL数据库与Oracle数据库的PK之中，重点在关系型数据库的分布式架构研究。

引言

Oracle数据库是设计为一个高度共享的数据库，这里所说的“共享”，可以从数据库共享内存、后台进程、cursor、执行计划、latch等方面去理解。Oracle如此设计的目的是以最小的系统开销、最大化地支持更多的并发会话。也是基于这个设计思想，所以Oracle单个实例的垂直扩展能力一直是DB领域内的佼佼者。

之前曾经看到PG大牛的文章分析关于Oracle的CursorPinS为什么不会在PostgreSQL里面出现，其主要原因是PostgreSQL的执行计划不是全局共享的，而Oracle里面同样的Cursor在不同session间一般情况下都是可以共享的（Oracle在某些条件下会也触发重新硬解析）。这样的设计客观来讲其实各有优劣，虽然PG的plancache是不同会话不共享的，避免了高并发时不同会话对同一个cursor产生争用，但是也意味着同样的并发会话数的情况下，PG的会话所需求的cache会更多，而且每个会话都至少要parse一次；或者反过来说同样的资源限制的前提下，Oracle支持的并发数更高。

引用一位Oracle7的OCP，资深Oracle老司机的一段话：“早期Oracle就是使用session私有内存，但当负载并发增加时，内存消耗成了问题，而且执行计划无法共享，增加率parse时间，对于OLTP系统parse时间的增加对于整体执行时间影响较大。因此Oracle基于这一点进行了优化，包括sessioncachedcursor和sharedpool等，减少了SQL执行过程中的parsingtime和planningtime。但没有免费的午餐，肯定会有其它消耗，类似内存结构的并发保护上的成本。总之：

session级SQL解析是Oracle最开始采用过的技术。

任何应用都要针对其所采用的数据库的特点进行好的设计。

这里不探讨哪个数据库更NB，每种数据库技术的发展会受多种因素的影响，包括商业战略、市场需求、软硬件技术成熟度等。我们采用Oracle多年，对于Oracle数据库有深厚的感情，但是目前也同时义无反顾地投入开源数据库和NoSQL的怀抱，技术无好坏，最适合应用场景的就是最好的。这里只重点探讨，当Oracle数据库的这些“共享”资源，遭遇高并发时的问题发生的原因和应对措施。

这里谈的是思路，不是具体的命令。

这里的处理方法，是基于过往发生过的实际案例总结而来。

高并发的DML引发的问题

Oracle的表是堆表，索引是B树，当对表做DML时，Oracle会对table的block进行操作，同时也对索引树的block进行维护，那么当同一时间有大量会话都需要对索引（或表）的同一个block做维护时，就会产生索引（或表）上面的争用。当出现争用时，v$session_wait显示了当前的会话正在等待的eventname。

1enq:TX-allocateITLentry

这个等待事件表明，当前的会话正在等待一个block上面的事务槽的分配，可能是tableblock或indexblock。

可能的原因有：

Initialtrans设置过小。

并发DML太高，ITLslot被其它会话占用还未释放、且该block上也无空闲空间可以增加新的ITLslot，故当暂无free的ITLslot可供使用，后续的会话只能等待。

设置合理，并发也不是非常高，但是正在运行的语句效率发生了变化，导致holdITL的时间变长，进而引发了后续的拥堵。

解决思路：

已有的索引，进行Rebuild，重建时增加initrans，比如从16->32->64（重建索引时若加大pctfree，只是刚重建完毕为每个block预留了更多空闲空间，但是随着后续索引的维护这些空闲空间有可能还会被占用）。

新建的索引，修改数据库开发规范，新建索引时默认initrans为16；

Tableblock一般很少出现上述问题，但是生产过程中我们的一个非常高频update的table也遇到过此问题，所以最后也修改了规范新建表时默认指定initrans为6。

如果确认是sql语句效率下降导致holdITL时间变长，那么分析sql效率下降的原因并优化。

小知识点：

在v$lock中若看到某个会话正request一个lmode为4的锁，其原因之一就可能是ITL等待造成的，其它原因可能是并发操作主键、位图索引、分布式事务等。

2enq:TX-indexcontention

这个问题一般发生在表在高并发insert操作时，等待在字段类型是日期、自增序列的索引block上。因为应用始终插入的都是最新（highkey）的值，导致这些索引一般都是右倾斜增长的，也就是说最近最频繁的操作都发生在索引最右边的那个叶子块上，叶子块的free空间很快被填满，然后叶子块要分裂，分裂过程总要去找freeblock，indexspliter的进程会持有一个enq:TX锁，其它并发insert的进程一般也正是需往最右边的这个indexleafblock去insert数据，所以都要等待这个spliter进程完成并释放这个锁。（竞争更加激烈时，甚至会在branchblock的split时产生）

解决思路：

删除无用索引。为什么把这个显而易见的措施放到第一位呢，其实是有来由的。很多开发人员其实并不知道一个表上若创建过多索引会对DML产生影响，只知道创建索引对查询带来帮助，有些夸张的甚至会为一个表的每个列上都创建单列或组合索引。但是事实证明，经过DBA的采样监控，很多索引可能一年半载都不会被用到，那么还不删除这些索引更待何时？

将索引改造为hash分区索引。原理是可以打散并发操作的叶子节点。

将索引改造为反序索引。原理同上，因为是reverseindex，同样可以打散highkey的叶子节点。

设置更小的blocksize，比如8k->4k-2k。原理一样，因为更小的索引block里面存放的条目更少，理论上减少了两个不同会话同时访问同一个block的几率，进而减少了争用。但是这个方案其实会有其它副作用，除非其它方案都不能考虑，否则不建议这个方案。

重建索引。为什么重建索引对这个问题能够带来帮助呢，因为重建索引后减少了索引的碎片，索引block变得更加紧凑，减少了indexleafblocksplit时寻找空块的时间，提高了Oracle进行索引分裂时的效率，进而可以减少等待时间。

如果indexcontention的对象不是leafblock，而是rootblock，则可以考虑通过以下方法激活索引的rootblock分裂时的优化：

1）altersystemsetevents'43822tracenamecontextforever,level1';

2）event43822启用后，对于rootblock的split进行了增强，不会超过5次的indexblockreclamation，Oracle就会去申请分配新块了。

背景知识：

Oracle在索引split时中寻找可复用的freeblock的过程如下：

Oracle不会一开始就让indexsegment申请分配新的空间（这会造成indexsegment的空间过度增长)，而是到该indexsegment的其它地方搜索是否存在可用的FreeBlock，这些FreeBlock的要求是status是75%-100%Free的，serverprocess会扫描这些75%-100%Free的block并确认这些block实际上是100%空的，如果找到100%FreeBlock则使用；如果没有则继续搜索，直到所有候选block都被检查过，这个行为叫做probesonindexblockreclamation。每次寻找空块并failed，oracle就会增加这个统计指标：“failedprobesonindexblockreclamation”。Oracle内部机制会控制要找多少次，不会去FULLSCAN所有indexblock的，failed超过一定次数后就会申请分配新的block。

不能重用的原因有2个：

可能这个块不是100%free的，而是70%~<100%free的，也就是找到的这个block上面还有几行或者多行索引记录，所以不能被重用来做split。

可能这个块上还有一些其它的activetransaction，所以它重用不了。

在这个过程中，Oracle还有机会找到的block其实已经是索引结构中的一个非空block，但是Oracle只会在splittingandrelinkingtoindexstructure之后才会发现这个block其实是illegal的选择，这个时候Oracle会回滚这个操作，这个统计记录在‘transactionrollback’inv$sysstat，然后继续寻找另外一个block。

Oracle进行找空块的过程中，如果这些块不在内存中，会增加物理读，如果这些块还需要做延迟块清除或者还要回滚，则需要触发更多系统递归操作，可见，如果“failedprobes”过多，split效率低下时，会直接导致indexcontention增加。

3enq:HW–contention

TABLE的HighWaterMark（即高水位线）标识tablesegment中已用空间和未用空间的边界，具体来讲，HWM以上的block的状态是：unformattedandhaveneverbeenused；HWM以下的block的状态是：Allocated,butcurrentlyunformattedandunused、Formattedandcontaindata、Formattedandemptybecausethedatawasdeleted。当HWM以下的block都无空闲空间可以使用时，Oracle会推进HWM来申请分配新的block到segment里面，而HWenqueue锁被用来管理推进HWM分配新空间时的串行操作。

显而易见，当高并发的insert发生时，甚至表中若有LOB字段时情况更糟，HWM的推进分配新空间的速度赶不上并发会话所需空间的速度时，就会发生在HW的enq上的等待。

解决思路：

删除无用索引。

改造为hash分区表。同一时间的并发的空间分配需求会被打散到多个分区段上。

提前手工allocatenew空间（可以做成定期自动任务）。

主动shrink回收可以重用的空间，避免业务高峰期的自动allocate竞争。

设置表空间更大的UNIFORMSIZE，每次allocate更多extent到表的HWM之上，避免HWM剧烈时偶尔还会等在表空间的extent分配上。

确保使用ASSM(Automaticsegmentspacemanagement)tablespace。

隐含参数_bump_highwater_mark_count，可以控制HWM每次推进的block个数。但是设置该隐含参数应该得到Oracle的支持，而且对其它小表有负面影响。

检查IO子系统性能，有时候IO性能的变化也会导致空间分配操作缓慢，进而引发等待。

LOB段空间的频繁重回收，可能也会导致该竞争，针对LOB可以适当增加chunk，每次分配更多空间；也可以主动allocate或shrink

另外针对使用ASSM表空间的LOB有一个Bug6376915注意检查是否已appliedfixedpatch，并且要通过设置event来启用。此event用于控制1次LOBchunk回收操作时的chunk个数（default是1），进而可以减少HWMenq等待发生的次数。

EVENT="44951TRACENAMECONTEXTFOREVER,LEVEL<1-1024>"

4enq:US–contention

这个等待事件通常说明会话在等待UndoSegment，注意等待的原因一般其实并不是因为UNDOTABLESPACE没有空间了，UNDO表空间不足会直接报ORA-30036（NOSPACEERRCNT）。

造成这个等待的典型场景有：

如果UNDO表空间是AUTOEXTEND的，则Oracle会自动调整undoretention，在尽量保持retention参数设定的undoblock保留期的基础上，还会尽量满足一些长查询的读一致性需求。那么当这个特性发挥作用时，很多UNDOsegment都被用在了长查询(MAXQUERYLEN)的支持上，当突发很多并发会话同时需要申请分配undosegment时，Oracle的回收机制（UNXPSTEAL）就会捉襟见肘。

大量active的undoblock正在回滚、无法重用，可能是由于不久之前刚kill了一个长事务造成的。

也可能是虽然有空闲空间，但是由于应用重启、或者准点抢售类的应用导致高并发事务进入数据库后，短暂时间内需要将大量的undoseg从offline变成online，而smon没有处理得那么快，故可能出现短暂的大量enq:US-contention，这个时候通常会伴随大量的'latch:rowcacheobjects'(onDC_ROLLBACK_SEGMENTS)。我们的一个保险类系统在双11抢售时后台数据库就曾经出现过这个问题。

解决思路：

如果预期要做抢售活动，可以提前维护，设置_ROLLBACK_SEGMENT_COUNT为一个较高的值，保持一定数量的undosegments始终是online状态。

设置event让SMON不会自动将undosegmentOFFLINE：

altersystemsetevents'10511tracenamecontextforever,level1';

将_UNDO_AUTOTUNE临时设置为FALSE，以避免当UNDOTBS很空闲时，Oracle自动将undoretention调得很大，提前占用过多undosegments。

设置_HIGHTHRESHOLD_UNDORETENTION，虽然允许Oracle自动调整undoretention，但是为它设置一个天花板，不会过份地受MAXQUERYLEN的影响。

本文重要提示：

上述所有隐含参数的介绍，一方面是为了加深对Oracle相关管理机制的了解，另一方面都是在常规手段包括应用层调优的手段无法奏效的前提下的应急方案，在生产环境启用之前请得到Oracle原厂的确认与支持，而且在高峰期或问题应急解决后务必要取消隐参。

不要随意在生产环境使用隐含参数，这是一个最基本的数据库运维原则！

总结

上面这些问题的解决思路其实都是治标不治本的，这些优化措施可能能够帮助你的系统度过当前的系统波峰，但是随着时间的推移当更大的波峰出现时，问题还会再次发生。优化“对数据库的需求”带来的效果永远大于优化“数据库所能提供的资源”，虽然有时候优化“对数据库的需求”的成本投入更高，但是投入与产出一般都是成正比的。从这个意义上来讲，若应用能够合理控制并发、系统架构中引入缓存层、采用异步队列处理机制、优化DB模型设计以及SQL写法等，这才是解决问题的根本之道。

本文来自云栖社区合作伙伴"DBAplus"，原文发布时间：2016-10-08

[url]http://click.aliyun.com/m/21918/[/url]