Oracle 数据库膨胀分析

数据库膨胀概述

数据库膨胀是指数据库对象（如表、索引、分区等）占用的存储空间超过了实际数据所需的空间，导致存储资源浪费和性能下降的现象。数据库膨胀主要分为表膨胀和索引膨胀两种类型，通常由以下原因引起：

频繁的 DML 操作（插入、更新、删除）
不恰当的表设计
缺少定期维护
低效的存储管理

数据库膨胀会带来以下问题：

存储资源浪费
查询性能下降
备份和恢复时间延长
索引维护成本增加
I/O 性能下降

表膨胀分析

表膨胀的原因

删除操作：当表中的数据被删除后，Oracle 不会立即释放空间，而是将这些空间标记为可用，导致表占用的空间大于实际数据量
更新操作：特别是对变长列的更新，可能导致行迁移或行链接
分区表：分区表中的历史分区可能包含大量已删除的数据
LOB 列：LOB 列的存储管理不当可能导致空间浪费
回滚段：长时间运行的事务可能导致回滚段膨胀

表膨胀识别方法

使用 DBMS_SPACE 包分析表空间使用

sql

-- 分析表的空间使用情况
DECLARE
    v_seg_size NUMBER;
    v_used_size NUMBER;
    v_free_size NUMBER;
    v_free_blocks NUMBER;
BEGIN
    DBMS_SPACE.SPACE_USAGE(
        segment_owner => 'SCOTT',
        segment_name => 'EMP',
        segment_type => 'TABLE',
        total_blocks => v_seg_size,
        used_blocks => v_used_size,
        expired_blocks => NULL,
        unexpired_blocks => NULL
    );
    
    DBMS_OUTPUT.PUT_LINE('Total blocks: ' || v_seg_size);
    DBMS_OUTPUT.PUT_LINE('Used blocks: ' || v_used_size);
    DBMS_OUTPUT.PUT_LINE('Free blocks: ' || (v_seg_size - v_used_size));
    DBMS_OUTPUT.PUT_LINE('Bloat ratio: ' || ROUND((v_seg_size - v_used_size) / v_seg_size * 100, 2) || '%');
END;
/

查看表的实际数据量与占用空间

sql

-- 查看表的实际数据量与占用空间
SELECT
    t.table_name,
    t.num_rows,
    t.avg_row_len,
    ROUND((t.num_rows * t.avg_row_len) / 1024 / 1024, 2) AS estimated_data_mb,
    ROUND(s.bytes / 1024 / 1024, 2) AS actual_size_mb,
    ROUND((s.bytes - (t.num_rows * t.avg_row_len)) / s.bytes * 100, 2) AS bloat_pct
FROM
    dba_tables t
    JOIN dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name
WHERE
    t.owner = 'SCOTT'
    AND t.table_name = 'EMP';

分析表的行迁移和行链接

sql

-- 分析表的行迁移和行链接
ANALYZE TABLE SCOTT.EMP LIST CHAINED ROWS;

SELECT
    COUNT(*) AS chained_rows_count
FROM
    CHAINED_ROWS
WHERE
    table_name = 'EMP' AND owner_name = 'SCOTT';

表膨胀处理方法

1. 重建表

sql

-- 方法 1：使用 CREATE TABLE AS SELECT
CREATE TABLE SCOTT.EMP_NEW AS SELECT * FROM SCOTT.EMP;
DROP TABLE SCOTT.EMP;
ALTER TABLE SCOTT.EMP_NEW RENAME TO EMP;

-- 方法 2：使用 ALTER TABLE MOVE
ALTER TABLE SCOTT.EMP MOVE;
-- 重建索引
ALTER INDEX SCOTT.PK_EMP REBUILD;

2. 收缩表（10g+）

sql

-- 启用行移动
ALTER TABLE SCOTT.EMP ENABLE ROW MOVEMENT;
-- 收缩表
ALTER TABLE SCOTT.EMP SHRINK SPACE;
-- 收缩表及索引
ALTER TABLE SCOTT.EMP SHRINK SPACE CASCADE;
-- 仅收缩表空间，不调整高水位线
ALTER TABLE SCOTT.EMP SHRINK SPACE COMPACT;

3. 分区表维护

sql

-- 重建分区
ALTER TABLE SCOTT.SALES MOVE PARTITION SALES_Q1_2023;
-- 收缩分区
ALTER TABLE SCOTT.SALES SHRINK SPACE PARTITION SALES_Q1_2023;
-- 合并分区
ALTER TABLE SCOTT.SALES MERGE PARTITIONS SALES_Q1_2023, SALES_Q2_2023 INTO PARTITION SALES_H1_2023;

4. LOB 列优化

sql

-- 分析 LOB 列空间使用
SELECT
    table_name,
    column_name,
    ROUND((bytes / 1024 / 1024), 2) AS lob_size_mb
FROM
    dba_lobs
WHERE
    owner = 'SCOTT' AND table_name = 'EMP';

-- 重建 LOB 段
ALTER TABLE SCOTT.EMP MOVE LOB(EMP_PHOTO) STORE AS (TABLESPACE USERS);

索引膨胀分析

索引膨胀的原因

频繁的 DML 操作：特别是删除和更新操作，会导致索引叶节点出现空洞
不恰当的索引设计：过多的索引或不合理的索引结构
低效的索引维护：缺少定期的索引重建或重组
序列生成器：使用递增序列生成索引键，可能导致索引右倾斜
并行 DML 操作：并行 DML 可能导致索引碎片

索引膨胀识别方法

查看索引的空间使用情况

sql

-- 查看索引的空间使用情况
SELECT
    i.index_name,
    i.table_name,
    i.leaf_blocks,
    i.distinct_keys,
    ROUND((i.leaf_blocks * 8192) / 1024 / 1024, 2) AS index_size_mb,
    ROUND((i.leaf_blocks * 8192) / i.distinct_keys, 2) AS bytes_per_key
FROM
    dba_indexes i
WHERE
    i.owner = 'SCOTT' AND i.table_name = 'EMP';

使用 DBMS_SPACE 包分析索引空间

sql

-- 分析索引的空间使用
DECLARE
    v_seg_size NUMBER;
    v_used_size NUMBER;
BEGIN
    DBMS_SPACE.SPACE_USAGE(
        segment_owner => 'SCOTT',
        segment_name => 'PK_EMP',
        segment_type => 'INDEX',
        total_blocks => v_seg_size,
        used_blocks => v_used_size
    );
    
    DBMS_OUTPUT.PUT_LINE('Index: PK_EMP');
    DBMS_OUTPUT.PUT_LINE('Total blocks: ' || v_seg_size);
    DBMS_OUTPUT.PUT_LINE('Used blocks: ' || v_used_size);
    DBMS_OUTPUT.PUT_LINE('Bloat ratio: ' || ROUND((v_seg_size - v_used_size) / v_seg_size * 100, 2) || '%');
END;
/

分析索引碎片

sql

-- 分析索引碎片
SELECT
    owner,
    index_name,
    blevel,
    leaf_blocks,
    distinct_keys,
    avg_leaf_blocks_per_key,
    avg_data_blocks_per_key,
    clustering_factor
FROM
    dba_indexes
WHERE
    owner = 'SCOTT' AND table_name = 'EMP';

索引膨胀处理方法

1. 重建索引

sql

-- 方法 1：使用 ALTER INDEX REBUILD
ALTER INDEX SCOTT.PK_EMP REBUILD;

-- 方法 2：使用 ALTER INDEX REBUILD ONLINE（允许并发 DML）
ALTER INDEX SCOTT.PK_EMP REBUILD ONLINE;

-- 方法 3：使用 ALTER INDEX COALESCE（合并叶节点，不重建索引结构）
ALTER INDEX SCOTT.PK_EMP COALESCE;

2. 分区索引维护

sql

-- 重建分区索引
ALTER INDEX SCOTT.IDX_SALES_DATE REBUILD PARTITION SALES_Q1_2023;

-- 合并分区索引
ALTER INDEX SCOTT.IDX_SALES_DATE COALESCE PARTITION SALES_Q1_2023;

3. 索引优化

删除不必要的索引
重新设计不合理的索引
考虑使用复合索引替代多个单列索引
对于频繁更新的列，考虑减少索引或使用位图索引（如果适合）

数据库膨胀监控

定期生成膨胀报告

创建表膨胀监控脚本

sql

-- 创建表膨胀监控表
CREATE TABLE dba_table_bloat_monitor (
    snapshot_time TIMESTAMP,
    owner VARCHAR2(30),
    table_name VARCHAR2(30),
    num_rows NUMBER,
    avg_row_len NUMBER,
    estimated_data_mb NUMBER,
    actual_size_mb NUMBER,
    bloat_pct NUMBER,
    chained_rows_count NUMBER
);

-- 创建表膨胀监控存储过程
CREATE OR REPLACE PROCEDURE monitor_table_bloat IS
BEGIN
    -- 清空临时表
    EXECUTE IMMEDIATE 'TRUNCATE TABLE CHAINED_ROWS';
    
    -- 分析所有表的行迁移和行链接
    FOR t IN (SELECT owner, table_name FROM dba_tables WHERE owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ANALYZE TABLE ' || t.owner || '.' || t.table_name || ' LIST CHAINED ROWS';
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
    END LOOP;
    
    -- 插入监控数据
    INSERT INTO dba_table_bloat_monitor
    SELECT
        SYSTIMESTAMP,
        t.owner,
        t.table_name,
        t.num_rows,
        t.avg_row_len,
        ROUND((t.num_rows * t.avg_row_len) / 1024 / 1024, 2) AS estimated_data_mb,
        ROUND(s.bytes / 1024 / 1024, 2) AS actual_size_mb,
        ROUND((s.bytes - (t.num_rows * t.avg_row_len)) / s.bytes * 100, 2) AS bloat_pct,
        (SELECT COUNT(*) FROM CHAINED_ROWS cr WHERE cr.owner_name = t.owner AND cr.table_name = t.table_name) AS chained_rows_count
    FROM
        dba_tables t
        JOIN dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name
    WHERE
        t.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
        AND t.num_rows > 0;
    
    COMMIT;
END;
/

创建索引膨胀监控脚本

sql

-- 创建索引膨胀监控表
CREATE TABLE dba_index_bloat_monitor (
    snapshot_time TIMESTAMP,
    owner VARCHAR2(30),
    index_name VARCHAR2(30),
    table_name VARCHAR2(30),
    leaf_blocks NUMBER,
    distinct_keys NUMBER,
    index_size_mb NUMBER,
    bytes_per_key NUMBER,
    blevel NUMBER,
    clustering_factor NUMBER
);

-- 创建索引膨胀监控存储过程
CREATE OR REPLACE PROCEDURE monitor_index_bloat IS
BEGIN
    -- 插入监控数据
    INSERT INTO dba_index_bloat_monitor
    SELECT
        SYSTIMESTAMP,
        i.owner,
        i.index_name,
        i.table_name,
        i.leaf_blocks,
        i.distinct_keys,
        ROUND((i.leaf_blocks * 8192) / 1024 / 1024, 2) AS index_size_mb,
        ROUND((i.leaf_blocks * 8192) / i.distinct_keys, 2) AS bytes_per_key,
        i.blevel,
        i.clustering_factor
    FROM
        dba_indexes i
    WHERE
        i.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
        AND i.leaf_blocks > 0
        AND i.distinct_keys > 0;
    
    COMMIT;
END;
/

设置膨胀告警阈值

根据业务需求和系统特点，设置合理的膨胀告警阈值，例如：

表膨胀率超过 50% 告警
索引膨胀率超过 60% 告警
行迁移和行链接数量超过 1000 告警

19c 和 21c 版本差异

数据库膨胀管理新特性

Oracle 19c 新特性

自动索引优化：Oracle 19c 引入了自动索引功能，能够自动识别和优化索引，减少索引膨胀
分区表增强：改进了分区表的维护功能，包括在线分区移动和合并
LOB 存储优化：改进了 LOB 列的存储管理，减少 LOB 段膨胀

Oracle 21c 新特性

实时膨胀检测：Oracle 21c 提供了实时数据库膨胀检测功能，能够自动识别膨胀的表和索引
自动表收缩：引入了自动表收缩功能，能够根据预设策略自动收缩膨胀的表
智能索引重建：改进了索引重建算法，减少了索引重建的时间和资源消耗
新视图 V$OBJECT_BLOAT：提供了实时的对象膨胀信息

新的膨胀分析工具

Oracle 19c 增强

DBMS_SPACE 包增强：改进了空间使用分析功能，提供了更详细的膨胀信息
AWR 报告增强：在 AWR 报告中添加了表和索引膨胀的统计信息

Oracle 21c 新增强

DBMS_SPACE.OBJECT_BLOAT_STATUS：新的存储过程，用于评估对象的膨胀状态
自动诊断工作流：能够自动生成膨胀分析报告和修复建议

生产环境最佳实践

预防数据库膨胀

合理的表设计：
- 选择合适的数据类型，避免使用过大的数据类型
- 合理设计表结构，避免过多的列
- 对于频繁更新的表，考虑使用较小的块大小
优化 DML 操作：
- 批量处理 DML 操作，减少事务次数
- 避免频繁的删除和更新操作
- 对于大量删除操作，考虑使用 TRUNCATE 替代 DELETE
合理的索引设计：
- 只创建必要的索引
- 合理设计复合索引，考虑列的顺序和选择性
- 对于频繁更新的列，谨慎创建索引
定期收集统计信息：
- 定期收集表和索引的统计信息
- 使用 DBMS_STATS 包自动收集统计信息
- 确保统计信息的准确性，以便优化器生成正确的执行计划

定期维护计划

每周任务：
- 运行表和索引膨胀监控脚本
- 分析膨胀报告，识别需要维护的对象
每月任务：
- 重建或收缩膨胀率超过 50% 的表
- 重建或合并膨胀率超过 60% 的索引
- 分析行迁移和行链接，采取相应措施
季度任务：
- 全面分析数据库膨胀情况
- 调整维护策略和告警阈值
- 优化表和索引设计

膨胀处理注意事项

维护窗口选择：
- 选择业务低峰期进行维护操作
- 对于大型表和索引，考虑使用在线操作
- 提前通知相关业务团队
备份策略：
- 在进行维护操作前，确保有有效的备份
- 对于关键业务表，考虑进行测试环境验证
性能影响评估：
- 评估维护操作对系统性能的影响
- 对于 RAC 环境，考虑节点负载均衡
- 监控维护操作的进度和资源使用
索引重建顺序：
- 先重建主键索引
- 然后重建唯一索引
- 最后重建普通索引

常见问题 (FAQ)

如何区分正常的空间使用和数据库膨胀？

正常的空间使用是指数据和索引实际占用的空间，而数据库膨胀是指对象占用的空间超过了实际数据所需的空间。可以通过以下方法区分：

比较表的实际大小与估算大小（基于行数和平均行长度）
分析表的行迁移和行链接数量
查看索引的叶节点使用率和键密度
监控对象大小的增长趋势

什么时候需要重建表或索引？

通常在以下情况下需要重建表或索引：

表膨胀率超过 50%
索引膨胀率超过 60%
行迁移和行链接数量较多
查询性能明显下降
备份和恢复时间过长

重建表和收缩表有什么区别？

重建表：创建一个新表，将原表数据复制到新表，然后删除原表，重命名新表。此操作会释放表占用的空间，但需要重建索引。
收缩表：通过移动行来消除表中的空洞，调整高水位线，释放未使用的空间。此操作不需要重建索引（使用 CASCADE 选项时会自动调整索引）。

如何减少索引膨胀？

减少索引膨胀的方法包括：

优化 DML 操作，减少删除和更新操作
定期重建或合并索引
合理设计索引，避免过多的索引
使用合适的索引类型，如位图索引或函数索引
考虑使用分区索引，分散索引负载

19c 和 21c 在数据库膨胀管理方面有什么主要区别？

Oracle 21c 在数据库膨胀管理方面相比 19c 有以下主要增强：

引入了实时膨胀检测功能
提供了自动表收缩功能
改进了索引重建算法
新增了 V$OBJECT_BLOAT 视图，提供实时膨胀信息
增强了 DBMS_SPACE 包，提供了更详细的膨胀分析功能
引入了智能索引重建功能

如何监控数据库膨胀趋势？

监控数据库膨胀趋势的方法包括：

定期运行膨胀监控脚本，收集历史数据
分析 AWR 报告中的表和索引大小变化
使用 OEM 或其他监控工具设置膨胀告警
定期生成膨胀趋势报告，预测未来的膨胀情况

总结

数据库膨胀是 Oracle 数据库中常见的性能问题，会导致存储资源浪费和性能下降。DBA 需要定期监控和分析数据库膨胀情况，采取相应的处理措施，如重建表、收缩表、重建索引等。

预防数据库膨胀同样重要，包括合理的表设计、优化 DML 操作、合理的索引设计和定期收集统计信息。在生产环境中，DBA 应该建立完善的膨胀监控和维护计划，确保数据库系统高效、稳定地运行。

随着 Oracle 版本的升级，数据库膨胀管理功能不断增强，DBA 应充分利用这些新特性，提高数据库膨胀管理的效率和效果。通过合理的设计、优化和维护，可以有效减少数据库膨胀，提高数据库系统的性能和可靠性。

Oracle 数据库膨胀分析 ​

数据库膨胀概述 ​

表膨胀分析 ​

表膨胀的原因 ​

表膨胀识别方法 ​

使用 DBMS_SPACE 包分析表空间使用 ​

查看表的实际数据量与占用空间 ​

分析表的行迁移和行链接 ​

表膨胀处理方法 ​

1. 重建表 ​

2. 收缩表（10g+） ​

3. 分区表维护 ​

4. LOB 列优化 ​

索引膨胀分析 ​

索引膨胀的原因 ​

索引膨胀识别方法 ​

查看索引的空间使用情况 ​

使用 DBMS_SPACE 包分析索引空间 ​

分析索引碎片 ​

索引膨胀处理方法 ​

1. 重建索引 ​

2. 分区索引维护 ​

3. 索引优化 ​

数据库膨胀监控 ​

定期生成膨胀报告 ​

创建表膨胀监控脚本 ​

创建索引膨胀监控脚本 ​

设置膨胀告警阈值 ​

19c 和 21c 版本差异 ​

数据库膨胀管理新特性 ​

Oracle 19c 新特性 ​

Oracle 21c 新特性 ​

新的膨胀分析工具 ​

Oracle 19c 增强 ​

Oracle 21c 新增强 ​

生产环境最佳实践 ​

预防数据库膨胀 ​

定期维护计划 ​

膨胀处理注意事项 ​

常见问题 (FAQ) ​

如何区分正常的空间使用和数据库膨胀？ ​

什么时候需要重建表或索引？ ​

重建表和收缩表有什么区别？ ​

如何减少索引膨胀？ ​

19c 和 21c 在数据库膨胀管理方面有什么主要区别？ ​

如何监控数据库膨胀趋势？ ​

总结 ​