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

作者介绍

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

引言  

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

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

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

session级sql解析是oracle最开始采用过的技术。

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

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

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

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

高并发的dml引发的问题

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

  1 enq: tx - allocate itl entry

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

可能的原因有：

initial trans设置过小。

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

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

解决思路：

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

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

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

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

小知识点：

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

  2 enq: tx - index contention

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

解决思路：   

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

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

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

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

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

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

1）alter system set events '43822 trace name context forever,level1';

2）event 43822启用后，对于root block的split进行了增强， 不会超过5次的index block reclamation，oracle就会去申请分配新块了。

背景知识：

oracle在索引split时中寻找可复用的free block的过程如下：

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

不能重用的原因有2个：   

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

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

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

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

  3 enq: hw –contention

table的high watermark（即高水位线）标识table segment中已用空间和未用空间的边界，具体来讲，hwm以上的block的状态是：unformattedand have never been used； hwm以下的block的状态是：allocated, but currentlyunformatted and unused、 formatted and contain data、formatted and empty because the data was deleted。当hwm以下的block都无空闲空间可以使用时，oracle会推进hwm来申请分配新的block到segment里面，而hw enqueue锁被用来管理推进hwm分配新空间时的串行操作。

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

解决思路：  

删除无用索引。

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

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

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

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

确保使用assm (automatic segment spacemanagement) tablespace。

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

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

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

另外针对使用assm表空间的lob有一个bug 6376915注意检查是否已applied fixed patch，并且要通过设置event来启用。此event用于控制1次lob chunk回收操作时的chunk个数（default是1），进而可以减少hwm enq等待发生的次数。

event="44951 trace name context forever, level < 1 -1024 >"

  4 enq: us –contention

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

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

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

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

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

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

设置event让smon不会自动将undo segment offline：

alter system set events '10511 trace name context forever, level1 '; 

将_undo_autotune临时设置为false，以避免当undo tbs很空闲时，oracle自动将undo retention调得很大，提前占用过多undo segments。

设置_highthreshold_undoretention，虽然允许oracle自动调整undo retention，但是为它设置一个天花板，不会过份地受maxquerylen的影响。

本文重要提示：

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

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

总结

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

<b></b>

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