模拟等待事件row lock waits

      是索引块分裂引起的锁等待，往往与enq: TX - index contention 伴随产生，enq:TX - index contention的解释。

      Waits for TX in mode 4 also occur when a transaction inserting a row in an index has to wait for the end of an index block split being done by another transaction. This type of TX enqueue wait corresponds to the wait event enq: TX - index contention。

session 1:

SQL> drop table test purge;
表已删除。
SQL>  create table test(x number,y char(20),z date,q varchar2(4000));
表已创建。
SQL> create index t_idx on test(q,z);
索引已创建。
SQL> begin
         for x in 1..1500 loop
             insert into test values(1162,'1060000abcdefg', sysdate, rpad('x',2000,'x'));
         end loop;
         end;
     /
PL/SQL 过程已成功完成。


session 2:
SQL> begin
         for x in 1..1500 loop
         insert into test values(1162,'1060000abcdefg', sysdate, rpad('x',2000,'x'));
    end loop;
    end;
    /
PL/SQL 过程已成功完成。


session 3:
session 1与session 2开始插入前：
SQL> select object_name, STATISTIC_NAME, value
      from v$segment_statistics
     where object_name in ('TEST', 'T_IDX')
       and STATISTIC_NAME = 'row lock waits'
     order by 1;
OBJECT_NAME          STATISTIC_NAME            VALUE
-------------------- -------------------- ----------
TEST                 row lock waits                0
T_IDX                row lock waits                0

SQL> /
OBJECT_NAME          STATISTIC_NAME            VALUE
-------------------- -------------------- ----------
TEST                 row lock waits                0
T_IDX                row lock waits               74