Oracle数据库性能优化之Enq: TM – contention

Oracle数据库技术 2021-04-04

4742

编者按：

本文作者系流浪的金鱼（花名），甲骨文数据库工程师。个人主页：https://blog.csdn.net/rishairu1，经其本人授权发布。

【免责声明】本公众号文章仅代表个人观点，与任何公司无关。

通常我们判断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
;