一次客户诡异的TM锁表问题

一：问题背景：

XX医院客户反馈主要问题发生时间为早上9点开始，数据库有锁。

应急措施，开发商的工程师进行会话查杀。

二：结论：

常见TM锁原因：

出现TM锁的会话主要为DML语句，未发现针对该表的DDL，该表也不存在外键约束导致的TM锁，同时该表的DML语句也未带有APPEND和FOR UPDATE等会导致TM锁的关键字。

BUG：

出现TM表锁的会话，存在block_session。这些block_session的会话信息里面发现，是由于数据库系统后台进程EMON，处理速度无法满足通知生成速度，导致大量WAIT FOR EMON TO PROCESS NTFNS 等待事件，疑似遭遇bug 9735536，可以根据文档Doc ID 1257264.1的解决方案，尝试解决。

三：分析过程：

通过数据库内部分析，主要TM锁导致。问题时间情况如下：

TM锁可能主要原因如下：

1. 表上的DDL：数据库历史SQL中，未发生存在该表的DDL
2. 涉及表的INSERT语句存在APPEND：数据库历史SQL中，未发生存在该表的APPEND语句
3. 涉及表的SELECT语句存在FOR UPDATE：数据库历史SQL中，未发生存在该表的FOR UPDATE语句
4. 外键约束：该表没有任何关联的外键
5. BUG

前4个原因均不成立，与客户确认，也未有对表的DDL。

查询9：00-11：00之间TM锁开始时间为：9：47  到10：16。

查看TM等待事件对应的SQL统计排序如下：

查找了TM等待事件对应的SQL语句（选取最多的3个）：都是对CIS_EXAM_REQUEST表的DML

查看TM等待事件对应的对象P2是296570（和上述语句一样：CIS_EXAM_REQUEST）

 

查看历史视图发现从9：47开始有了TM等待事件对应的session的blocking_session ：3327

查看阻塞会话blocking_session ：3327的会话信息：

1. SQL：SELECT WDHIS.PG_KTAP_SYS_PARAMETER.F_GET_PARAMETER(:code, :parmName) FROM DUAL

SQL语句是执行一个函数。

1. 涉及的主机名和程序：详见下图。
2. Current_obj#=296570(就是堵塞的那张表CIS_EXAM_REQUEST)，与TM等待事件的表对应
3. 主要的等待事件：EMON 进程 的 WAIT FOR EMON TO PROCESS NTFNS 等待事件。

具体信息详见下图：

关于该等待事件的说明：

首先 EMON 进程是 事件监控进程，以大量子进程形式存在 EMNC 进程是协调进程，Ennn 是从属进程。

V$EMON 记录了所有子进程（Slave Process）的使用情况。在操作系统的进程名称类似 e001 ,e002 这样。

文档记录其作用是：执行数据库事件管理和通知 （Performs database event management and notifications）

发生原因：

只有定期通知才会出现此问题。当入队速率快于EMON可以处理的速率时，通道被填满，入队者将等待EMON释放通道。

由于这样的通知不会溢出，并且当缓冲区已满时，流控制会启动并阻止EMON发送新的通知，直到有更多的缓冲区来处理挂起的通知。这会阻止其他会话对“等待EMON处理ntfns”上的订阅表执行DML。

从该前面视图可以看到对应的这里提到的

订阅表：就是current_obj# 296570（CIS_EXAM_REQUEST）

执行DML：前面SQL那张图里面有很多的对应这张表的update，insert等。

查询ORACLE官网，疑似遭遇Users Blocked and Waiting on 'Wait for EMON to Process Ntfns' When Using DCN (Doc ID 1257264.1)

可以参考该文档，通过修改2个隐含参数来尝试解决。

其他建议：

1. 建议后续如果有大量TM异常等待，麻烦尽快联系我们同事，做一个全局hanganalyze进行更有针对性的确认。
2. 该问题如果只是出现一次，建议先观察观察，等再次出现的时候，做hanganalyze进一步分析。
3. 如果该问题反复出现，可以先尝试进行Doc ID 1257264.1解决方案的修复，观察效果