end:TX-row lock contention 等待事件分析过程与调优方法案例一则-优快云博客

5月20号上午9：00到10：00之间，某项目组进行数据质检作业。效率异常低下，几乎处于停滞状态。果断收集了9－10点的AWR报告，现在跟着我，通过AWR报告来发现问题。

拣关键的信息看，自然先看TOP 5 EVENTS：

排第一位的是end:TX-row lock contention。这是一个行级锁的争用，一般在以下三种情况下会产生该事件：

第一种情况：当两个会话对同一行进行更新时，ORACLE为了保证数据库的一致性，加了一个TX锁，这时另一个或多个会话必须等待第一个会话commit或rollback，否则会一等待下去，这是最为常见的一种模式！

第二种情况：两个或多个会话向具有唯一主健索引的表中插入或更新相同的数据行，既然是唯一主健索引，那么先获得插入的session以TX排它锁模式进行添加，此时其它session只能等待，也是常见的一种情况

第三种情况：两个或多个会话插入或更新具有位图索引的列，跟据位图索引的特性，一个索引键值对应多个数据行的rowid值，此种情况下也是以TX模式访问，一般在OLTP系统中很少有位图索引，但不排除个别系统，本例中不存在位图索引。

该事件在1小时以内一共出现了100次，次数倒是不多。不过当看到每次要等待61832ms时，小伙伴表示惊呆了，一等就是一分多钟，100次就是100分钟。在60分钟的运行时间里，等待花了100分钟，这绝对是巨大的问题。

那如何找到始作俑者呢？即然是row lock等待，那我们直接去看哪些对象（可能是表，也可能是索引）上发生这种等待的次数最多，直接跳到segments by row lock wait:

Segments by row lock waits一共列出了4个segment，等待总次数为185次。185次？TOP 5 EVENTS中的end:TX-row lock contention不是只等待了100次么？事实上，end:TX-row lock contention中只列出了等待时间超过0.001s的等待，而Segments by row lock waits列出了所有的等待。在本例中，说明有85次的row lock waits小于0.001s。

等待次数最多的segment为TBCHECK_CHECKITEM，是一个表对象。

排第二次的segment为SYS_IL0000086962C00006$$，是一个索引对象。通过名称可以判断，这是一个lobindex。我们去数据库里查询，该lobindex位于哪个表的哪个字段之上：

select table_name,column_name,index_name,segment_name from user_lobs t where index_name in ('SYS_IL0000086962C00006$$')

查询结果如下：

仍然是TBCHECK_CHECKITEM表。另外可知，该表的f_ruleitem字段为LOB字段。

到目前为止，我们知道了造成end:TX-row lock contention是由哪个对象引起的。但我们仍然不知道是在对该对象做什么操作时引起的。我们继续分析。

在AWR报告的Time Model Statistics部分，我们分析各项statistic name耗时占比，如下图：

SQL执行用了92.78%的时间，而有效的DB CPU时间只占4.67%，说明SQL执行大多数时间都用在了等待上。既然是SQL执行用了最多的时间，接下来，我们就去分析最耗时的SQL语句。

此处还要注意，解析时间用了181s，这其中硬解析占了169s。虽然相对于SQL执行时间来说，这点儿时间不算什么，但这里仍然需要警惕。要分析过高的硬解析是否是由于SQL复用率过低导致的。通过观察”Execute to Parse %”以及” % SQL with executions>1”的值，大致可以证明：SQL复用率过低。

还是回到主要矛盾上来，接下来，去看看最耗时的SQL语句：SQL ordered by Elapsed Time

选择第1-4，以及第6行，根据SQL_ID，查询到对应的SQL语句：

SQL_ID	SQL
4h0gfv3h27b95	UPDATE TBCHECK_CHECKITEM SET F_RULEITEM= :content WHERE ID = 1392
b39dw09mjdxqj	UPDATE TBCHECK_CHECKITEM SET F_RULEITEM= :content WHERE ID = 1394
33swqrzuv610c	UPDATE TBCHECK_CHECKITEM SET ID=1394, F_NAME='耕地后备资源图层属性一致性检查', F_DESC='', F_RULENAME='FeatValueHBTCCheck', F_KVSETTING='', F_ERRORCOUNT=0 WHERE ID = 1394
8g1g6tf3scb6f	UPDATE TBCHECK_CHECKITEM SET ID=1392, F_NAME='标准表格命名一致检查', F_DESC='', F_RULENAME='TableNameCheck', F_KVSETTING='', F_ERRORCOUNT=0 WHERE ID = 1392
03a4z4mhwup70	DELETE FROM GDHBSDE.HBTC WHERE F_XZQHDM = 130534

前4行都是对同一个表，即TBCHECK_CHECKITEM进行更新，仍然是TBCHECK_CHECKITEM，与之前对ROW LOCK WAIT结果吻合了。从前4行至少可以看出以下以个问题：

1） 1，2两行句式相同，仅字面量不同，导致SQL_ID不同，是不使用绑定变量导致SQL复用率低的典型案例；

2） 3，4两行名式相同，SQL_ID不同，同样是不使用绑定变量导致SQL复用率低的典型案例；

3） 1，4两行对同一条记录进行更新，极有可能导致ROW LOCK WAIT；

4） 2，3两行对同一条记录进行更新，极有可能导致ROW LOCK WAIT

在作出调优计划之前，自然需要跟开发人员沟通。问一下，为什么要这样设计？这些SQL语句在执行什么样的业务？

沟通结果让人大吃一惊。这些SQL原来仅仅是埋在代码里的调试代码，是最开始用于测试用的，后来发版本时忘了注释掉。其实TBCHECK_CHECKITEM是一个检查项的字典表，根本没有更新的必要，这些UPDATE语句更新后的值仍然是更新前的值。但就是这些UPDATE，不仅造成了并发时的ROW LOCK WAIT，还导致了并发时的enq: HW – contention,即TOP 5 EVENTS中排第二位的等待事件。

enq: HW – contention是为了防止多个进程同时修改表的HWM（即高水位线）而产生的等待事件。进程若想移动HWM的进程必须获得HW锁。Oracle高水位线标志着该线以下的block均被Oracle格式过，通俗一点讲就是该高水位线以下的block都被Oracle使用过。通常在执行INSERT操作时，有时在执行UPDATE操作时，当高水位线以下BLOCK不够用时，ORACLE将会推进高水位线。

查询近1小时之内的产生enq: HW – contention等待的SESSION。

select p1,p2,p3 from v$active_session_history where event='enq: HW - contention'

通过P3，可以用DBMS_UTILITY将其转换为文件和BLOCK；

select dbms_utility.data_block_address_block(140003563) file,dbms_utility.data_block_address_file(140003563) block from dual;

而后，通过file#和block#定位对象（借助dba_extents字典表），查到该对象为：“SYS_LOB0000086962C00006$$”。而通过上文对TBCHECK_CHECKITEM表的查询，可知该对象即为F_RULEITEM字段所对应的LOB段。

那为什么更新LOB段，会导致HWM的推进呢？这与LOB的更新机制有关。LOB实现读一致性的方式与其它信息所用UNDO实现读一致性的方式不同。LOBSEGMENT并不使用UNDO来记录其修改，而是直接在LOGSEGMENT本身中维护信息的版本。LOBINDEX会像其它段一样生成UNDO，但是LOGSEGMENT不会。相反，修改一个LOB时，ORACLE会分配一个新的CHUNK，并且仍保留原来的CHUNK。如果回滚了事务，对LOB索引所做的修改会回滚，索引将再次指向原来的CHUNK。正是由于修改LOB时，原来的数据保持不动，另外会创建新数据，创建新数据可能就会推进高水位线。当多个进程在同时创建新数据，同时推进高水位线时，争用就产生了。

说完了SQL语句的前4条，再来看第6条。第6条SQL语句单次执行的时间竟然达900多秒。通过与开发人员沟通，知道这一过程是删除分区表某个分区的所有数据。DML会产生大量的REDO与UNDO，因此对于整个分区的删除，可以采用直接删除分区的方式。

针对上文中提到的问题，给出针对性的解决办法：

1) 对于UPDATE TBCHECK_CHECKITEM表导致的等待，由于该SQL无实际作用，直接注释掉；

2) 对于大量硬解析的问题，再大量调整代码工作量过大，修改数据库参数，使其针对句式相同的SQL，强制转换成绑定变量

alter system set cursor_sharing='FORCE' scope=spfile;

经调整之后，质检过程中TOP 5 EVENTS中排前两名的等待事件消失。硬解析比率大大降低。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31340387/viewspace-2108429/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31340387/viewspace-2108429/