1. bitmap索引
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
--session 1 SQL> create table t1_tx(id number,name varchar2(20)) ; Table created. SQL> insert into t1_tx select rownum,chr(97+mod(rownum,2)) from dual connect by level<=10; 10 rows created. SQL> commit; Commit complete. SQL> select * from t1_tx; ID NAME ---------- ---------------------------------------- 1 b 2 a 3 b 4 a 5 b 6 a 7 b 8 a 9 b 10 a 10 rows selected. SQL> create bitmap index idx_bitmap_name on t1_tx(name); Index created. SQL> select sid from v$mystat where rownum=1; SID ---------- 63 SQL> update t1_tx set name='tx' where id=3; 1 row updated. SQL> -- not commit -- session 2: SQL> select sid from v$mystat where rownum=1; SID ---------- 5 SQL> update t1_tx set name='bitmap' where id=5; --hang ---session 3 SQL> select sid, 2 chr(bitand(p1, -16777216) / 16777215) || 3 chr(bitand(p1, 16711680) / 65535) "Name", 4 (bitand(p1, 65535)) "Mode", 5 event, 6 sql_id, 7 FINAL_BLOCKING_SESSION 8 from v$session 9 where event like 'enq%'; SID Name Mode EVENT SQL_ID FINAL_BLOCKING_SESSION ---------- ---------------- ---------- ------------------------------ -------------------------- ---------------------- 5 TX 4 enq: TX - row lock contention 1qtvgrv88q2dj 63 SQL> |
2. 主外键关系，主键表插入数据不提交,外键表插入数据被阻塞
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
--session 1 SQL> create table parent(id number primary key); Table created. SQL> create table child(id number references parent,name varchar2(20)); Table created. SQL> insert into parent values(1); 1 row created. SQL>--not commit; --session 2: SQL> insert into child values(1,'a'); --hang --session 3： SQL> l 1 select sid, 2 chr(bitand(p1, -16777216) / 16777215) || 3 chr(bitand(p1, 16711680) / 65535) "Name", 4 (bitand(p1, 65535)) "Mode", 5 event, 6 sql_id, 7 FINAL_BLOCKING_SESSION 8 from v$session 9* where event like 'enq%' SQL> / SID Name Mode EVENT SQL_ID FINAL_BLOCKING_SESSION ---------- ---------------- ---------- ------------------------------ -------------------------- ---------------------- 5 TX 4 enq: TX - row lock contention d8gc19unfrcsw 63 SQL> |
3.插入主键同一值
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
--session 1: SQL> create table mytab(id number primary key); Table created. SQL> insert into mytab values(1); 1 row created. SQL> --session 2: SQL> insert into mytab values(1); --hang --session 3: SQL> l 1 select sid, 2 chr(bitand(p1, -16777216) / 16777215) || 3 chr(bitand(p1, 16711680) / 65535) "Name", 4 (bitand(p1, 65535)) "Mode", 5 event, 6 sql_id, 7 FINAL_BLOCKING_SESSION 8 from v$session 9* where event like 'enq%' SQL> / SID Name Mode EVENT SQL_ID FINAL_BLOCKING_SESSION ---------- ---------------- ---------- ------------------------------ -------------------------- ---------------------- 5 TX 4 enq: TX - row lock contention 2srnv7c1ummk0 63 SQL> |
4.ITL争用，其实这个可以明显看出来，因为在10g后，就已经有enq:TX – allocate ITL entry，但是我在这里还是把它分类在mode=4的TX锁。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
注：9.2.0.8环境，因为10g以上环境，设置maxtrans无效，自动变成255 --session 1 SQL> create table test(x number) initrans 2 maxtrans 2; Table created. SQL> insert into test select rownum from dual connect by level<=3; 3 rows created. SQL> commit; Commit complete. SQL> select * from test; X ---------- 1 2 3 SQL> SQL> update test set x=99 where x=1; 1 row updated. SQL> --session 2 SQL> update test set x=99 where x=2; 1 row updated. SQL> --session 3: SQL> update test set x=99 where x=3; --hang |
5.索引分裂，会伴随着enq: TX – index contention的等待。