等待事件enq TX row lock contention分析(二)
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