Waits on this event typically occur because an index is missing on the column(s) containing a foreign key constraint. In this case Oracle is forced to acquire a TM lock on the child table during DELETE, INSERT and UPDATE statements. However, there are other cases where this can occur, e.g. a LOCK TABLE command is being used. Solutions Review all foreign key constraints to ensure corresponding indexes are in place. Script displays the problem table in the Objects tab for the SQL statement. Also review the Blockers tab to see what the blocker is doing. The following script will show all unindexed columns from foreign key constraints for a specific user and it can also be customized to include only the one table : SELECT * FROM ( SELECT c.table_name, cc.column_name, cc.position column_position FROM user_constraints c, user_cons_columns cc WHERE c.constraint_name = cc.constraint_name AND c.constraint_type = 'R' AND c.owner = upper('&&owner') and cc.owner = upper('&&owner') MINUS SELECT i.table_name, ic.column_name, ic.column_position FROM user_indexes i, user_ind_columns ic WHERE i.index_name = ic.index_name AND c.owner = upper('&&owner') and cc.owner = upper('&&owner') ) ORDER BY table_name, column_position;
oracle enq: tm,enq: TM - contention等待事件
最新推荐文章于 2024-02-04 16:38:35 发布
该博客讨论了Oracle数据库中由于外键约束缺少索引而引发的等待事件。当删除、插入或更新语句执行时,Oracle被迫在子表上获取TM锁。解决方案包括检查所有外键约束确保有相应的索引,并通过脚本找出未被索引的外键列。同时,建议查看阻塞者信息以了解其活动。
摘要由CSDN通过智能技术生成