enq:TM Contention等待事件的处理思路
今天分析下enq:TM Contention等待事件，用户反映系统很慢，导数操作十分卡，近问题比较频繁，希望分析下原因。通过对方提供ASH以及AWR报告，可以知道目前DBtime78% 花在该等待事件，从ASH看top SQL with top event发现一个SQL执行insert操作集中在一个时间段执行了2万多次，经过与业务沟通确定是业务集中导数需求。
下面我们分析该等待事件。来看看原文描述吧。
'TM' Enqueues and Parallel DML
A DML lock is a lock obtained on a table that is undergoing a DML operation (insert, update, delete). The purpose of a DML lock (data lock) is to guarantee the integrity of data being accessed concurrently by multiple users. DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations. TM locks are Table-level locks that protect the table from being modified mid transaction. A parallel DML operation's lock requirement is very different from serial DML. The available locks are controlled by the parameter 'DML_Locks'.
在事务期间该表的DML锁需要一直持有以保证完整性比如表不能缺少字段或者增加字段等，这个锁是表级锁。而这个锁的数量由参数DML_Locks 参数控制，锁的数量其默认值为Derived: 4 * TRANSACTIONS 。
从上述原理我们可以分析，如果该等待严重可以从两个角度解决：
1 应用insert操作在时间分布上打散，这个需要跟业务沟通一般很难控制。
2 修改数据库层面的参数，比如看看是否该表的外键没有索引，或者提高锁的数量。
我们通过脚本检查没有索引的外键。
SET ECHO off
REM NAME: TFSFKCHLK.SQL
REM USAGE:"@path/tfsfkchk"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM None -- checks only the USER_ views
REM --------------------------------------------------------------------------
REM This file checks the current users Foreign Keys to make sure of the
REM following:
REM
REM 1) All the FK columns are have indexes to prevent a possible locking
REM problem that can slow down the database.
REM
REM 2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking
REM problem the columns MUST be index in the same order as the FK is
REM defined.
REM
REM 3) If the script finds and miss match the script reports the correct
REM order of columns that need to be added to prevent the locking
REM problem.
REM
REM
REM
REM -------------------------------------------------------------------------
REM Main text of script follows:
drop table ck_log;
create table ck_log (
LineNum number,
LineMsg varchar2(2000));
declare
t_CONSTRAINT_TYPE user_constraints.CONSTRAINT_TYPE%type;
t_CONSTRAINT_NAME USER_CONSTRAINTS.CONSTRAINT_NAME%type;
t_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type;
t_R_CONSTRAINT_NAME USER_CONSTRAINTS.R_CONSTRAINT_NAME%type;
tt_CONSTRAINT_NAME USER_CONS_COLUMNS.CONSTRAINT_NAME%type;
tt_TABLE_NAME USER_CONS_COLUMNS.TABLE_NAME%type;
tt_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type;
tt_POSITION USER_CONS_COLUMNS.POSITION%type;
tt_Dummy number;
tt_dummyChar varchar2(2000);
l_Cons_Found_Flag VarChar2(1);
Err_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type;
Err_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type;
Err_POSITION USER_CONS_COLUMNS.POSITION%type;
tLineNum number;
cursor UserTabs is
select table_name
from user_tables
order by table_name;
cursor TableCons is
select CONSTRAINT_TYPE,
CONSTRAINT_NAME,
R_CONSTRAINT_NAME
from user_constraints
where OWNER = USER
and table_name = t_Table_Name
and CONSTRAINT_TYPE = 'R'
order by TABLE_NAME, CONSTRAINT_NAME;
cursor ConColumns is
select CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
POSITION
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;
cursor IndexColumns is
select TABLE_NAME,
COLUMN_NAME,
POSITION
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;
DebugLevel number := 99; -- >>> 99 = dump all info`
DebugFlag varchar(1) := 'N'; -- Turn Debugging on
t_Error_Found varchar(1);
begin
tLineNum := 1000;
open UserTabs;
LOOP
Fetch UserTabs into t_TABLE_NAME;
t_Error_Found := 'N';
exit when UserTabs%NOTFOUND;
-- Log current table
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, NULL );
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Checking Table '||t_Table_Name);
l_Cons_Found_Flag := 'N';
open TableCons;
LOOP
FETCH TableCons INTO t_CONSTRAINT_TYPE,
t_CONSTRAINT_NAME,
t_R_CONSTRAINT_NAME;
exit when TableCons%NOTFOUND;
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found CONSTRAINT_NAME = '|| t_CONSTRAINT_NAME);
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found CONSTRAINT_TYPE = '|| t_CONSTRAINT_TYPE);
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found R_CONSTRAINT_NAME = '|| t_R_CONSTRAINT_NAME);
commit;
end;
end if;
open ConColumns;
LOOP
FETCH ConColumns INTO
tt_CONSTRAINT_NAME,
tt_TABLE_NAME,
tt_COLUMN_NAME,
tt_POSITION;
exit when ConColumns%NOTFOUND;
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, NULL );
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found CONSTRAINT_NAME = '|| tt_CONSTRAINT_NAME);
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found TABLE_NAME = '|| tt_TABLE_NAME);
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found COLUMN_NAME = '|| tt_COLUMN_NAME);
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found POSITION = '|| tt_POSITION);
commit;
end;
end if;
begin
select 1 into tt_Dummy
from user_ind_columns
where TABLE_NAME = tt_TABLE_NAME
and COLUMN_NAME = tt_COLUMN_NAME
and COLUMN_POSITION = tt_POSITION;
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Row Has matching Index' );
end;
end if;
exception
when Too_Many_Rows then
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Row Has matching Index' );
end;
end if;
when no_data_found then
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'NO MATCH FOUND' );
commit;
end;
end if;
t_Error_Found := 'Y';
select distinct TABLE_NAME
into tt_dummyChar
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_R_CONSTRAINT_NAME;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Changing data in table '||tt_dummyChar
||' will lock table ' ||tt_TABLE_NAME);
commit;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,'Create an index on table '||tt_TABLE_NAME
||' with the following columns to remove lock problem');
open IndexColumns ;
loop
Fetch IndexColumns into Err_TABLE_NAME,
Err_COLUMN_NAME,
Err_POSITION;
exit when IndexColumns%NotFound;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,'Column = '||Err_COLUMN_NAME||' ('||Err_POSITION||')');
end loop;
close IndexColumns;
end;
end loop;
commit;
close ConColumns;
end loop;
if ( t_Error_Found = 'N' )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,'No foreign key errors found');
end;
end if;
commit;
close TableCons;
end loop;
commit;
end;
/
select LineMsg
from ck_log
where LineMsg NOT LIKE 'Checking%' AND
LineMsg NOT LIKE 'No foreign key%'
order by LineNum
/
这里没有发现问题，我们考虑增加dml_locks参数的方式增加锁的数量，并行需要更多的DML锁。
下面我们强调下我们的具体情况，这个情况跟MOS描述一致：
Resolving Issues Where Lock Contention for 'enq: TM - contention' Wait Event (TM Enqueue Contention) Using Parallel DML (Doc ID 1475340.1
Problem Confirmation:
The time spent actively in the local database is significant
Only certain sessions, queries or jobs are experiencing slowness (not throughout the database)
Parallel DML is in use
There are no 'Missing' Indexes on Foreign Key columns
Enqueue waits for 'enq: TM - contention' (the TM enqueue) are a significant component of the overall db time
“ANALYZE INDEX VALIDATE STRUCTURE” is not currently being performed on the Table experiencing TM enqueue contention
下面修改参数，从而增加更多的dml锁尝试解决问题。
alter system set dml_locks=4000 scope=spfile;
重启数据库。后续继续监控，问题缓解。
相关文章