一 环境

OS:
 $cat /etc/redhat-release
uname -aCentOS release 5.4 (Final)
$uname -a
Linux dbserver 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux


DB:
 $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Nov 2 16:11:34 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set lines 150
SQL> COL PRODUCT FORMAT A55
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  10.2.0.4.0      Production
Oracle Database 10g Enterprise Edition                  10.2.0.4.0      64bi
PL/SQL                                                  10.2.0.4.0      Production
TNS for Linux:                                          10.2.0.4.0      Production

SQL>

二 描述

应用端向表case_info中插入数据.hang住不动.sql :select event,sid from v$session_wait where event not like 'SQL%' and event not like 'rdbm%'; 查看数据库端等待事件为 " enq: TM - contention ".于是关键词"enq: TM - contention" 查询metalink,无果.遂google发现此地址http://hi.baidu.com/james_yulei/blog/item/fc21b91a90a5f4b84bedbc42.html .问题与此链接地址描述一样.

三 结构

四 步骤

由于已经定位问题引的原因由于外键没有创建索引所致.因此找到相应外键并对其创建索引即可.
1.查询表之间的主外键关系

Select b.table_name 主键表名,
           b.column_name 主键列名,
           a.table_name 外键表名,
           a.column_name 外键列名
From (Select a.constraint_name,
                       b.table_name,
                       b.column_name,
                       a.r_constraint_name
          From user_constraints a, user_cons_columns b
         Where a.constraint_type = 'R'
           And a.constraint_name = b.constraint_name) a,
       (Select Distinct a.r_constraint_name, b.table_name, b.column_name
          From user_constraints a, user_cons_columns b
         Where a.constraint_type = 'R'
           And a.r_constraint_name = b.constraint_name) b
Where a.r_constraint_name = b.r_constraint_name

2.拼接创建索引的sql语句.
Select
'create index fk_'||a.table_name||'_RCI on '||a.table_name||'('||a.column_name||');'
  From (Select a.constraint_name,
               b.table_name,
               b.column_name,
               a.r_constraint_name
          From user_constraints a, user_cons_columns b
         Where a.constraint_type = 'R'
           And a.constraint_name = b.constraint_name) a,
       (Select Distinct a.r_constraint_name, b.table_name, b.column_name
          From user_constraints a, user_cons_columns b
         Where a.constraint_type = 'R'
           And a.r_constraint_name = b.constraint_name) b
 Where a.r_constraint_name = b.r_constraint_name
   and b.table_name = 'CASE_INFO';

个人总结

官方文档不熟,后将上传此等待事件描述. 20111102