索引分裂的enq

索引分裂引起的enq-index contention和enq: TX - allocate ITL entry，該等待事件是由於索引分裂下，併發的使用者在此更新索引導致。

SQL> create table t1(id number,name varchar2(1000));

Table created

SQL> create index index_t1 on t1(id,name) pctfree 0;

Index created

SQL> begin

2 for i in 1..1000 loop

3 insert into t1 values(i,rpad('x',1000,'x'));

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed

SQL> analyze index index_t1 validate structure;

Index analyzed

SQL> select name,height,blocks,lf_rows,lf_blks,pct_used from index_stats;

NAME HEIGHT BLOCKS LF_ROWS LF_BLKS PCT_USED

------------------------------ ---------- ---------- ---------- ---------- ----------

INDEX_T1 2 256 1000 143 89

SQL>

SQL> select a.value,b.name from v$mystat a,v$statname b

2 where a.statistic#=b.statistic# and b.name like 'leaf node%'

3 ;

VALUE NAME

---------- ----------------------------------------------------------------

156 leaf node splits

156 leaf node 90-10 splits

SQL> insert into t1 values(1,rpad('x',1000,'x'));

1 row inserted

SQL> commit;

SQL> analyze index index_t1 validate structure;

Index analyzed

SQL> select name,height,blocks,lf_rows,lf_blks,pct_used from index_stats;

NAME HEIGHT BLOCKS LF_ROWS LF_BLKS PCT_USED

------------------------------ ---------- ---------- ---------- ---------- ----------

INDEX_T1 2 256 1001 144 88

SQL>

SQL> select a.value,b.name from v$mystat a,v$statname b

2 where a.statistic#=b.statistic# and b.name like 'leaf node%'

3 ;

VALUE NAME

---------- ----------------------------------------------------------------

157 leaf node splits

156 leaf node 90-10 splits

發生索引葉塊的5-5分裂，葉塊由143變為了144.

SQL> insert into t1 values(0.5,rpad('x',1000,'x'));

1 row inserted

SQL> analyze index index_t1 validate structure;

Index analyzed

SQL> select name,height,blocks,lf_rows,lf_blks,pct_used from index_stats;

NAME HEIGHT BLOCKS LF_ROWS LF_BLKS PCT_USED

------------------------------ ---------- ---------- ---------- ---------- ----------

INDEX_T1 2 256 1002 144 88

SQL> insert into t1 values(1001,rpad('x',1000,'x'));

1 row inserted

SQL> commit;

Commit complete

SQL> analyze index index_t1 validate structure;

Index analyzed

SQL> select name,height,blocks,lf_rows,lf_blks,pct_used from index_stats;

NAME HEIGHT BLOCKS LF_ROWS LF_BLKS PCT_USED

------------------------------ ---------- ---------- ---------- ---------- ----------

INDEX_T1 2 256 1003 144 89

此時沒有發生葉塊分裂，因為索引順序排序下，葉塊的最後的一個葉塊節點並並沒有寫滿，所以1001這個鍵值會儲存在最後的一個葉塊上。

SQL> begin

2 for i in 1002..1200 loop

3 insert into t1 values(i,rpad('x',1000,'x'));

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed

SQL>

SQL> select a.value,b.name from v$mystat a,v$statname b

2 where a.statistic#=b.statistic# and b.name like 'leaf node%'

3 ;

VALUE NAME

---------- ----------------------------------------------------------------

186 leaf node splits

185 leaf node 90-10 splits

對比一下上述的統計資訊，重新插入數值由於在維護索引，分別發生了5-5分裂和1-9分裂

SQL> analyze index index_t1 validate structure;

Index analyzed

SQL> select name,height,blocks,lf_rows,lf_blks,pct_used from index_stats;

NAME HEIGHT BLOCKS LF_ROWS LF_BLKS PCT_USED

------------------------------ ---------- ---------- ---------- ---------- ----------

INDEX_T1 2 256 1202 173 88

模擬下關於索引等待的兩個事件

併發的兩個session執行下列pl/sql

SQL> declare

2 begin

3 for i in 501..1000 loop

4 insert into t1 values(i,rpad('x',1000,'x'));

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed

SQL> select userenv('SID') from dual;

USERENV('SID')

--------------

144

SQL> select sid,event,total_waits,time_waited,average_wait from v$session_event where event like 'enq%';

SID EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT

---------- ---------------------------------------------------------------- ----------- ----------- ------------

138 enq: HW - contention 428 664 1.55

138 enq: TX - index contention 7 2 0.28

144 enq: RO - fast object reuse 4 54 13.62

144 enq: HW - contention 592 1448 2.45

144 4 0 0.01

1442 21 10.49

6 rows selected

等待出現了，enq: TX - allocate ITL entry和enq: TX - index contention

[@more@]