外键无索引导致oracle数据库锁的经典有关问题
日期：2014-05-16 浏览次数：20425 次
外键无索引导致oracle数据库锁的经典问题
2年前的事情了，现在总结一下，至于深层次的外键索引导致数据库锁的原因网上文章比较多，这里就不再详细介绍了
1，数据库响应慢，登录后查看大量enq锁存在
2,查看被锁住的会话执行的sql语句，一般是下面几个
3，由于锁的对象不一，且被锁住的有insert语句，因此怀疑为外键没有索引导致，使用tom给的脚本查看
2年前的事情了，现在总结一下，至于深层次的外键索引导致数据库锁的原因网上文章比较多，这里就不再详细介绍了
1，数据库响应慢，登录后查看大量enq锁存在
SQL> SELECT 2 DECODE (request, 0, 'Holder: ', 'Waiter: ') status, SID, 3 inst_id,ctime, id1, id2, lmode, request, TYPE 4 FROM gv$lock 5 WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv$lock WHERE request > 0) 6 ; STATUS SID INST_ID CTIME ID1 ID2 LMODE REQUEST TY -------- ------ ---------- ---------- ---------- ---------- ---------- ---------- -- Waiter: 1047 2 1932 23285 0 0 2 TM Waiter: 1039 2 1158 23285 0 0 2 TM Waiter: 1038 2 1659 23285 0 0 2 TM Waiter: 1036 2 1601 23285 0 0 2 TM Waiter: 1028 2 893 23285 0 0 2 TM Waiter: 1025 2 1854 23285 0 0 2 TM Waiter: 1024 2 1872 23285 0 0 2 TM Waiter: 1020 2 2845 23285 0 0 2 TM Waiter: 1018 2 2703 23285 0 0 2 TM Waiter: 1004 2 1689 23285 0 0 2 TM Waiter: 998 2 2934 23285 0 0 2 TM Waiter: 981 2 1959 23285 0 0 2 TM Waiter: 979 2 1813 23285 0 0 2 TM Waiter: 978 2 2083 23285 0 0 2 TM Waiter: 971 2 1723 23285 0 0 2 TM Waiter: 969 2 2668 23285 0 0 2 TM Waiter: 963 2 2920 23285 0 0 2 TM ......
2,查看被锁住的会话执行的sql语句，一般是下面几个
sql1 1fjf8sujb2wuw insert into WWW_VARIABLEINSTANCE (NAME_, CONVERTER_, TOKEN_, TO KENVARIABLEMAP_, PROCESSINSTANCE_, STRINGVALUE_, CLASS_, ID_) va lues (:1, :2, :3, :4, :5, :6, 'S', :7) sql2 6ukh0kxukjthv insert into WWW_TOKEN (VERSION_, NAME_, START_, END_, NODEENTER _, NEXTLOGINDEX_, ISABLETOREACTIVATEPARENT_, ISTERMINATIONIMPLIC IT_, ISSUSPENDED_, NODE_, PROCESSINSTANCE_, PARENT_, SUBPROCESSI NSTANCE_, ID_) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14) sql3 dgr3mp4cc6sjx insert into WWW_COMMENT (VERSION_, ACTORID_, TIME_, MESSAGE_, T OKEN_, TASKINSTANCE_, ID_) values (:1, :2, :3, :4, :5, :6, :7) sql4 4fypdt56k14a3 delete from WWW_SWIMLANEINSTANCE where ID_=:1 sql5 g2hntwxrq2502 delete from WWW_TASKINSTANCE where ID_=:1 sql6 agjsdnk7951hy delete from WWW_MESSAGE where TOKEN_=:1
3，由于锁的对象不一，且被锁住的有insert语句，因此怀疑为外键没有索引导致，使用tom给的脚本查看
通过以上脚本分析，确有很多外键没有索引
COLUMN COLUMNS format a30 word_wrapped
COLUMN tablename format a15 word_wrapped
COLUMN constraint_name format a15 word_wrapped
SELECT TABLE_NAME,
CONSTRAINT_NAME,
CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||
NVL2(CNAME3, ',' || CNAME3, NULL) ||
NVL2(CNAME4, ',' || CNAME4, NULL) ||
NVL2(CNAME5, ',' || CNAME5, NULL) ||
NVL2(CNAME6, ',' || CNAME6, NULL) ||
NVL2(CNAME7, ',' || CNAME7, NULL) ||
NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
FROM (SELECT B.TABLE_NAME,
B.CONSTRAINT_NAME,
MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
