等待事件enq TX row lock contention分析
通过这个SQL可以将p1转换为易阅读的文字。
我针对这三种情况，分别进行测试。
首先，我准备一下测试表和数据。
--创建测试表和数据
create table t_all_objs as select owner,object_id,object_name from all_objects where 0=1;
alter table T_ALL_OBJS? add constraint pk_t_all_objs primary key (OBJECT_ID);
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011701,'test1');
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011702,'test2');
commit;
第一种情况，不同会话同时更新同一条记录
--test1
--sid=1015
update t_all_objs set object_name='test11' where object_id=2013011701;
--sid=1065
update t_all_objs set object_name='test101' where object_id=2013011701;
--第三个会话中查询
select sid,sql_id,blocking_session,event,p1text,p1,wait_class,state from v$session where sid in (1015, 1065);
select * from v$session_wait where sid in (1015, 1065);
?? ??? SID??? SQL_ID??? BLOCKING_SESSION??? EVENT??? P1TEXT??? P1??? WAIT_CLASS??? STATE
1??? 1015??? ??? ??? SQL*Net message from client??? driver id??? 1413697536??? Idle??? WAITING
2??? 1065??? 6y6u0gx4qa5v5??? 1015??? enq: TX - row lock contention??? name|mode??? 1415053318??? 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??? 44??? SQL*Net message from client??? driver id??? 1413697536??? 54435000??? #bytes??? 1??? 00000001??? ??? 0??? 00??? 2723168908??? 6??? Idle??? 0??? 1306??? WAITING
2??? 1065??? 41??? enq: TX - row lock contention??? name|mode??? 1415053318??? 54580006??? usn<<16 | slot??? 655407??? 000A002F??? sequence??? 92680??? 00016A08??? 4217450380??? 1??? Application??? 0??? 1291??? WAITING
--得到sql_id值，查询出SQL信息。
select sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = '6y6u0gx4qa5v5';
?? ??? SQL_TEXT??? EXECUTIONS??? S.ELAPSED_TIME/1000000
1??? ?update t_all_objs set object_name='test11' where object_id=2013011701 ??? 0??? 1286.977282
--再查询锁信息
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??? 1306??? 0
2??? 30D135B0??? 30D136CC??? 1015??? TX??? 655407??? 92680??? 6??? 0??? 1306??? 1
3??? 31AA5C88??? 31AA5CA0??? 1065??? TM??? 191499??? 0??? 3??? 0??? 1291??? 0
4??? 32034928??? 3203493C??? 1065??? TX??? 655407??? 92680??? 0??? 6??? 1291??? 0
第二种情况，不同会话中同时插入主键字段相同的记录
--test2
--sid=1015
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test1');
--sid=1065
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11');
--第三个会话中查询
select sid,sql_id,blocking_session,event,p1text,p1,wait_class,state from v$session where sid in (1015, 1065);
select * from v$session_wait where sid in (1015, 1065);
?? ??? SID??? SQL_ID??? BLOCKING_SESSION??? EVENT??? P1TEXT??? P1??? WAIT_CLASS??? STATE
1??? 1015??? ??? ??? SQL*Net message from client??? driver id??? 1413697536??? Idle??? WAITING
2??? 1065??? bmv0jcs53zkad??? 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??? 59??? SQL*Net message from client??? driver id??? 1413697536??? 54435000??? #bytes??? 1??? 00000001??? ??? 0??? 00??? 2723168908??? 6??? Idle??? 0??? 69??? WAITING
2??? 1065??? 69??? enq: TX - row lock contention??? name|mode??? 1415053316??? 54580004??? usn<<16 | slot??? 458790??? 00070026??? sequence??? 92434??? 00016912??? 4217450380??? 1??? Application??? 0??? 12??? WAITING
--得到sql_id值，查询出SQL信息。
select sql_text,s.EXECUTIONS,s.ELAPSED_TIME/1000000 from v$sql s where sql_id = 'bmv0jcs53zkad';
?? ??? SQL_TEXT??? EXECUTIONS??? S.ELAPSED_TIME/1000000
1??? ?insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11') ??? 0??? 11.992511
--再查询锁信息
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??? 69??? 0
2??? 30D135B0??? 30D136CC??? 1015??? TX??? 458790??? 92434??? 6??? 0??? 69??? 1
3??? 31AA5C88??? 31AA5CA0??? 1065??? TM??? 191499??? 0??? 3??? 0??? 12??? 0
4??? 32034928??? 3203493C??? 1065??? TX??? 458790??? 92434??? 0??? 4??? 12??? 0
5??? 30E30A50??? 30E30B6C??? 1065??? TX??? 589865??? 106712??? 6??? 0??? 12??? 0
第三种情况，不同会话中同时bitmap索引列值相同的记录
--test3
--创建一个bitmap索引
create bitmap index ind_T_ALL_OBJS on T_ALL_OBJS (owner);
--sid=1015
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test11');
--sid=1065
insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011703,'test12');
--第三个会话中查询
select sid,sql_id,blocking_session,event,p1text,p1,wait_class,state from v$session where sid in (1015, 1065);
select * from v$session_wait where sid in (1015, 1065);
?? ??? SID??? SQL_ID??? BLOCKING_SESSION??? EVENT??? P1TEXT??? P1??? WAIT_CLASS??? STATE
1??? 1015??? ??? ??? SQL*Net message from client??? driver id??? 1413697536??? Idle??? WAITING
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
--得到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索引。
