Oracle数据库常见等待事件原因和一般解决方法之(Enq: TM – contention)

通常我们判断Oracle数据库的性能是否有问题，需要衡量一些指标值。其中很重要的一个要素就是等待事件。

我们通常可以通过AWR报告或者是DBA_HIST_SYSTEM_EVENT视图来找到这些等待事件。

Top 5 Timed Foreground Events

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                                                           Avg

                                                          wait   % DB

Event                                 Waits     Time(s)   (ms)   time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

DB CPU                                                3          64.6

control file sequential read          2,485           1      0   23.6 System I/O

SQL*Net break/reset to client           710           0      0    1.9 Applicatio

log file sync                             9           0      5    1.1 Commit

SQL*Net message to client             1,480           0      0     .3 Network

今天开始整理一下一些经常发生的等待事件的发生原因和一般性解决方法。

---

Enq: TM - contention

P1: Enqueue信息

P2: Object#

P3: 表或者分区表信息

- 发生的条件:

当DML(INSERT,UPDATE,DELETE,SELECT..FOR UPDATE,MERGE,LOCK TABLE等)语句执行的时候，需要获得对象表的TM锁，也就是表级锁。这是为了防止DML操作时需要访问的表被更改。

在获得TM锁的过程中发生的等待就是enq：TM – contention。

 

- 发生的原因:

一般情况下有如下几种可能。

1. 对于某些表执行DDL语句时执行了DML操作。反之则不会，DML执行的时候执行DDL会发生ORA-54.

2. 使用了Lock table语句。比如，当正在执行某些DML语句的时候，执行了Lock table，由于需要获得TM锁而发生等待。

3. 当DML语句执行的时候，对同一个对象表进行并行DML时会发生。原因是并行DML需要获取各种类型的锁。

  关于各种并行DML语句类型和需要获取锁的类型可以参考Oracle官方的文档。

  https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/parameters-parallel-exec.html#GUID-C9A03D1A-66DA-4C73-813A-8AD5BC38952B

  Table 8-4 Locks Acquired by Parallel DML Statements

4. 外键约束上没有索引。

  在oracle早期版本的9i时代会出现这个问题，现在已经很少发生了。

5. INSERT / * + APPEND * / INTO 或者 SQL * Loader的direct path load。

   由于direct load不经过buffer cache直接读取数据文件，不允许表有任何变化，所以必须要获得排他的TM锁，获取的时候可能会发生enq：TM – contention。

 

- 问题发生时候的调查方法

问题发生的时候可以通过以下语句找到阻塞者的信息。

SELECT distinct w.tm, w.p2 OBJECT_ID, l.inst_id, l.sid, l.lmode, l.request
 FROM 
  ( SELECT p2, p3, 'TM-'||substr(p2raw,-8)||'-'||lpad(p3,8,'0') TM
      FROM v$session_wait 
     WHERE event='enq: TM - contention'
       and state='WAITING'
  ) W, 
  gv$lock L
 WHERE l.type(+)='TM'
   and l.id1(+)=w.p2
   and l.id2(+)=w.p3
 ORDER BY tm, lmode desc, request desc
;

注意有可能阻塞者并不只是一个。

如果发生时间点在过去，那么需要收集ASH报告，通过ASH报告的 EVENT 列来找到发生enq：TM – contention的信息，并根据BLOCKING_SESSION来特定阻塞者的session号。

 

- 解决方案

enq：TM-contention是一个和处理执行息息相关的等待事件。 只要根据上面的方法判断出发生enq：TM-contention'的session和阻塞者的session，

找到这些session 正在执行的sql语句，通过分时段执行就能避免该事件发生。比如一些能断定到需要长时间获得TM锁的处理，就放到空闲时间执行。