🍿 2.7 Oracle表、索引碎片、行迁移、行链接检查

Oracle 数据库中，​表/索引碎片​、行迁移和行链接是影响性能的重要因素。本文提供完整的检查方法、SQL 脚本及处理建议。

一、概念与影响

1. 表碎片

• 定义：表在数据文件中存储不连续，存在大量空闲块或块内空间利用率低。
• 影响：全表扫描时读取更多块，缓存命中率下降，DML 可能引发更多空间管理操作。
• 常见原因：频繁的 DML（尤其是 DELETE）、未启用 ASSM、未设置合理的 PCTFREE。

2. 索引碎片

• 定义：索引块存在大量删除标记，或叶子块填充率低，B*树层次过高。
• 影响：索引扫描效率下降，逻辑读增加。
• 常见原因：频繁的 DML、未及时重建索引。

3. 行迁移

• 定义：UPDATE 使行长度增加，原块空间不足，行被迁移到新块，原块留下指针。
• 影响：读取该行需访问两个块，增加 I/O。
• 识别​：通过 V$SYSSTAT​ 的 table fetch continued row 统计。

4. 行链接

• 定义：行长度超过块大小（如含 LOB、长字段），行被拆分存储在多个块中。
• 影响：读取一行需访问多个块，性能下降。
• 识别​：同样可通过 table fetch continued row 和链式行分析表发现。

二、表/索引碎片检查

1. 表碎片（空间使用率）

方法一：使用 DBA_TABLES 计算平均使用率

SELECT owner, table_name,
       ROUND((blocks - empty_blocks) * 8192 / (avg_row_len * num_rows), 2) AS avg_blocks_per_row,
       ROUND((1 - (avg_row_len * num_rows) / ((blocks - empty_blocks) * 8192)) * 100, 2) AS waste_pct
FROM dba_tables
WHERE owner NOT IN ('SYS','SYSTEM')
  AND num_rows > 10000
  AND blocks > 100
ORDER BY waste_pct DESC;

说明​：waste_pct 为预估的块内空间浪费比例，超过 30% 建议重组。

[!NOTE]
案例1：

从你贴出的表碎片检查结果来看，部分表（如 TP_LTGLLCSJFZB​、TP_LTBPDWLSJ​）的 ​​WASTE_PCT​​ 高达 96% 以上​，AVG_BLOCKS_PER_ROW 也异常大（28.64、12.84），这极不寻常。以下是对数据的解读及处理建议。

一、指标含义

• ​AVG_BLOCKS_PER_ROW​：平均每行数据占用的 Oracle 块数（理论值应接近 1）。如果远大于 1，说明每行数据跨多个块存储，或表中存在大量已删除但未回收的空间。
• ​WASTE_PCT​​：根据公式 (1 - (avg_row_len * num_rows) / ((blocks - empty_blocks) * 8192)) * 100​ 计算出的​块内空间浪费比例。数值越高，表示数据在块中填充率越低。

你的数据中，TP_LTGLLCSJFZB​ 的 AVG_BLOCKS_PER_ROW = 28.64​，WASTE_PCT = 96.51%，意味着：

• 该表分配了大量数据块（高水位线很高），但其中实际数据极少。

• 平均每行数据竟分布在 28 个块中，这是极不正常的，通常是由以下原因导致：

  1. 大量数据被删除，但高水位线未降低（即删除后空间未被重用，导致后续插入占用新块，而旧块仍保留在高水位以下，形成“空壳”）。
  2. 行迁移/行链接严重​：每行因过长被拆分到多个块，但这种情况更常见于含 LOB 字段或超长行的表。如果这些表确实包含大字段（如 BLOB、CLOB 或长 VARCHAR2），则 AVG_BLOCKS_PER_ROW 可能略大于 1，但 28 倍仍过于夸张。
  3. 统计信息严重滞后​：如果表近期有大量 DML 但未收集统计信息，num_rows 可能远小于实际行数，导致计算失真。可以先重新收集统计信息再评估。

二、诊断与确认

1. 检查统计信息是否最新

SELECT owner, table_name, num_rows, last_analyzed
FROM dba_tables
WHERE table_name IN ('TP_LTGLLCSJFZB','TP_LTBPDWLSJ','SYS_JOB_LOG');

案例1：

如果 last_analyzed​ 距离当前时间较长，或 num_rows 与表实际行数相差很大，建议重新收集统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS('LXMES', 'TP_LTGLLCSJFZB');

2. 查看表的高水位与段空间

SELECT owner, segment_name, bytes/1024/1024 AS size_mb,
       blocks, empty_blocks, avg_space
FROM dba_segments
WHERE segment_name IN ('TP_LTGLLCSJFZB','TP_LTBPDWLSJ','SYS_JOB_LOG');

对比 bytes​ 与实际数据量。如果 size_mb 很大但实际数据很少，说明高水位过高。

3. 检查行迁移/链接

• 查看系统累计统计：
  sql

  SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';

  如果该值持续快速增长，说明存在较多行迁移或链接。

• 对具体表进行分析：
  sql

  ANALYZE TABLE LXMES.TP_LTGLLCSJFZB LIST CHAINED ROWS INTO CHAINED_ROWS;
  SELECT COUNT(*) FROM CHAINED_ROWS WHERE table_name = 'TP_LTGLLCSJFZB' AND owner_name = 'LXMES';

  如果链式行数量较多，说明存在行迁移/链接问题。

---

三、处理建议

方案一：重组表（MOVE）

sql

ALTER TABLE LXMES.TP_LTGLLCSJFZB MOVE TABLESPACE <目标表空间>;
-- MOVE 后索引会失效，需重建
ALTER INDEX LXMES.INDEX_NAME REBUILD;

• 优点：彻底释放高水位线以下的空间，消除行迁移（如有），使数据紧凑。
• 缺点：需要额外空间（在移动期间），会锁表，需要重建索引。

方案二：在线收缩（SHRINK SPACE）

sql

-- 首先开启行移动（允许行在块间移动）
ALTER TABLE LXMES.TP_LTGLLCSJFZB ENABLE ROW MOVEMENT;
-- 收缩段，压缩高水位线
ALTER TABLE LXMES.TP_LTGLLCSJFZB SHRINK SPACE COMPACT;   -- 紧凑段但不释放
ALTER TABLE LXMES.TP_LTGLLCSJFZB SHRINK SPACE;            -- 释放空间到表空间

• 优点：在线操作，不影响 DML（但可能稍有阻塞），无需重建索引（索引也会随之收缩）。
• 缺点：要求表空间为 ASSM（自动段空间管理）且表所在表空间必须支持行移动。

方案三：如果存在大量行迁移，且是 LOB 字段导致

• 考虑将 LOB 字段单独存储到不同表空间（LOB (col) STORE AS ...）。
• 调整 PCTFREE 为 UPDATE 预留空间，减少后续行迁移。

---

四、后续优化

• 对于频繁进行 DELETE 的表，可考虑使用 分区表 或定期 TRUNCATE 分区。
• 设置合适的 PCTFREE（例如，如果表更新频繁，可设置较大值）。
• 定期进行表空间碎片整理，如使用 MOVE​ 或 SHRINK。

---

五、总结

当前数据显示这些表的空间利用率极低，主要原因是高水位线过高（或统计信息过期）。建议：

1. 先重新收集统计信息，确认数据量。
2. 执行 SHRINK SPACE​ 或 MOVE 来回收空间。
3. 重组后重新收集统计信息，并监控 table fetch continued row 是否下降。

如果行迁移是问题根源，则 MOVE 也能一并解决。

方法二：使用 DBMS_SPACE 包（更精确）

DECLARE
   l_used_bytes  NUMBER;
   l_alloc_bytes NUMBER;
BEGIN
   DBMS_SPACE.OBJECT_SPACE_USAGE(
      'SCOTT', 'EMP', 'TABLE',
      used_bytes  => l_used_bytes,
      alloc_bytes => l_alloc_bytes
   );
   DBMS_OUTPUT.PUT_LINE('Used: ' || l_used_bytes || ' bytes, Allocated: ' || l_alloc_bytes || ' bytes');
END;
/

可编写循环输出多个表，或查询视图 DBA_TABLESPACE_USAGE_METRICS 辅助判断。

2. 索引碎片

方法一：分析索引结构（ANALYZE INDEX ... VALIDATE STRUCTURE）

ANALYZE INDEX idx_name VALIDATE STRUCTURE;
SELECT name, height, lf_rows, lf_blks, del_lf_rows,
       ROUND(del_lf_rows / lf_rows * 100, 2) AS del_pct
FROM index_stats;

• del_pct > 20% 建议重建。
• height > 3 可能需要重建（视数据量而定）。

方法二：通过 DBA_INDEXES 估算

SELECT owner, index_name, blevel, leaf_blocks, distinct_keys,
       ROUND((leaf_blocks / distinct_keys), 2) AS avg_leaf_blocks_per_key
FROM dba_indexes
WHERE owner NOT IN ('SYS','SYSTEM')
  AND blevel > 2
ORDER BY blevel DESC;

• blevel 过高（>3）表示索引层次深。
• 若 leaf_blocks​ 远大于 distinct_keys，可能有大量空块或碎片。

三、行迁移 / 行链接检查

1. 查看系统统计

SELECT name, value
FROM v$sysstat
WHERE name = 'table fetch continued row';

• 该值代表行迁移/链接导致的额外读取次数。若持续增长且数值较大，说明存在较多迁移行。

2. 识别具体表

步骤1：创建链式行分析表

@$ORACLE_HOME/rdbms/admin/utlchain.sql   -- 创建 CHAINED_ROWS 表

步骤2：分析目标表

ANALYZE TABLE schema.table_name LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE TB_IRON_LOG LIST CHAINED ROWS INTO CHAINED_ROWS; --真实表名VISUAL.SYS_JOB_LOG

步骤3：查询链式行数量

SELECT table_name, chain_cnt FROM dba_tables WHERE table_name = 'TB_IRON_LOG';

若结果 > 0，则存在行迁移或链接。

案例：
​

3. 区分行迁移与行链接

• 行迁移：通常发生在 UPDATE 后行变长，可通过重建表消除。
• 行链接：行初始长度就超过块大小，通常由于 LOB 或长 VARCHAR2 字段。需调整 PCTFREE 或使用 LOB 存储分离。

四、修复建议

1. 表碎片与行迁移

• 重组表（MOVE） ：

  ALTER TABLE schema.table_name MOVE TABLESPACE target_tbs;
  -- 注意：MOVE 后索引需重建

• 使用 SHRINK SPACE（需开启行移动） ：

  ALTER TABLE schema.table_name ENABLE ROW MOVEMENT;
  ALTER TABLE schema.table_name SHRINK SPACE COMPACT;
  ALTER TABLE schema.table_name SHRINK SPACE;

  SHRINK 可在线进行，但对段空间有要求（ASSM）。

2. 索引碎片

• 重建索引：

  ALTER INDEX schema.index_name REBUILD ONLINE;

  或使用 SHRINK SPACE 对索引（Oracle 10g+）：

  ALTER INDEX schema.index_name SHRINK SPACE;

3. 行迁移

• 首选 MOVE 表，可消除行迁移（因为整表重建，数据重排）。
• 若只是行链接，可能需要调整块大小或处理 LOB。

4. 优化空间参数

• PCTFREE：为 UPDATE 预留空间，避免行迁移。根据更新频率设置（通常 10-20）。
• PCTUSED：控制块何时回到空闲列表（仅对 MSSM 有效）。
• 使用 ​ASSM（自动段空间管理）表空间可减少碎片。

五、自动化检查脚本示例

以下 SQL*Plus 脚本输出关键信息，便于日常巡检：

set pages 100 lines 200
col owner for a15
col object_name for a30

prompt === 1. 表空间使用率（Top 5） ===
select tablespace_name, round((used_space/tablespace_size)*100,2) used_pct
from dba_tablespace_usage_metrics
order by used_pct desc
fetch first 5 rows only;

prompt === 2. 表碎片（预估浪费率 > 30%） ===
select owner, table_name, round((1 - (avg_row_len * num_rows) / ((blocks - empty_blocks) * 8192)) * 100,2) waste_pct
from dba_tables
where owner not in ('SYS','SYSTEM')
  and num_rows > 10000
  and blocks > 100
  and (1 - (avg_row_len * num_rows) / ((blocks - empty_blocks) * 8192)) > 0.3
order by waste_pct desc;

prompt === 3. 索引碎片（删除率 > 20% 或 blevel > 3） ===
select owner, index_name, blevel, leaf_blocks, distinct_keys,
       round(del_lf_rows / lf_rows * 100,2) del_pct
from index_stats
where del_pct > 20 or blevel > 3;

prompt === 4. 行迁移统计 ===
select name, value from v$sysstat where name = 'table fetch continued row';

注意：第 3 部分需提前对索引执行 ANALYZE INDEX ... VALIDATE STRUCTURE​，否则 index_stats 为空。

六、总结

定期（如每月）执行上述检查，及时发现并处理碎片和行迁移，可显著提升数据库性能。