等待事件enq TX row lock contention分析(三)

2 1065 gsf39j000n6ys 1015 enq: TX - row lock contention name|mode 1415053316 Application WAITING

SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE

1 1015 74 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 2723168908 6 Idle 0 24 WAITING

2 1065 85 enq: TX - row lock contention name|mode 1415053316 54580004 usn<<16 | slot 524294 00080006 sequence 106695 0001A0C7 4217450380 1 Application 0 15 WAITING

www.2cto.com

--得到sql_id值，查询出SQL信息。

select sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 'gsf39j000n6ys';

SQL_TEXT EXECUTIONS S.ELAPSED_TIME/1000000

1 0 11.988711

--再查询锁信息

select * from v$lock where sid in (1015, 1065) order by sid, type;

ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK

1 31AA5BC4 31AA5BDC 1015 TM 191499 0 3 0 24 0

2 30D135B0 30D136CC 1015 TX 524294 106695 6 0 24 1

3 31AA5C88 31AA5CA0 1065 TM 191499 0 3 0 15 0

4 32034928 3203493C 1065 TX 524294 106695 0 4 15 0

5 30E30A50 30E30B6C 1065 TX 458798 92446 6 0 15 0

在数据库的awrrpt中我还看到关于某个表的大量insert操作，每次操作执行时间都很长，这要么是应用逻辑问题，要么就是bitmap索引记录更新等待。开发人员证实逻辑问题不可能，查询表的索引状况，确实有bitmap索引。