如何正确收集Oracle分区索引的全局统计信息？

Question

在Oracle数据库中，正确收集分区索引的全局统计信息是优化查询性能的关键环节。常见的技术问题包括：**如何在不重建全局统计信息的前提下，确保其准确性和一致性？** 由于分区表和索引通常采用局部分区策略，单独对每个分区收集统计信息可能导致全局统计信息缺失或不准确。此时，使用`DBMS_STATS`包时，需设置`GRANULARITY`为`GLOBAL`或`AUTO`，并配合`CASCADE=TRUE`，以确保全局统计信息被正确聚合。 此外，还需注意统计信息的锁机制、分区交换对统计信息的影响，以及是否启用了统计信息自动合成（如Oracle 12c及以上版本的`APPROXIMATE_NDV`特性），以避免全表扫描带来的性能开销。

Answer 1

一、Oracle分区索引统计信息收集的背景与挑战

在Oracle数据库中，分区索引是提升大规模数据查询性能的重要手段。然而，由于分区索引通常采用局部分区策略，即每个分区拥有独立的索引结构，因此在收集统计信息时，若仅对每个分区单独执行统计信息收集操作，可能导致全局统计信息缺失或不准确。

全局统计信息对于优化器（CBO）选择最佳执行计划至关重要。如果全局统计信息未能正确聚合，优化器可能会做出错误的基数估计，进而影响查询性能。

二、统计信息收集的核心参数设置

使用 DBMS_STATS 包进行统计信息收集时，关键参数包括：

• GRANULARITY：指定统计信息收集的粒度，可设置为 GLOBAL、PARTITION 或 AUTO。
• CASCADE：当设置为 TRUE 时，表示同时收集索引统计信息。

为了确保全局统计信息被正确聚合，推荐设置如下：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SCHEMA_NAME',
    tabname          => 'TABLE_NAME',
    granularity      => 'GLOBAL',
    cascade          => TRUE,
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
  );
END;

三、统计信息一致性问题的常见技术挑战

在实际运维过程中，以下情况可能导致全局统计信息不一致或失效：

1. 仅对单个分区进行统计信息收集，未触发全局聚合。
2. 使用了错误的 GRANULARITY 参数，如设置为 PARTITION。
3. 分区交换操作后未更新统计信息，导致索引统计信息与实际数据不匹配。
4. 未启用统计信息自动合成功能，如 APPROXIMATE_NDV。

四、分区交换对统计信息的影响

当使用 ALTER TABLE ... EXCHANGE PARTITION 操作时，统计信息不会自动更新。此时，全局索引统计信息可能仍然反映旧分区的数据分布，导致查询计划偏差。

解决方法包括：

• 在交换完成后手动收集统计信息，并设置 GRANULARITY='GLOBAL'。
• 启用统计信息锁定机制，防止意外覆盖。

五、统计信息锁机制与自动合成特性

Oracle提供了统计信息锁机制，通过以下命令可以锁定或解锁统计信息：

-- 锁定统计信息
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

-- 解锁统计信息
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

此外，从Oracle 12c开始，支持统计信息自动合成（APPROXIMATE_NDV）特性，可显著减少全表扫描带来的性能开销。启用方式如下：

ALTER SYSTEM SET APPROXIMATE_NDV = TRUE;

六、统计信息收集策略与流程图

一个完整的统计信息收集流程应包括以下步骤：

1. 判断是否为分区表及索引结构。
2. 确认当前统计信息状态及是否锁定。
3. 选择合适的 GRANULARITY 与 CASCADE 参数。
4. 执行收集操作并验证统计信息准确性。

graph TD A[开始] --> B{是否为分区表？} B -->|是| C{是否已收集分区统计？} C -->|否| D[收集分区统计] D --> E[设置GRANULARITY=GLOBAL] E --> F[执行DBMS_STATS.GATHER_TABLE_STATS] F --> G[验证统计信息一致性] G --> H[结束] B -->|否| I[直接收集表统计] I --> H

七、统计信息收集的建议与最佳实践

场景	推荐设置	说明
全表统计收集	GRANULARITY=GLOBAL, CASCADE=TRUE	确保全局索引统计信息正确聚合
仅更新分区统计	GRANULARITY=PARTITION	适用于频繁更新的分区
分区交换后	手动收集全局统计	避免统计信息与数据不一致
大数据量表	APPROXIMATE_NDV=TRUE	减少全表扫描开销