Oracle索引碎片分析、空间重用和整理

浏览:
字体:
发布时间:2013-12-20 16:18:42
来源:

对索引频繁的update,delete操作会产生index Frag,影响索引效率,增加索引IO。

1、索引碎片分析
产生测试索引碎片:
SCOTT @devcedb>select count(*) from obj;

COUNT(*)
----------
124256

SCOTT @devcedb>create index ind_obj_id on obj(OBJECT_ID);

Index created.

SCOTT @devcedb>delete obj where rownum<50000;

49999 rows deleted.

SCOTT @devcedb>commit;

Commit complete.

索引碎片分析:
SCOTT @devcedb>analyze index ind_obj_id validate structure;

Index analyzed.

--注意一点,就是该命令有一个坏处,就是在运行过程中,会锁定整个表,从而阻塞其他session对表进行插入、更新和删除等操作。这是因为该命令的主要目的并不是用来填充index_stats视图的,其主要作用在于校验索引中的每个有效的索引条目都对应到表里的一行,同时表里的每一行数据在索引中都存在一个对应的索引条目。为了完成该目的,所以在运行过程中要锁定整个表,同时对于很大的表来说,运行该命令需要耗费非常多的时间。

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
IND_OBJ_ID 384 766085 1906952 40.1732713 40.2394062

索引碎片比率:(del_lf_rows_len/lf_rows_len)*100,如果百分比超过20%就说明索引碎片比率很高了。需要整理碎片。
索引和表数据是级联关系的,当删除表数据的时候,索引条目不会被自动删除,而是在该条目上打上一个删除(D)的标识位,具体后面会说明,索引的block数量是不会改变的,空叶块不会被删除。所以当INDEX FAST FULL SCAN和INDEX FULL SCAN的时候,这些空索引块也会被加载到内存中,增加了IO。索引空叶块多,也影响了索引扫描的效率。如下:

SCOTT @devcedb>select object_id from obj where object_id < 9000;

41377 rows selected.

Elapsed: 00:00:01.13

Execution Plan
----------------------------------------------------------
Plan hash value: 2777403740

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55341 | 702K| 79 (2)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IND_OBJ_ID | 55341 | 702K| 79 (2)| 00:00:01 |
-----------------------------------------------------------------------------------

SYS AS SYSDBA@devcedb>select count(*) from x$bh where obj='102822';

COUNT(*)
---------- ---从x$bh查询缓存blocks,要用DBA_OBJECTS.DATA_OBJECT_ID
268 ---该索引加载到buffer pool的数据块数,该索引分配有384 blocks,266 LEAF_BLOCKS

那么索引空块会不会被重用呢?下面测试说明:
SCOTT @devcedb>select count(*) from obj2;

COUNT(*)
----------
62144

SCOTT @devcedb>create unique index uni_ind_obj2_id on obj2(id);

Index created. --该索引分配有256 blocks,130 LEAF_BLOCKS

SCOTT @devcedb>delete obj2 where rownum<15537;

15536 rows deleted.

SCOTT @devcedb>commit;

Commit complete.

SCOTT @devcedb>analyze index uni_ind_obj2_id validate structure;

Index analyzed.

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
UNI_IND_OBJ2_ID 256 240063 938727 25.5732497 25.5732626

SCOTT @devcedb>insert into obj2 select seq_obj2.nextval id,a.* from dba_objects a;

15537 rows created.

SCOTT @devcedb>commit;

Commit complete.

SCOTT @devcedb>analyze index uni_ind_obj2_id validate structure;

Index analyzed.

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
UNI_IND_OBJ2_ID 256 7180 938727 .764865611 .764882473

SYS AS SYSDBA@devcedb>select INDEX_NAME,LEAF_BLOCKS,BLEVEL from dba_indexes where INDEX_NAME=upper('uni_ind_obj2_id');

INDEX_NAME LEAF_BLOCKS BLEVEL
------------------------------ ----------- ----------
UNI_IND_OBJ2_ID 130 1

这里我们看到,索引的碎片降低了,而且LEAF_BLOCKS的数量没有增加,说明空叶块被重用了。

当删除表里的一条记录时,其对应于索引里的索引条目并不会被物理的删除,只是做了一个删除标记(这可以通过dump 索引数据块alter system dump datafile # block #;可以看到类似”row#0[443] flag: ---D-, lock: 2“。)当一个空叶块被重用的时候,当第一条数据插入该索引叶块之前,oracle清空该空叶块上所有打上D标识位的索引条目,然后重用该索引块。如下:
SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100,USED_SPACE,BTREE_SPACE,PCT_USED from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100 USED_SPACE BTREE_SPACE PCT_USED
------------------------------ ---------- --------------- ----------- --------------------------------- ------------------------- ---------- ----------- ----------
IND_OBJ_ID 256 307878 835601 36.8450971 36.8625788 837792 1279392 66

SCOTT @devcedb>insert into obj select a.* from dba_objects a where rownum<5; --该索引存在大量空索引块,我们插入4条记录

4 rows created.

SCOTT @devcedb>commit;

Commit complete.

SCOTT @devcedb>analyze index ind_obj_id validate structure;

Index analyzed.

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100,USED_SPACE,BTREE_SPACE,PCT_USED from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100 USED_SPACE BTREE_SPACE PCT_USED
------------------------------ ---------- --------------- ----------- --------------------------------- ------------------------- ---------- ----------- ----------
IND_OBJ_ID 256 306312 834091 36.7240505 36.7303962 836282 1279392 66

我们关注下USED_SPACE和BTREE_SPACE
USED_SPACE--Total space that is currently being used in the B-Tree
BTREE_SPACE --Total space currently allocated in the B-Tree
重新分析该索引后我们注意到USED_SPACE反而降低了,BTREE_SPACE不变(当然我们也可以看到LF_ROWS减少了),在这4条数据重用一个空索引块后,释放的空间大于使用的空间,该空叶块被重用了。

半空叶块是如何重用的呢?
我们构想一下,一个有两个叶块的index,第一个叶块包含1到10(不包含6),第二个叶块11到20,这时候我们删除表中2,4,11的数据,分析下索引后,分三种情况:1)插入键之前删除的键值,插入2,索引叶块1标识为D的两条索引记录会被清空,重新插入键值为2的记录,索引空间被重用,BTREE_SPACE不变,USED_SPACE降低,LF_ROWS减1。2)插入一个属于原来被删除键值范围内的值,插入6,我们会发现和情况1相同。3)插入比之前键值更大的值,假定叶块2空间满了,会新增一个叶块。

所以说经常被删除或更新index键值,以后几乎不再会被插入时,空间的重用率很低,碎片产生的就越快。

2、索引碎片整理
由于index_stats只能存储最近一行数据,analyze index后查询index_stats必须在同一session内,否则查询index_stats无记录,我们回到之前碎片很严重的索引ind_obj_id需要重新analyze一下:
SCOTT @devcedb>analyze index ind_obj_id validate structure;

Index analyzed.

Elapsed: 00:00:00.13
SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;


NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
IND_OBJ_ID 384 766085 1906952 40.1732713 40.2394062

碎片整理的方式
1)重建索引
alter index INDEXNAME rebuild;
alter index INDEXNAME rebuild online;

index rebuild可以使用nologging减少redo的生成,parallel并行创建,compute statistics在创建的同时收集CBO的统计信息。例:
SCOTT @devcedb>alter index IND_OBJ_ID rebuild online nologging parallel 4 compute statistics;

Index altered.

SCOTT @devcedb>select INDEX_NAME,LOGGING,DEGREE from user_indexes where INDEX_NAME='IND_OBJ_ID';

INDEX_NAME LOG DEGREE
------------------------------ --- ----------------------------------------
IND_OBJ_ID NO 4

SCOTT @devcedb>alter index IND_OBJ_ID logging parallel 1;

Index altered.

2)coallesce索引
alter index INDEXNAME coalesce;

SCOTT @devcedb>alter index ind_obj_id rebuild;

Index altered.

SCOTT @devcedb>analyze index ind_obj_id validate structure;

Index analyzed.

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
IND_OBJ_ID 256 0 1140867 0 0

附:

INDEX_STATS 

Column

Datatype

NULL

Description

HEIGHT

NUMBER

 

Height of the B-Tree

BLOCKS

NUMBER

NOT NULL

Blocks allocated to the segment

NAME

VARCHAR2(30)

NOT NULL

Name of the index

PARTITION_NAME

VARCHAR2(30)

 

Name of the partition of the index which was analyzed. If the index is not partitioned, NULL is returned.

LF_ROWS

NUMBER

 

Number of leaf rows (values in the index) --包含已打上delete标识的行数

LF_BLKS

NUMBER

 

Number of leaf blocks in the B-Tree --包含空叶块

LF_ROWS_LEN

NUMBER

 

Sum of the lengths of all the leaf rows

LF_BLK_LEN

NUMBER

 

Usable space in a leaf block

BR_ROWS

NUMBER

 

Number of branch rows in the B-Tree

BR_BLKS

NUMBER

 

Number of branch blocks in the B-Tree

BR_ROWS_LEN

NUMBER

 

Sum of the lengths of all the branch blocks in the B-Tree

BR_BLK_LEN

NUMBER

 

Usable space in a branch block

DEL_LF_ROWS

NUMBER

 

Number of deleted leaf rows in the index

DEL_LF_ROWS_LEN

NUMBER

 

Total length of all deleted rows in the index

DISTINCT_KEYS

NUMBER

 

Number of distinct keys in the index (may include rows that have been deleted)

MOST_REPEATED_KEY

NUMBER

 

How many times the most repeated key is repeated (may include rows that have been deleted)

BTREE_SPACE

NUMBER

 

Total space currently allocated in the B-Tree

USED_SPACE

NUMBER

 

Total space that is currently being used in the B-Tree

PCT_USED

NUMBER

 

Percent of space allocated in the B-Tree that is being used --(USED_SPACE/BTREE_SPACE)*100

ROWS_PER_KEY

NUMBER

 

Average number of rows per distinct key (this figure is calculated without consideration of deleted rows)

BLKS_GETS_PER_ACCESS

NUMBER

 

Expected number of consistent mode block reads per row, assuming that a randomly chosen row is accessed using the index. Used to calculate the number of consistent reads that will occur during an index scan.

PRE_ROWS

NUMBER

 

Number of prefix rows (values in the index)

PRE_ROWS_LEN

NUMBER

 

Sum of lengths of all prefix rows

OPT_CMPR_COUNT

NUMBER

 

Optimal key compression length

OPT_CMPR_PCTSAVE

NUMBER

 

Corresponding space savings after an ANALYZE


>更多相关文章
24小时热门资讯
24小时回复排行
资讯 | QQ | 安全 | 编程 | 数据库 | 系统 | 网络 | 考试 | 站长 | 关于东联 | 安全雇佣 | 搞笑视频大全 | 微信学院 | 视频课程 |
关于我们 | 联系我们 | 广告服务 | 免责申明 | 作品发布 | 网站地图 | 官方微博 | 技术培训
Copyright © 2007 - 2024 Vm888.Com. All Rights Reserved
粤公网安备 44060402001498号 粤ICP备19097316号 请遵循相关法律法规
');})();