【知识点解释】什么是Oracle的等待事件
TM Enqueue (DML Enqueue)是再执行DML操作修改表时由事务获取的。DML操作需要表锁，以防止与事务发生冲突的同时DDL操作。
当应用程序具有引用完整性并试图修改子/父表时，当外键上没有索引时，Oracle将在父/子表上获得额外的锁定。因此，建议为数据库中定义的所有外键添加索引。
TM锁上等待的最常见原因往往涉及外键约束，其中受约束的列没有被索引。Oracle还会等待TM锁，直到它被授予或请求超时或TM锁发生死锁检测。
问题描述： 2022-09-16 17:42 客户一重要系统数据库出现大量 enq:tm-contention 等待事件。
环境描述： AIX 6.1 + Oracle 11g RAC
问题原因： 对 t01_account 表更新主键，导致引用该表主键作为外键的多个子表上发生了 enq:tm-contention 等待事件。
临时解决方案：与业务人员确认后 kill update t02_account 表的那条会话。
enq:TM-contention等待的发生，发生TM锁争用的情况如下：
1. 修改无索引外键(foreign key)的父键时
2 . DML与DDL之间的TM争用
3. LOCK TABLE 引起的TM锁争用
4. direct load工作引起的TM锁争用
分析过程：
1、 查看等待时间，17：42开始出现了 enq:tm-contention 等待事件
16 ：30后核心数据库主要等待事件中 enq:tm-contention 最多
2、 AWR 报告
节点1：
节点2：
该数据库2个节点top 10等待事件中 enq:tm-contention 均占第1位，节点1该等待时间占DB time的69.3%，节点2是79.9%。
3、 查询等待事件的p1,p2,p3参数
P1 为mode 为3 ，4 ，5
p2 是数据库对象号242764,242895,242223,224171,241960,243602,242389,241986,242222,249589,242942,242368
SQL>select * from dba_objects o where O.OBJECT_ID= object_id;
P3 为0 表示等待的为表或分区的锁。
4、 检查对象之间主外键关系 t02_account是多个表的父表。
可以使用一下脚本
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
/
-the end-
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28373936/viewspace-2918797/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Oracle数据库性能诊断与优化工具全景指南（11g、12c、19c与26ai）
- Oracle 19c 本地索引分区变为 UNUSABLE 后的空间占用验证
- Oracle中建立到CDB和PDB的连接
- Oracle 19c CDB中如何创建PDB
- 如何获取Data Pump dump file文件
- 19c CDB架构下使用具有SYSBACKUP权限用户执行DataPump-expdp报ORA-01045和ORA-39406
- IMPDP时有2张表报ORA-31693 ORA-38500
- Oracle中建立到CDB和PDB的连接
- 19c 多租户架构下数据库备份
- Oracle的特权管理用户终止其他用户发起的数据泵作业
