oracle数据库优化-表碎片优化性能。

在Oracle数据库中表碎片Table Fragmentation通常是由于频繁的DELETE、UPDATE操作导致高水位线HWM, High Water Mark居高不下使得数据库在全表扫描时读取了大量包含已删除数据的空块从而降低查询性能并浪费存储空间。1 案例一次ogg数据库表同步索引选择性很好统计表统计信息也不管用无法进行执行计划绑定很纳闷发现表delete很频繁于是想重新对表进行碎片整理。对表进行重建后正常使用索引。以下是处理Oracle表碎片的主要方式、适用场景及操作步骤1. 核心处理方法对比方法命令示例是否在线是否需要行移动空间回收效果适用场景Shrink SpaceALTER TABLE ... SHRINK SPACE是 (Online)必须启用(ROW MOVEMENT)高(降低HWM释放空间给表空间)大多数堆表希望在线整理并释放空间Move TableALTER TABLE ... MOVE否 (Offline)必须启用(ROW MOVEMENT)高(重建表降低HWM)维护窗口充足或无法使用Shrink的旧版本/特殊情况在线重定义DBMS_REDEFINITION是 (Online)不需要 (内部处理)高(重建表)业务不能中断且表结构复杂或有依赖对象较多时导出导入expdp/impdp否 (Offline)不需要高跨平台迁移或极度碎片化需彻底重建时索引重建ALTER INDEX ... REBUILD可选 (ONLINE)N/AN/A (仅针对索引)配合表整理必须单独处理索引碎片2. 推荐方案ALTER TABLE ... SHRINK SPACE(最常用)这是Oracle 10g引入的最便捷方式可以在线压缩数据段调整高水位线并释放多余空间回表空间。前置条件表所在的表空间必须是本地管理 (Locally Managed)且自动段空间管理 (ASSM)。必须启用行移动功能因为压缩过程中行的物理位置RowID会发生改变。操作步骤-- 1. 检查并启用行移动 (Row Movement) ALTER TABLE your_table_name ENABLE ROW MOVEMENT; -- 2. 执行收缩 (分两步走更稳妥也可一步到位) -- 2.1 紧凑数据 (Compact)只整理碎片不降低高水位线对业务影响最小 ALTER TABLE your_table_name SHRINK SPACE COMPACT; -- 2.2 降低高水位线 (Cascade)释放空间给表空间并级联处理索引 -- 注意这一步会短暂持有锁建议在业务低峰期执行 ALTER TABLE your_table_name SHRINK SPACE CASCADE; -- 3. (可选) 如果之前为了安全开启了行移动且业务逻辑强依赖RowID不变可考虑关闭通常建议保持开启 -- ALTER TABLE your_table_name DISABLE ROW MOVEMENT;COMPACT: 仅整理块内碎片不调整HWM不释放空间耗时短几乎不锁表。CASCADE: 同时整理表及其所有相关索引的碎片。3. 备选方案ALTER TABLE ... MOVE如果表不支持SHRINK例如非ASSM表空间或者需要更改表的存储参数如表空间、PCTFREE等可以使用MOVE。特点缺点执行期间表不可用锁表所有索引会变为UNUSABLE状态必须重建。优点彻底重组数据适用于任何表空间类型。操作步骤-- 1. 启用行移动 ALTER TABLE your_table_name ENABLE ROW MOVEMENT; -- 2. 执行移动 (可指定新表空间或存储参数) ALTER TABLE your_table_name MOVE; -- 或者: ALTER TABLE your_table_name MOVE TABLESPACE new_tablespace; -- 3. 重建索引 (因为MOVE后索引全部失效) -- 手动重建 ALTER INDEX index_name REBUILD ONLINE; -- 或者批量重建失效索引 BEGIN FOR rec IN (SELECT index_name FROM user_indexes WHERE status UNUSABLE) LOOP EXECUTE IMMEDIATE ALTER INDEX || rec.index_name || REBUILD ONLINE; END LOOP; END; /4. 高级方案在线重定义 (DBMS_REDEFINITION)适用于对可用性要求极高几乎零停机、表上有复杂依赖如触发器、约束、权限的场景。它通过在后台创建新表、同步数据、切换指针的方式完成。流程复杂需要调用DBMS_REDEFINITION.START_REDEF_TABLE,SYNC_INTERIM_TABLE,FINISH_REDEF_TABLE等包。优势业务感知度最低无需手动处理索引和约束会自动迁移。5. 如何判断是否需要整理在执行操作前建议先评估碎片程度。以下SQL可查询表的“浪费空间”SELECT table_name, ROUND((blocks * 8 / 1024), 2) AS 高水位线大小 (MB), -- 假设块大小为8KB ROUND((num_rows * avg_row_len / 1024 / 1024), 2) AS 实际数据大小 (MB), ROUND(((blocks * 8) - (num_rows * avg_row_len / 1024)) / 1024, 2) AS 预估浪费空间 (MB) FROM user_tables WHERE table_name YOUR_TABLE_NAME AND (blocks * 8) (num_rows * avg_row_len / 1024) * 1.2; -- 浪费超过20%视为有碎片注需确保统计信息是最新的否则num_rows和avg_row_len可能不准。可先执行EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, TABLE_NAME);也可以使用Oracle自带的Segment Advisor进行诊断-- 创建任务 DECLARE tname VARCHAR2(100); BEGIN DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, tname); -- 具体参数设置较复杂通常通过Enterprise Manager (OEM) 图形界面操作更方便 END; /6. 重要注意事项RowID 变化SHRINK和MOVE都会改变行的ROWID。如果有应用程序硬编码了ROWID作为主键或引用这些程序将失效。索引失效SHRINK SPACE CASCADE会自动维护索引。SHRINK SPACE(不带cascade) 不会更新索引可能导致索引效率暂时下降需后续重建。MOVE会导致所有索引失效必须重建。Undo/Redo 压力碎片整理本质是大量数据的更新和移动会产生大量的 Undo 和 Redo 日志务必确保归档空间和Undo表空间充足。分区表如果是分区表建议按分区进行整理ALTER TABLE ... MODIFY PARTITION ... SHRINK SPACE避免一次性锁住整张大表。外键约束如果父表进行MOVE或SHRINK子表的外键约束可能会带来额外的锁竞争需谨慎评估。总结建议首选ALTER TABLE ... SHRINK SPACE CASCADE前提是ASSM表空间且允许Row Movement。大表/分区表按分区逐个执行 Shrink或在业务低峰期执行。非ASSM环境使用MOVEREBUILD INDEX。极致可用性使用DBMS_REDEFINITION。