索引分裂的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 enq: TX - allocate ITL entry 4 0 0.01

144 enq: TX - index contention 2 21 10.49

6 rows selected

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

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1058113/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1058113/