做DBA这些年碎片处理算是绕不开的老话题。尤其是攒了几千万行的大表平时增删改查都还凑合一旦做完批量清理或者大版本维护性能突然就拉胯。群里一聊往往第一反应都是是不是碎片太多了说实话这问题既简单又复杂。简单在于处理动作就那几个表用 MOVE 或 SHRINK索引用 REBUILD 或 COALESCE复杂在于碎片率怎么量化判断、什么时候该动、怎么动不影响业务、动完还要做什么这一串做不严谨很容易弄巧成拙。这篇文章把我实际运维中的排查经验、处理脚本和一些踩坑教训整理出来适合DBA、运维开发以及需要自己维护Oracle库的朋友参考。1. 碎片到底从哪来从存储结构说起1.1 段、区、块碎片的第一层来源Oracle 表数据物理上以段Segment为单位管理段下面分区Extent区再往下才是数据块Block。每张表和每个索引都有自己独立的段。建表初期系统只分配少量区数据增长时再批量追加。问题就出在这个“追加”上一张表反复经历插入、清理、再插入它的区分布会变得七零八落相邻区不再连续原本可以顺序扫描的路径被迫变成跳跃式访问物理读自然上去。可以把这个结构类比成仓库货架区是一排连续的货架块是单个货位。理想情况下表数据整整齐齐码在相邻几排搬运工顺着一条路线走完就行。碎片化之后同一张表的数据散布在仓库不同角落搬运工每次都得来回跑腿效率肉眼可见地下降。更麻烦的是统计信息与实际存储结构差距拉大后优化器算出来的成本是错的执行计划也会跟着跑偏。1.2 高水位线比想象中更坑的存在高水位线HWM指的是段内曾经使用过的最高块位置。它才是表碎片最典型、也最容易被忽略的表现。DELETE 大量行之后块里的数据被清空但高水位线不会自动回落。全表扫描依然会扫描到高水位线为止哪怕这些块已经全是空壳。这就好比胡同口的路灯沿街的房子都拆平了路灯还杵在原地每晚巡逻依然要走到胡同底再折返。与高水位线相伴的还有行迁移和行链接。UPDATE 导致行长度变大、原块放不下时Oracle 会把整行搬到新块并在原位置留一个指针这叫行迁移如果行大到单个块都装不下就会被拆成多段存放这叫行链接。不管哪种访问一行数据可能需要多次 I/O性能损耗非常直接。碎片处理的核心目标说穿了就两件事压低高水位线、压缩段空间同时把行迁移和行链接的数量降下来。2. 表碎片怎么查、怎么处理2.1 用数据说话怎么判断表碎不碎不能光看段大小也不能凭感觉“这表该整理了吧”。最省力的经验是先查dba_segments和dba_tables用统计信息估算数据实际占用真实数据量约等于num_rows × avg_row_len把估算值和段实际字节数一对比碎片率基本就出来了。SELECT t.owner, t.table_name, ROUND(s.bytes / 1024 / 1024, 2) seg_mb, t.num_rows, ROUND(t.avg_row_len * t.num_rows / 1024 / 1024, 2) est_mb, ROUND((1 - (t.avg_row_len * t.num_rows) / s.bytes) * 100, 2) waste_pct FROM dba_tables t, dba_segments s WHERE t.owner s.owner AND t.table_name s.segment_name AND s.segment_type TABLE AND t.num_rows 100000 ORDER BY waste_pct DESC;waste_pct超过 30% 到 50% 就值得排期处理。有个前提必须强调这个脚本依赖统计信息准确度所以最好先对目标表跑一次dbms_stats再评估。想更精细的话可以用ANALYZE TABLE ... LIST CHAINED ROWS配合UTL_CHAINED_ROWS脚本建一张 chained_rows 表直接查出哪些行发生了迁移和链接。2.2 ALTER TABLE MOVE最直接的主方案处理方案的取舍主要看表的规模和可停机时间。能接受写阻塞的话ALTER TABLE MOVE是最干净利落的手段ALTER TABLE orders MOVE TABLESPACE users; -- 大表可以加并行和 NOLOGGING ALTER TABLE orders MOVE PARALLEL 8 NOLOGGING;MOVE 会新建一个段把数据重新紧凑排列再删掉旧段。高水位线在操作完成后自动落到实际数据末尾空间也能回收到表空间。有几个关键点务必记牢第一MOVE 会让该表上的普通索引全部变成 UNUSABLE执行完必须重建索引否则业务 SQL 直接走全表扫描比整理前还慢第二MOVE 前要预留足够表空间大约等于目标段大小再加上原段空间别让操作执行到一半报 ORA-01654第三并行度不要贪个人经验控制在 8 比较稳32 的并行很容易把 CPU 打满拖垮整个实例第四NOLOGGING 能减少大量 redo但前提是数据库没有开启 force logging否则这个参数不生效。2.3 SHRINK SPACE不重建段也能压缩如果不想让索引失效、也不想让表长时间不可用SHRINK 是更温和的方案。它有两个硬性前提表所在表空间必须是自动段空间管理ASSM表本身要开启 row movement。基本语法如下ALTER TABLE orders ENABLE ROW MOVEMENT; ALTER TABLE orders SHRINK SPACE CASCADE;SHRINK 同样可以拉低高水位线而且是在线操作执行期间允许 DML 等待通过对业务的影响比 MOVE 小很多。代价是它也会改变行的 ROWID任何依赖 ROWID 的物化视图、应用临时表、JDBC 缓存都可能被影响动手前必须检查清楚依赖关系。分区表建议逐个分区收缩别一把梭否则一个大操作拖太久不但占资源中途失败回滚也麻烦。选 MOVE 还是 SHRINK我的习惯是能接受写窗口选 MOVE速度快且彻底必须在线、表又特别大选 SHRINK但要接受执行时间更长、执行期间资源占用更持续。3. 索引碎片处理的完整路径3.1 索引碎片的判定标准索引碎片和表碎片成因类似但表现形式不一样。B 树索引在频繁 DELETE 之后叶子块占用率会下降甚至残留大量删除标记。扫描索引时逻辑读虚高实际却不产生多少有效数据这就是典型的索引碎片。经验判定方法有两个。最直接的是ANALYZE INDEX VALIDATE STRUCTURE然后查INDEX_STATSANALYZE INDEX orders_idx1 VALIDATE STRUCTURE; SELECT name, lf_rows, del_lf_rows, ROUND(del_lf_rows / lf_rows * 100, 2) del_pct FROM index_stats WHERE name ORDERS_IDX1;del_lf_rows占比超过 20%基本就值得处理了。另一个方法是用dba_indexes里的BLEVEL和LEAF_BLOCKS做趋势判断如果 BLEVEL 长期偏高或者 LEAF_BLOCKS 的增长速度和表行数严重脱节说明索引结构已经比较虚胖同样需要关注。3.2 REBUILD、COALESCE、SHRINK 的选择索引处理三种手段各有适用场景。REBUILD 是最推荐的方式相当于用同样的字段结构重新构建一棵紧凑的 B 树存储参数可以重设还能在线执行ALTER INDEX orders_idx1 REBUILD ONLINE; -- 大索引可以配合并行和指定表空间 ALTER INDEX orders_idx1 REBUILD ONLINE TABLESPACE idx_ts PARALLEL 4 NOLOGGING;COALESCE 不重建段只在现有块内尝试合并相邻叶子。它不会把索引搬到新的表空间碎片压缩效果也比较有限适合不想动索引物理结构、只想稍微清理一下的场景。SHRINK SPACE 和表的 SHRINK 类似在线压缩段空间同样适合轻量维护。实操中我认为 REBUILD ONLINE 基本是默认答案。低峰期做重建注意观察归档日志增长速度和 CPU 使用率必要时分几批执行每批重建一两个索引后歇一会儿把资源消耗摊平。3.3 索引表空间规划与避免回表的误区独立索引表空间的好处不只在管理层面。把表数据和索引放在不同数据文件上可以减少 I/O 竞争重建索引时也可以顺手把索引迁到规划好的idx_ts表空间后续容量管理更清晰。有一个观念需要纠正很多人以为重建索引能“优化回表”。这是两码事。回表次数由索引选择性和查询字段决定碎片整理只让索引扫描本身更快物理读更少并不会减少需要回表的行数。要真正避免回表得靠复合索引覆盖查询列这不是碎片整理能替代的。碎片该整理还是要整理但别指望它能解决错误的索引设计问题。4. 实战案例几千万行订单表的碎片处理全过程4.1 案例背景与诊断之前在生产库碰到一张 orders 表大约 4800 万行业务按月份清理过两次历史数据清理后只剩 1600 万行左右但段大小还是顶着 6.8GB。现象是报表跑批越来越慢全表扫描类的 SQL 经常超过预期时间。我用检测脚本一查waste_pct 约 65%属于典型的高水位线虚高再查dba_segments表有 1200 多个 extents两个二级索引的 del_lf_rows 占比也超过了 30%。处理窗口安排在周六凌晨大约有 3 小时可停机归档空间剩余 10GB 左右。我的计划是表用 MOVE 到新数据文件索引全部 REBUILD ONLINE整个过程控制在 2 小时内完成留 1 小时给验证和兜底。4.2 执行过程与参数选择先重新收集统计信息然后按顺序执行关键语句ALTER TABLE orders MOVE TABLESPACE users PARALLEL 8 NOLOGGING; ALTER INDEX orders_pk REBUILD ONLINE TABLESPACE idx_ts PARALLEL 8 NOLOGGING; ALTER INDEX orders_created_idx REBUILD ONLINE TABLESPACE idx_ts PARALLEL 8 NOLOGGING; ALTER INDEX orders_status_idx REBUILD ONLINE TABLESPACE idx_ts PARALLEL 8 NOLOGGING;MOVE 实际耗时约 26 分钟期间 DML 被阻塞我在值班群提前打了招呼业务侧没受到太大影响。索引重建一个接一个执行总耗时大约 40 分钟。并行度全部选 8没有提到 16 或更高怕的是并行进程和业务进程抢资源。所有 DDL 执行完毕后马上对表重新收集统计信息这一步不能省否则优化器还在用旧的高水位线数据做成本估算执行计划依然不准。4.3 效果验证与在线重定义备选处理后的验证结果挺直观orders 段从 6.8GB 降到 2.1GBextents 从 1200 多个降到 40 个三个索引的 leaf_blocks 平均下降 30% 以上del_lf_rows 全部归零全表扫描 SQL 从 40 秒回到 12 秒。这个结果其实在意料之中因为压缩掉的大部分空间本来就在高水位线以下属于典型的虚胖。万一遇到不能接受长时间锁的场景DBMS_REDEFINITION在线重定义是更稳妥的备选。它通过中间表同步数据可以在业务运行期间把表搬到新结构、新表空间。核心步骤是先调用can_redef_table检查可行性再start_redef_table启动在线重定义接着copy_table_dependents复制依赖对象、sync_interim_table做增量同步最后finish_redef_table完成切换。代价是实施复杂度明显更高需要更严密的验证和回滚预案不建议作为日常碎片整理方案只在大表彻底搬迁且不允许停机时才用。5. 常见问题与排查技巧实录5.1 踩坑速查表现象原因处理方式MOVE 后查询反而变慢普通索引全部失效查询走了全表扫描执行完 MOVE 后必须重建所有索引SHRINK 报 ORA-10631未开启 ROW MOVEMENT先执行ALTER TABLE ... ENABLE ROW MOVEMENTMOVE 报 ORA-01654目标表空间剩余空间不足预留表大小 1 倍以上空间或扩容表空间REBUILD ONLINE 长时间不结束大量未提交事务或快照过旧先查v$session和v$undostat选低峰执行并行度过高导致 CPU 打满并行度设置到 16 或更高控制在 4 到 8同时观察归档日志增长分区表 move 后全局索引不可用全局索引跨分区失效重建全局索引或评估改用本地索引5.2 让脚本自动干活的思路手工维护几十张表和上百个索引太费劲。可以利用数据字典生成标准 DDL再用DBMS_SCHEDULER定时跑。核心逻辑其实就是一个动态语句生成器BEGIN FOR c IN ( SELECT ALTER INDEX || owner || . || index_name || REBUILD ONLINE NOLOGGING; sql_text FROM dba_indexes WHERE table_owner APP AND status VALID AND blevel 2 AND leaf_blocks 5000 ) LOOP dbms_output.put_line(c.sql_text); END LOOP; END;在此基础上加一张执行日志表、异常捕获逻辑和失败告警就是一个能用的自动碎片整理工具。我的习惯是先在小表上试跑几次观察每一轮的 redo 生成量和等待事件确认脚本不会冲击业务再逐步放开到所有目标表。5.3 我个人的几条实操习惯碎片处理前一定先记录段大小、行数、统计信息采集时间处理后再做一次对比这样给领导汇报时手里有数据也方便判断这次操作到底值不值。有一次我在周五下午手滑对核心表执行了 MOVE虽然操作本身很快完成了但业务方正好在跑批结果被投诉到值班群。从那以后所有碎片整理脚本都放进调度系统只允许窗口期执行。窗口充足时用 MOVE窗口紧张用 SHRINK但无论哪种方式处理完之后的统计信息收集绝对不能省。最后说一句如果某张表每隔一两个月就严重碎片化别只想着定期维护更要从应用层排查是不是存在频繁 DELETE 加 INSERT 的写法治理源头永远比被动维护更有效。