1 概要
碎片简单理解就是在大量使用DML操作数据库时,其产生一些不能被再次使用的碎小空间,根据每种不同的碎片他们的产生也是有区别的,主要包涵一下几个层次。
|--disk-level fragmention
|----tablespace-level fragmentation
|------segment-level fragmentation
|--------block-level fragmentation
|----------row-level fragmentation
|----------index leaf block-level fragmentation
2 具体处理方式
注:所有操作使用sysdba权限操作,操作之前请将正在使用的数据库备份(切记)。
一、 方式一(SQL执行的方式结果为推荐式)
SELECT 'Segment Advice --------------------------'|| chr(10) || 'TABLESPACE_NAME : ' || tablespace_name || chr(10) || 'SEGMENT_OWNER : ' || segment_owner || chr(10) || 'SEGMENT_NAME : ' || segment_name || chr(10) || 'ALLOCATED_SPACE : ' || allocated_space || chr(10) || 'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) || 'RECOMMENDATIONS : ' || recommendations || chr(10) || 'SOLUTION 1 : ' || c1 || chr(10) || 'SOLUTION 2 : ' || c2 || chr(10) || 'SOLUTION 3 : ' || c3 Advice FROM TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')) |
说明:
1. 执行脚本后请查阅详情请查看数据编辑器;
2. 遇到有‘movement’sql语句的solution请优先执行。
3. 验证方式,执行如下语句:
SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", /*\*04*\. */ BLOCKS "SUM_BLOCKS", /*05. */ SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", /*06.*/ ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' /*07. */ "USED_RATE(%)", /*08.*/ FREE_SPACE || 'M' "FREE_SPACE(M)" /*09. */ FROM ( SELECT TABLESPACE_NAME, /*10. */ ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, /*11. */ SUM (BLOCKS) BLOCKS /*12. */ FROM DBA_DATA_FILES /*13. */ GROUP BY TABLESPACE_NAME) D, /*14. */ ( SELECT TABLESPACE_NAME, /*15. */ ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE /*16. */ FROM DBA_FREE_SPACE /*17. */ GROUP BY TABLESPACE_NAME) F /*18. */WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) /*19. */UNION ALL --如果有临时表空间 /*20. */SELECT D.TABLESPACE_NAME, /*21.*/ SPACE || 'M' "SUM_SPACE(M)", /*22.*/ BLOCKS SUM_BLOCKS, /*23. */ USED_SPACE || 'M' "USED_SPACE(M)", /*24. */ ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", /*25. */ NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" /*26. */ FROM ( SELECT TABLESPACE_NAME, /*27. */ ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, /*28. */ SUM (BLOCKS) BLOCKS /*29. */ FROM DBA_TEMP_FILES /*30. */ GROUP BY TABLESPACE_NAME) D, /*31. */ ( SELECT TABLESPACE_NAME, /*32. */ ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, /*33. */ ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE /*34. */ FROM V$TEMP_SPACE_HEADER /*35. */ GROUP BY TABLESPACE_NAME) F /*36.*/ WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) /*37.*/ ORDER BY 1; |
二、 方式二(全面处理方式)
2.1 表空间级碎片
1. 查询系统表空间使用情况
执行如下语句:
select 'alter tablespace '||tablespace_name||' coalesce;'from ( select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI from dba_free_space group by tablespace_name having sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) <30 and tablespace_name not in('SYS','SYSTEM','DBSNMP','SYSMAN','XDB')) |
在一个有着足够有效自由空间,且FSFI 值超过30的表空间中,很少会遇见有效自由空间的问题。当一个空间将要接近可比参数时,就需要做碎片整理了(DMT空间可以整理,如果是LMT就无法整理,SMON 会将相邻的自由范围自动合并)
2. 操作方式
执行上一步查询的SQL语句。
3. 收缩空闲表空间
select /*+ ordered use_hash(a,c) */ 'alter database datafile '''||a.file_name||''' resize ' ||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||'M;', a.filesize, c.hwmsize from ( select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files ) a, ( select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c where a.file_id = c.file_id and a.filesize - c.hwmsize > 100; |
执行查询出的SQL语句。
2.2 段级碎片
2.2.1 表级碎片
这里主要是对表碎片的整理与空间的收缩两方面,以达到碎片的整理目的。可通过两种方式进行操作,请根据实际需要选择。
以下两种方式选其一即可。
1. 空间收缩优于数据增长,碎片清理不彻底
a) 原理
操作项 | 执行语句 |
shrink必须开启行迁移功能。 | alter table table_name enable row movement ; |
保持HWM,相当于把块中数据打结实了 | alter table table_name shrink space compact; |
回缩表与降低HWM | alter table table_name shrink space; |
回缩表与相关索引,降低HWM | alter table table_name shrink space cascade; |
重新编译失效对象 | SQL>@?/rdbms/admin/utlrp.sql |
b) 实施步骤
i. 执行如下语句:
select 'alter table '||OWNER||'.'||TABLE_NAME||' enable row movement;<br>'||'alter table '||OWNER||'.'||TABLE_NAME||' shrink space compact; <br>'||'alter table '||OWNER||'.'||TABLE_NAME||' shrink space; <br>'||'alter table '||OWNER||'.'||TABLE_NAME||' shrink space cascade;<br>'|| 'alter table '||OWNER||'.'||TABLE_NAME||' disable row movement;' from ( SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE, GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER, ROUND(BYTES/1024, 2) TABLE_KB, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS, CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER, DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE, NEXT_EXTENT, MAX_FREE_SPACE, O_TABLESPACE_NAME TABLESPACE_NAME FROM (SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES, B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM, DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0), 0, 1, ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0) ) + 2 AVG_USED_BLOCKS, ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER, ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS, A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C WHERE A.OWNER =B.OWNER and SEGMENT_NAME = TABLE_NAME and SEGMENT_TYPE = 'TABLE' AND B.TABLESPACE_NAME = C.NAME UNION ALL SELECT A.OWNER OWNER, SEGMENT_NAME || '.' || B.PARTITION_NAME, SEGMENT_TYPE, BYTES, B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM, DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0), 0, 1, ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0) ) + 2 AVG_USED_BLOCKS, ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER, ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS, A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME FROM SYS.DBA_SEGMENTS A, SYS.DBA_TAB_PARTITIONS B, SYS.TS$ C, SYS.DBA_TABLES D WHERE A.OWNER = B.TABLE_OWNER and SEGMENT_NAME = B.TABLE_NAME and SEGMENT_TYPE = 'TABLE PARTITION' AND B.TABLESPACE_NAME = C.NAME AND D.OWNER = B.TABLE_OWNER AND D.TABLE_NAME = B.TABLE_NAME AND A.PARTITION_NAME = B.PARTITION_NAME), (SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES) MAX_FREE_SPACE FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME AND GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25 AND OWNER not in ('SYS','SYSTEM','DBSNMP','SYSMAN','XDB') AND BLOCKS > 128 ORDER BY 10 DESC, 1 ASC, 2 ASC); |
ii. 导出为HTML格式,执行查询出的SQL语句。
iii. SQL>@?/rdbms/admin/utlrp.sql #SQLPlus运行
2. 数据增长优于空间收缩,碎片清理彻底
a) 原理
操作项 | 执行语句 |
整理表 | alter table table_name move stroage(initial 初始大小) |
整理对应表索引 | alter index index_name rebuild nologging online |
重新编译失效对象 | SQL>@?/rdbms/admin/utlrp.sql |
b) 实施步骤
i. 整理表
select 'alter table '||OWNER||'.'||TABLE_NAME||' move;' from ( SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE, GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER, ROUND(BYTES/1024, 2) TABLE_KB, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS, CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER, DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE, NEXT_EXTENT, MAX_FREE_SPACE, O_TABLESPACE_NAME TABLESPACE_NAME FROM (SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES, B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM, DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0), 0, 1, ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0) ) + 2 AVG_USED_BLOCKS, ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER, ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS, A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C WHERE A.OWNER =B.OWNER and SEGMENT_NAME = TABLE_NAME and SEGMENT_TYPE = 'TABLE' AND B.TABLESPACE_NAME = C.NAME UNION ALL SELECT A.OWNER OWNER, SEGMENT_NAME || '.' || B.PARTITION_NAME, SEGMENT_TYPE, BYTES, B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM, DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0), 0, 1, ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0) ) + 2 AVG_USED_BLOCKS, ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER, ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS, A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME FROM SYS.DBA_SEGMENTS A, SYS.DBA_TAB_PARTITIONS B, SYS.TS$ C, SYS.DBA_TABLES D WHERE A.OWNER = B.TABLE_OWNER and SEGMENT_NAME = B.TABLE_NAME and SEGMENT_TYPE = 'TABLE PARTITION' AND B.TABLESPACE_NAME = C.NAME AND D.OWNER = B.TABLE_OWNER AND D.TABLE_NAME = B.TABLE_NAME AND A.PARTITION_NAME = B.PARTITION_NAME), (SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES) MAX_FREE_SPACE FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME AND GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25 AND OWNER not in ('SYS','SYSTEM','DBSNMP','SYSMAN','XDB') AND BLOCKS > 128 ORDER BY 10 DESC, 1 ASC, 2 ASC); |
执行查询的SQL语句。
ii. 整理索引
select 'alter index ' || OWNER || '.' || segment_name || 'rebuild nologgin online;'from (select * from SYS.DBA_SEGMENTS where segment_type = 'INDEX'); |
执行查询的SQL语句。
iii. SQL>@?/rdbms/admin/utlrp.sql #SQLPlus运行
2.2.2 索引级碎片
1. 分析索引是否有碎片
执行语句:analyze index index_name validate structure;
注:index_name可以执行语句查看:
select * from dba_segments where segment_type = 'INDEX' and owner in ('MW_APP','MW_SYS','STATDBA','DICTDBA') |
a) 索引分析完成后,在index_stats查看其分析结果,执行如下语句:
select height, blocks, lf_blks, lf_rows, br_blks, br_rows , del_lf_rows from index_stats |
b) 索引整理原则如下:
i. 删除的行数如占总的行数的30%,即del_lf_rows / lf_rows > 0.3,那就考虑索引碎片整理;
ii. 如果”hight“大于4,可以考虑碎片整理;
iii. 如果索引的行数(LF_rows)远远小于‘LF_BLKS’ ,那就说明有了一个大的删除动作,需要整理碎片。
c) 索引碎片整理方法
alter index index_name rebuild nologging online ;
2. 分析处理系统索引扩展次数大于10次
SELECT 'alter index ' || owner || '.' || segment_name || ' rebuild nologgin online;' FROM ( SELECT COUNT ( * ), owner, segment_name, t.tablespace_name FROM dba_extents t WHERE t.segment_type = 'INDEX' AND t.owner NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','XDB') GROUP BY owner, segment_name, t.tablespace_name HAVING COUNT ( * ) > 10 ORDER BY COUNT ( * ) DESC); |
执行查询的SQL语句。