一 环境
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来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-710115/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-710115/