索引空间重用实验
来源：互联网 发布：分布式的文档数据库是 编辑：程序博客网 时间：2024/05/22 14:19
随机删除：
SQL> create table TEST as select rownum id, object_name from dba_objects where rownum < 10;
Table created
SQL> create index idx_id_test on test(id);
Index created
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select * from index_stats;
HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
1 8 IDX_ID_TEST 9 1 126 7996 0 0 0 0 0 0 9 1 7996 126 2 1 2 0 0 0 0
SQL> commit;
Commit complete
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 0
SQL> DELETE FROM TEST WHERE ID IN (2,4,6,8);
4 rows deleted
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 4
SQL> col object_name format a30;
SQL> select object_name, object_id from dba_objects where object_name in ('IDX_ID_TEST');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
IDX_ID_TEST 75626
SQL> alter session set events 'immediate trace name treedump level 75626';
Session altered
SQL>
SQL> SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
2 P.SPID || '.trc' TRACE_FILE_NAME
3 FROM (SELECT P.SPID
4 FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
5 WHERE M.STATISTIC# = 1
6 AND S.SID = M.SID
7 AND P.ADDR = S.PADDR) P,
8 (SELECT T.INSTANCE
9 FROM V$THREAD T, V$PARAMETER V
10 WHERE V.NAME = 'thread'
11 AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,
12 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') T
13 ;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5544.trc
-----------------------------------------------------
vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5544.trc
*** 2013-07-10 17:36:02.241
----- begin tree dump
leaf: 0x41a289 4301449 (0: nrow: 9 rrow: 5)
----- end tree dump
--------------------------------------------------------------
SQL> select dbms_utility.data_block_address_file(4301449),dbms_utility.data_block_address_block(4301449) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
1 107145
SQL> alter system dump datafile 1 block 107145;
System altered
-----------------------------------------------------------------------------
vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5544.trc
----------------------------------------------------------------------------
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 a2 81 00 00
row#1[8008] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 41 a2 81 00 01
row#2[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 41 a2 81 00 02
row#3[7984] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 00 41 a2 81 00 03
row#4[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 00 41 a2 81 00 04
row#5[7960] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 00 41 a2 81 00 05
row#6[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 00 41 a2 81 00 06
row#7[7936] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 00 41 a2 81 00 07
row#8[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 00 41 a2 81 00 08
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 107145 maxblk 107145
SQL> insert into test values (10,'jerry');
1 row inserted
SQL> commit;
Commit complete
SQL> alter system checkpoint;
System altered
SQL> alter system dump datafile 1 block 107145;
System altered
-----------------------------------------------------------------------------
vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5544.trc
----------------------------------------------------------------------------
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 a2 81 00 00
row#1[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 41 a2 81 00 02
row#2[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 00 41 a2 81 00 04
row#3[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 00 41 a2 81 00 06
row#4[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 00 41 a2 81 00 08
row#5[7912] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 00 41 a2 81 00 09
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 107145 maxblk 107145
row#5是我们新插入的数据。空间被重用了。通过下面的SQL也可以验证。
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
6 0
总结：
1.任何一个新事务，往叶块中插入一条记录，ORACLE会自去移除/释放这个索引块内所有的已被删除的ENTRY;
2.在一个随机插入的索引块上，被删除的空间的重用往往不会存在什么问题，就像我们看到的一样，只有一有新插入，空间就会释放。
场景二、
一个表的字段是自增的，如果我们按照ID的某个范围，把记录删除，这些记录占用的索引空间能否释放。
SQL> drop table test;
Table dropped
SQL> create table TEST as select rownum id, object_name from dba_objects where rownum < 10000;
Table created
SQL> create index idx_id_test on test(id);
Index created
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
9999 0
SQL> delete from test;
9999 rows deleted
SQL> commit;
Commit complete
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_blks,lf_rows, del_lf_rows from index_stats;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
SQL> insert into test select rownum + 20000,'jerry' from dba_objects where rownum <10000;
9999 rows inserted
SQL> commit;
Commit complete
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_blks,lf_rows, del_lf_rows from index_stats;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
场景二总结：
索引的叶块被清空后，会把这个叶块做为空闲块加入到索引段的BITMAP里面（如果使用的是ASSM），这样后面的事务就可以重新使用这些空闲的索引块了。需要指出的是，这些被清空的索引块虽然被加入到了索引的空间列表里面，但是他们依然在索引的逻辑结构中，被分支节点所参照（LINK），直到被重新使用，才会被重新RELINK.
场景三、
延迟块清除会在叶块做CLEANOUT，释放索引的空间
SQL> drop table test;
Table dropped
SQL> create table TEST as select rownum id, object_name from dba_objects where rownum < 10000;\
Table created
SQL> create index idx_id_test on test(id);
Index created
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
9999 0
SQL> select lf_blks,lf_rows, del_lf_rows from index_stats;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
SQL> delete from test;
9999 rows deleted
不要提交，另开一个SESSION，执行SQL> alter system flush buffer_cache;回到原SESSION
SQL> commit;
Commit complete
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 75632';
Session altered
SQL>
SQL> SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
2 P.SPID || '.trc' TRACE_FILE_NAME
3 FROM (SELECT P.SPID
4 FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
5 WHERE M.STATISTIC# = 1
6 AND S.SID = M.SID
7 AND P.ADDR = S.PADDR) P,
8 (SELECT T.INSTANCE
9 FROM V$THREAD T, V$PARAMETER V
10 WHERE V.NAME = 'thread'
11 AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,
12 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') T
13 ;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10752.trc
vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10752.trc
----- begin tree dump
*** 2013-07-10 18:09:55.297
branch: 0x41a2a9 4301481 (0: nrow: 21, level: 1)
leaf: 0x41a2aa 4301482 (-1: nrow: 485 rrow: 0)
leaf: 0x41a2ab 4301483 (0: nrow: 479 rrow: 0)
leaf: 0x41a2ac 4301484 (1: nrow: 479 rrow: 0)
leaf: 0x41a2ad 4301485 (2: nrow: 479 rrow: 0)
leaf: 0x41a2ae 4301486 (3: nrow: 479 rrow: 0)
leaf: 0x41a2af 4301487 (4: nrow: 478 rrow: 0)
leaf: 0x41a2b0 4301488 (5: nrow: 479 rrow: 0)
leaf: 0x41a2b1 4301489 (6: nrow: 479 rrow: 0)
leaf: 0x41a2b2 4301490 (7: nrow: 479 rrow: 0)
leaf: 0x41a2b3 4301491 (8: nrow: 478 rrow: 0)
leaf: 0x41a2b4 4301492 (9: nrow: 479 rrow: 0)
leaf: 0x41a2b5 4301493 (10: nrow: 479 rrow: 0)
leaf: 0x41a2b6 4301494 (11: nrow: 479 rrow: 0)
leaf: 0x41a2b7 4301495 (12: nrow: 479 rrow: 0)
leaf: 0x41a2b8 4301496 (13: nrow: 478 rrow: 0)
leaf: 0x41a2b9 4301497 (14: nrow: 479 rrow: 0)
leaf: 0x41a2ba 4301498 (15: nrow: 479 rrow: 0)
leaf: 0x41a2bb 4301499 (16: nrow: 479 rrow: 0)
leaf: 0x41a2bc 4301500 (17: nrow: 479 rrow: 0)
leaf: 0x41a2bd 4301501 (18: nrow: 478 rrow: 0)
leaf: 0x41a2be 4301502 (19: nrow: 417 rrow: 0)
----- end tree dump
SQL> select dbms_utility.data_block_address_file(4301502),dbms_utility.data_block_address_block(4301502) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
1 107198
SQL> alter system dump datafile 1 block 107198;
System altered
vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10752.trc
Block header dump: 0x0041a2be
Object id on Block? Y
seg/obj: 0x12770 csc: 0x00.17ef89 itc: 2 flg: O typ: 2 - INDEX
fsl: 0 fnx: 0x41a2bd ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x000a.00a.00000329 0x00c00ccd.01c4.26 C-U- 0 scn 0x0000.0017eb81
Leaf block dump
===============
header address 9998940=0x98925c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 0
kdxcofbo 36=0x24
kdxcofeo 8032=0x1f60
kdxcoavs 7996
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 4301501=0x41a2bd
kdxledsz 0
kdxlebksz 8032
*** dummy key ***
row#0[8019] flag: ---D--, lock: 0, len=13
col 0; len 3; (3): c2 60 54
col 1; len 6; (6): 00 41 a2 a5 00 12
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 107198 maxblk 107198
场景三总结：
1.一些大的事务由于脏块较多，可以导致事务提交的时候，一些脏块已经被写入到磁盘了。
2.当没有清除的块再次被访问时，会发生延迟块清除。
3.延迟块清除会把这个块上所有的被删除的索引ENTRY做CLEANOUT.
在实验中，我们用到了ANALYZE XXX VALIDATE STRUCTURE， 在生产环境使用一定要小心，会在表上加一个类型为4的TM锁，阻塞表上的一切DML操作。如果用ONLINE做的话，在INDEX_STATS里面看不到记录。
上面符合的场景是，做的删除是随机的，所以很多索引叶块都存在被删除的ENTRY，如果这些块没有完全被清空，则不属于FREE块。
你后续插入的ID，如果都比现在的表的ID要大，则不会插入到之前的那些叶子节点里。
这个索引会一直向右延伸，从些变得不均衡，存在了很多的空洞，因此索引空间就是浪费的，这种情况要重建索引。
如果这些ID不是自增的，而是随机生成的，则这些空间早晚会被重用。
SQL> create table TEST as select rownum id, object_name from dba_objects where rownum < 10;
Table created
SQL> create index idx_id_test on test(id);
Index created
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select * from index_stats;
HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
1 8 IDX_ID_TEST 9 1 126 7996 0 0 0 0 0 0 9 1 7996 126 2 1 2 0 0 0 0
SQL> commit;
Commit complete
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 0
SQL> DELETE FROM TEST WHERE ID IN (2,4,6,8);
4 rows deleted
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 4
SQL> col object_name format a30;
SQL> select object_name, object_id from dba_objects where object_name in ('IDX_ID_TEST');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
IDX_ID_TEST 75626
SQL> alter session set events 'immediate trace name treedump level 75626';
Session altered
SQL>
SQL> SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
2 P.SPID || '.trc' TRACE_FILE_NAME
3 FROM (SELECT P.SPID
4 FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
5 WHERE M.STATISTIC# = 1
6 AND S.SID = M.SID
7 AND P.ADDR = S.PADDR) P,
8 (SELECT T.INSTANCE
9 FROM V$THREAD T, V$PARAMETER V
10 WHERE V.NAME = 'thread'
11 AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,
12 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') T
13 ;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5544.trc
-----------------------------------------------------
vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5544.trc
*** 2013-07-10 17:36:02.241
----- begin tree dump
leaf: 0x41a289 4301449 (0: nrow: 9 rrow: 5)
----- end tree dump
--------------------------------------------------------------
SQL> select dbms_utility.data_block_address_file(4301449),dbms_utility.data_block_address_block(4301449) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
1 107145
SQL> alter system dump datafile 1 block 107145;
System altered
-----------------------------------------------------------------------------
vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5544.trc
----------------------------------------------------------------------------
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 a2 81 00 00
row#1[8008] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 41 a2 81 00 01
row#2[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 41 a2 81 00 02
row#3[7984] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 00 41 a2 81 00 03
row#4[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 00 41 a2 81 00 04
row#5[7960] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 00 41 a2 81 00 05
row#6[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 00 41 a2 81 00 06
row#7[7936] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 00 41 a2 81 00 07
row#8[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 00 41 a2 81 00 08
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 107145 maxblk 107145
SQL> insert into test values (10,'jerry');
1 row inserted
SQL> commit;
Commit complete
SQL> alter system checkpoint;
System altered
SQL> alter system dump datafile 1 block 107145;
System altered
-----------------------------------------------------------------------------
vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5544.trc
----------------------------------------------------------------------------
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 a2 81 00 00
row#1[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 41 a2 81 00 02
row#2[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 00 41 a2 81 00 04
row#3[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 00 41 a2 81 00 06
row#4[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 00 41 a2 81 00 08
row#5[7912] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 00 41 a2 81 00 09
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 107145 maxblk 107145
row#5是我们新插入的数据。空间被重用了。通过下面的SQL也可以验证。
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
6 0
总结：
1.任何一个新事务，往叶块中插入一条记录，ORACLE会自去移除/释放这个索引块内所有的已被删除的ENTRY;
2.在一个随机插入的索引块上，被删除的空间的重用往往不会存在什么问题，就像我们看到的一样，只有一有新插入，空间就会释放。
场景二、
一个表的字段是自增的，如果我们按照ID的某个范围，把记录删除，这些记录占用的索引空间能否释放。
SQL> drop table test;
Table dropped
SQL> create table TEST as select rownum id, object_name from dba_objects where rownum < 10000;
Table created
SQL> create index idx_id_test on test(id);
Index created
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
9999 0
SQL> delete from test;
9999 rows deleted
SQL> commit;
Commit complete
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_blks,lf_rows, del_lf_rows from index_stats;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
SQL> insert into test select rownum + 20000,'jerry' from dba_objects where rownum <10000;
9999 rows inserted
SQL> commit;
Commit complete
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_blks,lf_rows, del_lf_rows from index_stats;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
场景二总结：
索引的叶块被清空后，会把这个叶块做为空闲块加入到索引段的BITMAP里面（如果使用的是ASSM），这样后面的事务就可以重新使用这些空闲的索引块了。需要指出的是，这些被清空的索引块虽然被加入到了索引的空间列表里面，但是他们依然在索引的逻辑结构中，被分支节点所参照（LINK），直到被重新使用，才会被重新RELINK.
场景三、
延迟块清除会在叶块做CLEANOUT，释放索引的空间
SQL> drop table test;
Table dropped
SQL> create table TEST as select rownum id, object_name from dba_objects where rownum < 10000;\
Table created
SQL> create index idx_id_test on test(id);
Index created
SQL> analyze index idx_id_test validate structure;
Index analyzed
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
9999 0
SQL> select lf_blks,lf_rows, del_lf_rows from index_stats;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
SQL> delete from test;
9999 rows deleted
不要提交，另开一个SESSION，执行SQL> alter system flush buffer_cache;回到原SESSION
SQL> commit;
Commit complete
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 75632';
Session altered
SQL>
SQL> SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
2 P.SPID || '.trc' TRACE_FILE_NAME
3 FROM (SELECT P.SPID
4 FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
5 WHERE M.STATISTIC# = 1
6 AND S.SID = M.SID
7 AND P.ADDR = S.PADDR) P,
8 (SELECT T.INSTANCE
9 FROM V$THREAD T, V$PARAMETER V
10 WHERE V.NAME = 'thread'
11 AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,
12 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') T
13 ;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10752.trc
vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10752.trc
----- begin tree dump
*** 2013-07-10 18:09:55.297
branch: 0x41a2a9 4301481 (0: nrow: 21, level: 1)
leaf: 0x41a2aa 4301482 (-1: nrow: 485 rrow: 0)
leaf: 0x41a2ab 4301483 (0: nrow: 479 rrow: 0)
leaf: 0x41a2ac 4301484 (1: nrow: 479 rrow: 0)
leaf: 0x41a2ad 4301485 (2: nrow: 479 rrow: 0)
leaf: 0x41a2ae 4301486 (3: nrow: 479 rrow: 0)
leaf: 0x41a2af 4301487 (4: nrow: 478 rrow: 0)
leaf: 0x41a2b0 4301488 (5: nrow: 479 rrow: 0)
leaf: 0x41a2b1 4301489 (6: nrow: 479 rrow: 0)
leaf: 0x41a2b2 4301490 (7: nrow: 479 rrow: 0)
leaf: 0x41a2b3 4301491 (8: nrow: 478 rrow: 0)
leaf: 0x41a2b4 4301492 (9: nrow: 479 rrow: 0)
leaf: 0x41a2b5 4301493 (10: nrow: 479 rrow: 0)
leaf: 0x41a2b6 4301494 (11: nrow: 479 rrow: 0)
leaf: 0x41a2b7 4301495 (12: nrow: 479 rrow: 0)
leaf: 0x41a2b8 4301496 (13: nrow: 478 rrow: 0)
leaf: 0x41a2b9 4301497 (14: nrow: 479 rrow: 0)
leaf: 0x41a2ba 4301498 (15: nrow: 479 rrow: 0)
leaf: 0x41a2bb 4301499 (16: nrow: 479 rrow: 0)
leaf: 0x41a2bc 4301500 (17: nrow: 479 rrow: 0)
leaf: 0x41a2bd 4301501 (18: nrow: 478 rrow: 0)
leaf: 0x41a2be 4301502 (19: nrow: 417 rrow: 0)
----- end tree dump
SQL> select dbms_utility.data_block_address_file(4301502),dbms_utility.data_block_address_block(4301502) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
1 107198
SQL> alter system dump datafile 1 block 107198;
System altered
vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10752.trc
Block header dump: 0x0041a2be
Object id on Block? Y
seg/obj: 0x12770 csc: 0x00.17ef89 itc: 2 flg: O typ: 2 - INDEX
fsl: 0 fnx: 0x41a2bd ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x000a.00a.00000329 0x00c00ccd.01c4.26 C-U- 0 scn 0x0000.0017eb81
Leaf block dump
===============
header address 9998940=0x98925c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 0
kdxcofbo 36=0x24
kdxcofeo 8032=0x1f60
kdxcoavs 7996
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 4301501=0x41a2bd
kdxledsz 0
kdxlebksz 8032
*** dummy key ***
row#0[8019] flag: ---D--, lock: 0, len=13
col 0; len 3; (3): c2 60 54
col 1; len 6; (6): 00 41 a2 a5 00 12
----- end of leaf block dump -----
End dump data blocks tsn: 0 file#: 1 minblk 107198 maxblk 107198
场景三总结：
1.一些大的事务由于脏块较多，可以导致事务提交的时候，一些脏块已经被写入到磁盘了。
2.当没有清除的块再次被访问时，会发生延迟块清除。
3.延迟块清除会把这个块上所有的被删除的索引ENTRY做CLEANOUT.
在实验中，我们用到了ANALYZE XXX VALIDATE STRUCTURE， 在生产环境使用一定要小心，会在表上加一个类型为4的TM锁，阻塞表上的一切DML操作。如果用ONLINE做的话，在INDEX_STATS里面看不到记录。
上面符合的场景是，做的删除是随机的，所以很多索引叶块都存在被删除的ENTRY，如果这些块没有完全被清空，则不属于FREE块。
你后续插入的ID，如果都比现在的表的ID要大，则不会插入到之前的那些叶子节点里。
这个索引会一直向右延伸，从些变得不均衡，存在了很多的空洞，因此索引空间就是浪费的，这种情况要重建索引。
如果这些ID不是自增的，而是随机生成的，则这些空间早晚会被重用。
- 索引空间重用实验
- 索引空间重用实验
- 关于索引块空间的重用
- [一篇好文章]关于索引重用空间的总结测试
- Oracle索引碎片分析、空间重用和整理
- 空间索引
- 空间索引
- 空间索引
- 空间索引
- 空间索引
- ORACEL空间管理实验9：空间管理汇总--索引和表的扫描/闪回时的块管理方式
- Tensorflow 变量命名空间及变量重用
- Tensorflow 变量命名空间及变量重用
- 深入浅出空间索引：为什么需要空间索引
- 深入浅出空间索引：为什么需要空间索引
- 空间数据库之空间索引
- Dynamips实验索引
- 整理索引的实验
- 假期学习目标
- xml基础整理
- 数位DP——UESTC1307 windy数
- mingui
- ARM (1)
- 索引空间重用实验
- VS2012 下编译boost1.52
- qt-designer使用教程--编写自己的槽
- 程序员的八个级别
- Apache详细配置
- Java测试工具
- 枚举类总结
- HDU dp 2059 龟兔赛跑
- Qt单元测试框架
