大家好我是睿。表碎片是Oracle DBA日常维护中经常遇到的问题。很多表在经过大量数据插入和删除后高水位线一直居高不下即使实际数据已经很少全表扫描时还是会读很多无效块导致性能变差。最近我专门做了一个实验用同一张表对比了 SHRINK 和 MOVE 两种缩表方式的实际效果包括操作步骤、资源消耗、整理后的段大小和索引情况等。今天把整个过程完整分享出来供大家参考。一、表碎片是怎么产生的简单来说当表频繁INSERT数据后又执行大量DELETE操作时高水位线只会上升不会自动下降。DELETE只会把数据块标记为空闲但高水位线位置不变。所以全表扫描时Oracle仍然会扫描到原来的高水位线产生大量无效逻辑读。二、两种主流缩表方式SHRINK方式10g引入支持在线操作必须先启用行移动ALTERTABLEmary.testENABLEROWMOVEMENT;第一步压缩整理不释放空间ALTERTABLEmary.test SHRINK SPACE COMPACT;第二步真正释放空间并处理索引ALTERTABLEmary.test SHRINK SPACECASCADE;MOVE方式效果最彻底ALTERTABLEmary.test MOVE;MOVE完成后所有相关索引都会失效必须重建ALTERINDEXidx_test_id REBUILD ONLINE;三、如何判断表是否存在碎片我常用下面这个SQL快速查看setlinesize500setpagesize300col ownerfora15 col table_namefora10SELECTowner,table_name,num_rows,ROUND((blocks*8)/1024,2)HIGH_WATER_MB,ROUND((num_rows*avg_row_len/1024/1024),2)REAL_DATA_MB,ROUND((blocks*8-num_rows*avg_row_len/1024-blocks*8*0.1)/1024,2)WASTE_MBFROMdba_tablesWHEREownerMARYANDtable_nameTEST;如果想看得更详细可以使用 show_space 存储过程适用于ASSM表空间 show_space完整存储过程createorreplaceprocedureshow_space(p_segname_1invarchar2,p_spaceinvarchar2defaultMANUAL,p_type_1invarchar2defaultTABLE,p_analyzedinvarchar2defaultN,p_owner_1invarchar2defaultuser)asp_segname varchar2(100);p_type varchar2(10);p_owner varchar2(30);l_unformatted_blocks number;l_unformatted_bytes number;l_fs1_blocks number;l_fs1_bytes number;l_fs2_blocks number;l_fs2_bytes number;l_fs3_blocks number;l_fs3_bytes number;l_fs4_blocks number;l_fs4_bytes number;l_full_blocks number;l_full_bytes number;l_free_blks number;l_total_blocks number;l_total_bytes number;l_unused_blocks number;l_unused_bytes number;l_LastUsedExtFileId number;l_LastUsedExtBlockId number;l_LAST_USED_BLOCK number;procedurep(p_labelinvarchar2,p_numinnumber)isbegindbms_output.put_line(rpad(p_label,40,.)||p_num);end;beginp_segname :upper(p_segname_1);p_owner :upper(p_owner_1);p_type :p_type_1;if(p_type_1iorp_type_1I)thenp_type :INDEX;endif;if(p_type_1torp_type_1T)thenp_type :TABLE;endif;if(p_type_1corp_type_1C)thenp_type :CLUSTER;endif;dbms_space.unused_space(segment_ownerp_owner,segment_namep_segname,segment_typep_type,total_blocksl_total_blocks,total_bytesl_total_bytes,unused_blocksl_unused_blocks,unused_bytesl_unused_bytes,LAST_USED_EXTENT_FILE_IDl_LastUsedExtFileId,LAST_USED_EXTENT_BLOCK_IDl_LastUsedExtBlockId,LAST_USED_BLOCKl_LAST_USED_BLOCK);ifp_spaceMANUALor(p_spaceautoandp_spaceAUTO)thendbms_space.free_blocks(segment_ownerp_owner,segment_namep_segname,segment_typep_type,freelist_group_id0,free_blksl_free_blks);p(Free Blocks,l_free_blks);endif;p(Total Blocks,l_total_blocks);p(Total Bytes,l_total_bytes);p(Unused Blocks,l_unused_blocks);p(Unused Bytes,l_unused_bytes);p(Last Used Ext FileId,l_LastUsedExtFileId);p(Last Used Ext BlockId,l_LastUsedExtBlockId);p(Last Used Block,l_LAST_USED_BLOCK);/*IF the segment is analyzed */ifp_analyzedYthendbms_space.space_usage(segment_ownerp_owner,segment_namep_segname,segment_typep_type,unformatted_blocksl_unformatted_blocks,unformatted_bytesl_unformatted_bytes,fs1_blocksl_fs1_blocks,fs1_bytesl_fs1_bytes,fs2_blocksl_fs2_blocks,fs2_bytesl_fs2_bytes,fs3_blocksl_fs3_blocks,fs3_bytesl_fs3_bytes,fs4_blocksl_fs4_blocks,fs4_bytesl_fs4_bytes,full_blocksl_full_blocks,full_bytesl_full_bytes);dbms_output.put_line(rpad( ,50,*));dbms_output.put_line(The segment is analyzed);p(0% -- 25% free space blocks,l_fs1_blocks);p(0% -- 25% free space bytes,l_fs1_bytes);p(25% -- 50% free space blocks,l_fs2_blocks);p(25% -- 50% free space bytes,l_fs2_bytes);p(50% -- 75% free space blocks,l_fs3_blocks);p(50% -- 75% free space bytes,l_fs3_bytes);p(75% -- 100% free space blocks,l_fs4_blocks);p(75% -- 100% free space bytes,l_fs4_bytes);p(Unused Blocks,l_unformatted_blocks);p(Unused Bytes,l_unformatted_bytes);p(Total Blocks,l_full_blocks);p(Total bytes,l_full_bytes);endif;end;/使用方法SETSERVEROUTPUTONEXECshow_space(TEST,AUTO,TABLE,Y,MARY);四、完整实验过程我新建了两张测试表 mary.test2026、mary.test2026_2分别插入约500万行数据然后DELETE全量数据制造碎片分别用两种方式处理并观察效果。实验准备制造碎片创建测试表CREATETABLEmary.test2026(id NUMBERprimarykey,name VARCHAR2(50),create_timeDATE);插入测试数据BEGINFORiIN1..5000000LOOPINSERTINTOmary.test2026VALUES(i,test||i,SYSDATE);ENDLOOP;COMMIT;END;/创建第二张测试表、插入数据、添加主键createtablemary.test2026_2asselect*frommary.test2026;altertablemary.test2026_2modify(id NUMBERconstraintmary_pkprimarykey);收集统计信息executedbms_stats.gather_table_stats(MARY,TEST2026);executedbms_stats.gather_table_stats(MARY,TEST2026_2);查看两张测试表的段大小col segment_namefora40selectsegment_name,sum(bytes)/1024/1024assize_MBfromdba_segmentswheresegment_namein(TEST2026,TEST2026_2)andownerMARYgroupbysegment_name;SEGMENT_NAME size_MBTEST2026 168TEST2026_2 168查看两张表的高水位线与碎片情况setlinesize500setpagesize300col ownerfora15 col table_namefora10setlinesize500setpagesize300col ownerfora15 col table_namefora10SELECTowner,table_name,num_rows,ROUND((blocks*8)/1024,2)HIGH_WATER_MB,ROUND((num_rows*avg_row_len/1024/1024),2)REAL_DATA_MB,ROUND((blocks*8-num_rows*avg_row_len/1024-blocks*8*0.1)/1024,2)WASTE_MBFROMdba_tablesWHEREownerMARYANDtable_namein(TEST2026,TEST2026_2);OWNER TABLE_NAME NUM_ROWS HIGH_WATER_MB REAL_DATA_MB WASTE_MBMARY TEST2026 5000000 166.54 123.98 25.91MARY TEST2026_2 5000000 166.54 123.98 25.91删除全部数据DELETEFROMmary.test2026;DELETEFROMmary.test2026_2;COMMIT;重新收集统计信息executedbms_stats.gather_table_stats(MARY,TEST2026);executedbms_stats.gather_table_stats(MARY,TEST2026_2);这里我们可以看到数据量虽然全部删除但是两张表的数据段大小并没有减少col segment_namefora40selectsegment_name,sum(bytes)/1024/1024assize_MBfromdba_segmentswheresegment_namein(TEST2026,TEST2026_2)andownerMARYgroupbysegment_name;SEGMENT_NAME size_MBTEST2026 168TEST2026_2 168通过以下SQL能够更容易看到实际占用空间与碎片空间的比对。高水位线很高碎片非常严重。setlinesize500setpagesize300col ownerfora15 col table_namefora10setlinesize500setpagesize300col ownerfora15 col table_namefora10SELECTowner,table_name,num_rows,ROUND((blocks*8)/1024,2)HIGH_WATER_MB,ROUND((num_rows*avg_row_len/1024/1024),2)REAL_DATA_MB,ROUND((blocks*8-num_rows*avg_row_len/1024-blocks*8*0.1)/1024,2)WASTE_MBFROMdba_tablesWHEREownerMARYANDtable_namein(TEST2026,TEST2026_2);OWNER TABLE_NAME NUM_ROWS HIGH_WATER_MB REAL_DATA_MB WASTE_MBMARY TEST2026 0 166.54 0 149.89MARY TEST2026_2 0 166.54 0 149.89实验1对mary.test2026表执行SHRINKALTERTABLEmary.test2026ENABLEROWMOVEMENT;ALTERTABLEmary.test2026 SHRINK SPACE COMPACT;ALTERTABLEmary.test2026 SHRINK SPACECASCADE;实验2对mary.test2026_2执行MOVE重新制造碎片后ALTERTABLEmary.test2026_2 MOVE;重新收集统计信息executedbms_stats.gather_table_stats(MARY,TEST2026);executedbms_stats.gather_table_stats(MARY,TEST2026_2);观测实验结果先检查索引情况可以看到SHRINK方式后索引状态正常MOVE方式则导致MARY_PK索引失效。setlinesize600setpagesize500col INDEX_NAMEfora20 col TABLE_OWNERfora20 col TABLE_NAMEfora30 colSTATUSfora30selectINDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUSfromdba_indexeswhereTABLE_OWNERMARY;INDEX_NAME TABLE_OWNER TABLE_NAME STATUSSYS_C009491 MARY TEST2026 VALIDMARY_PK MARY TEST2026_2 UNUSABLE特殊说明在原实验中我此处做出来的结果MARY_PK没有失效因为所有数据都被清空的原因但如果表中还有数据索引一定是会失效的重建test2026_2表的失效索引ALTERINDEXmary.MARY_PK REBUILD ONLINE;setlinesize500setpagesize300col ownerfora15 col table_namefora10setlinesize500setpagesize300col ownerfora15 col table_namefora10SELECTowner,table_name,num_rows,ROUND((blocks*8)/1024,2)HIGH_WATER_MB,ROUND((num_rows*avg_row_len/1024/1024),2)REAL_DATA_MB,ROUND((blocks*8-num_rows*avg_row_len/1024-blocks*8*0.1)/1024,2)WASTE_MBFROMdba_tablesWHEREownerMARYANDtable_namein(TEST2026,TEST2026_2);OWNER TABLE_NAME NUM_ROWS HIGH_WATER_MB REAL_DATA_MB WASTE_MBMARY TEST2026 0 .01 0 .01MARY TEST2026_2 0 0 0 0特殊说明本次实验使用的数据段过于连续并且是删除所有数据所以实验结果并不明显SHRINK方式仅残留了0.01MB的使用空间在生产环境中剩余的残留空间会更多SHRINK完成后观察1、段大小明显下降但仍有少量碎片残留高水位没有降到最低2、索引状态正常无需重建3、全表扫描的逻辑读显著减少但不是最优MOVE完成后观察1、高水位线彻底归零段大小降到了接近真实数据量 PCTFREE 的水平2、碎片清理非常干净全表扫描逻辑读达到接近TRUNCATE的效果3、但操作期间需要额外约等于表大小的空间新旧段同时存在4、所有索引失效必须重建五、实验结论与选型建议通过这次对比可以清晰看到MOVE在碎片整理效果上明显优于SHRINK高水位线清理得更彻底后续全表扫描性能更好空间释放也更干净。但它需要额外空间操作时锁表时间相对更长11g以上可以用ONLINE方式缓解。SHRINK操作更灵活对空间要求低大部分过程在线但碎片整理效果稍差有时还会残留少量碎片。我的实际建议1、如果空间充足、有维护窗口优先使用MOVE长远收益更高2、如果空间紧张或者业务不能接受较长时间锁表就选择SHRINK。不管哪种方式都强烈建议在业务低峰期或维护窗口操作。SHRINK的最后一步仍有锁MOVE更需要提前评估空间。操作完成后建议重新收集统计信息表碎片整理看似简单实际操作时需要综合考虑业务窗口、可用空间和性能要求。希望这次完整的实验记录能给大家一些参考。你在生产环境中主要用哪种方式处理表碎片欢迎在评论区分享你的经验或遇到的问题。—— 睿 | Oracle性能优化老司机专注硬核干货欢迎一起卷技术