CREATE TABLE t
(
NAME VARCHAR2(20)
);
CREATE BITMAP INDEX idx_b_name ON t(NAME);
INSERT INTO t VALUES('U');
INSERT INTO t VALUES('C');
select sid from v$mystat where rownum<2; --198
select addr, sid, type, id1,id2, lmode,request,block from v$lock where sid=198 or sid=200;
1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000000001E2E9600 198 TM 77149 0 3 0 0
3 000007FFB9CE8488 198 TX 65561 1133 6 0 0
--这个时候登录第二个session
select sid from v$mystat where rownum<2;
200
INSERT INTO t VALUES('U');
select addr, sid, type, id1,id2, lmode,request,block from v$lock where sid=198 or sid=200;
1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000007FFBDC56588 200 AE 100 0 4 0 0
3 0000000026980C10 198 TM 77149 0 3 0 0
4 000007FFB9CE8488 198 TX 196617 1336 6 0 0
这个时候在SESSION2中
INSERT INTO t VALUES('E');
select addr, sid, type, id1,id2, lmode,request,block from v$lock where sid=198 or sid=200;
1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000007FFBDC56588 200 AE 100 0 4 0 0
3 000000002697E370 200 TM 77149 0 3 0 0
4 000000002697E370 198 TM 77149 0 3 0 0
5 000007FFB9CC9678 200 TX 65568 1131 6 0 0
6 000007FFB9CE8488 198 TX 196617 1336 6 0 0
SEssion不阻塞
这个时候继续在session2 做插入
INSERT INTO t VALUES('U');
1 000007FFBDC55D50 198 AE 100 0 4 0 0
2 000007FFBDC56588 200 AE 100 0 4 0 0
3 000007FFBDC56658 200 TX 196617 1336 0 4 0
4 0000000026980C10 200 TM 77149 0 3 0 0
5 0000000026980C10 198 TM 77149 0 3 0 0
6 000007FFB9CC9678 200 TX 65568 1131 6 0 0
7 000007FFB9CE8488 198 TX 196617 1336 6 0 1
这个时候Insert发生了阻塞 session2在等seesion1释放资源
在这个时候发生一个insert 阻碍了 INSERT 的操作 造成它的原因就是 BITMAP
SELECT OBJECT_ID FROM User_Objects WHERE object_name='IDX_B_NAME' ; --77150
alter session set events 'immediate trace name treedump level INDEX_OBJECT_ID';
alter session set events 'immediate trace name treedump level 52563'