优化01-统计信息

Oracle 的统计信息是数据库优化器生成高效执行计划的核心依据。它记录了数据库对象（如表、索引、列等）的元数据信息，帮助优化器评估查询成本并选择最优执行路径。以下是关于 Oracle 统计信息的详细介绍：

一、统计信息的分类

表统计信息

• 行数（NUM_ROWS）：表的近似行数（可能基于采样）。
• 块数（BLOCKS）：表占用的数据块总数。
• 空块数（EMPTY_BLOCKS）：未被数据占用的块数。
• 行迁移/链化（ROW_MOVEMENT）：标识是否有行因更新操作导致迁移或链化。
• 平均行长度（AVG_ROW_LEN）：表中每行的平均字节数。

索引统计信息

• 叶子块数（LEAF_BLOCKS）：索引的叶子节点块数。
• 层级（BLEVEL）：索引的高度（根节点到叶子节点的层数）。
• 聚簇因子（CLUSTERING_FACTOR）：衡量索引列与表数据物理存储顺序的匹配程度。值越低，索引效率越高。
• 唯一值数量（DISTINCT_KEYS）：索引列的唯一值数量。

列统计信息

• 唯一值数量（NUM_DISTINCT）：列中不同值的数量。
• 高频值（DENSE_RANK）：最常见的值及其出现频率。
• 空值比例（NUM_NULLS）：列中 NULL 值的数量。
• 直方图（HISTOGRAM）：描述列数据分布的详细统计信息（可选）。

直方图（Histogram）

• 频率直方图（FREQUENCY）：记录每个列值的精确出现次数（适用于低基数列）。
• 高度均衡直方图（HEIGHT BALANCED）：将数据划分为相等大小的区间，记录每个区间的行数（适用于高基数列）。
• 拓扑直方图（TOP-N）：仅记录前 N 个高频值（适用于需要快速分析 TOP 值的场景）。

二、统计信息的存储位置

统计信息存储在以下数据字典视图中：

• 表统计信息：DBA_TABLES / USER_TABLES
• 索引统计信息：DBA_INDEXES / USER_INDEXES
• 列统计信息：DBA_TAB_COLUMNS / USER_TAB_COLUMNS
• 直方图信息：DBA_HISTOGRAMS / USER_HISTOGRAMS

三、统计信息的收集方法

自动收集（Auto Optimizer Stats Collection）

• 机制：Oracle 后台进程 auto optimizer stats collection 定期（默认每小时）收集统计信息。

• 触发条件：

  • 表的数据变更量超过 10%（通过 DBMS_STATS 的 ESTIMATE_PERCENT 计算）。
  • AWR 快照生成时（如果统计信息过期）。

• 管理命令：

  -- 查看自动作业状态
  SELECT * FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME = 'auto optimizer stats collection';
  -- 禁用自动收集
  EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('auto optimizer stats collection', TRUE, TRUE);
  -- 启用自动收集
  EXEC DBMS_AUTO_TASK_ADMIN.ENABLE('auto optimizer stats collection');
  

手动收集（Manual Collection）

使用 DBMS_STATS 包手动收集统计信息，支持精细控制：

• 收集整个数据库：

  EXEC DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 自动选择采样比例degree          => 8,                             -- 并行度cascade         => TRUE                           -- 收集索引和约束统计信息
  );
  

• 收集特定表：

  EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname        => 'HR',tabname        => 'EMPLOYEES',partname       => 'SALES_Q1',                     -- 分区名称（可选）method_opt     => 'FOR COLUMNS SAL SIZE 254',    -- 对 SAL 列生成直方图degree         => 4,cascade        => TRUE
  );
  

• 收集索引统计信息：

  EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname => 'HR', indname => 'EMP_IDX');
  

关键参数

• estimate_percent：采样比例。DBMS_STATS.AUTO_SAMPLE_SIZE 会根据数据量自动调整（推荐使用）。

• degree：并行度，加速统计信息收集。

• **

  method_opt
  

  **：控制列统计信息和直方图的生成方式。例如：

  • FOR ALL COLUMNS SIZE AUTO：自动决定是否为列生成直方图。
  • FOR COLUMNS SAL SIZE 254：强制为 SAL 列生成最大桶数的直方图。

• cascade：是否同时收集索引和约束的统计信息（默认 TRUE）。

四、查看统计信息

表和索引统计信息

-- 查看表统计信息
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, LAST_ANALYZED 
FROM USER_TABLES 
WHERE TABLE_NAME = 'EMPLOYEES';-- 查看索引统计信息
SELECT INDEX_NAME, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR, LAST_ANALYZED 
FROM USER_INDEXES 
WHERE INDEX_NAME = 'EMP_IDX';

列统计信息

-- 查看列的唯一值数量和空值比例
SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, DENSITY 
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME = 'EMPLOYEES';

直方图信息

-- 查看列的直方图数据
SELECT ENDPOINT_VALUE, ENDPOINT_NUMBER 
FROM USER_HISTOGRAMS 
WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = 'SALARY';

统计信息的维护策略

4.1最佳实践

• 定期收集：在业务低峰期手动收集关键表（如频繁更新的表或大表）。
• 监控AWR报告：通过 Top SQL 和 Segments by DB Time 发现统计信息失效的线索。
• 避免过度采样：默认的 AUTO_SAMPLE_SIZE 通常足够，除非需要精确分析数据分布。

4.2锁定统计信息

防止自动作业覆盖手动收集的结果：

-- 锁定表的统计信息
EXEC DBMS_STATS.LOCK_TABLE_STATS(ownname => 'HR', tabname => 'EMPLOYEES');
-- 解锁
EXEC DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'HR', tabname => 'EMPLOYEES');

处理过时统计信息

• 手动刷新：当表数据变更显著时（如批量插入、删除），立即重新收集统计信息。
• 使用 DBMS_STATS.LOCK_STATS：防止自动作业干扰手动维护。

六、常见问题与解决方案

统计信息过期导致性能下降

• 现象：执行计划突然变差，AWR 报告提示 Top SQL 或 Top SQL Text。

• 解决：手动收集相关表的统计信息：

  EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
  

直方图缺失或不准确

• 现象：优化器未选择索引扫描，但实际数据分布适合索引。

• 解决：强制生成直方图

  EXEC DBMS_STATS.GATHER_TABLE_STATS(OPTIONS => 'GATHER AUTO', METHOD_OPT => 'FOR COLUMNS SAL SIZE 254');
  

分区表统计信息未同步

• 现象：分区表的子分区统计信息未更新。

• 解决：指定分区名称收集统计信息：

  EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', partname => 'SALES_Q1');
  

七、高级功能

增量统计信息（Incremental Statistics）

针对分区表，自动合并子分区的统计信息到父分区：

ALTER TABLE employees SET STATISTICS LEVEL INCREMENTAL;

SQL Plan Management (SPM)

结合统计信息捕获和固定执行计划，防止计划回归：

-- 捕获当前执行计划
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'abc123');

八、示例：完整维护流程

-- 1. 手动收集整个数据库的统计信息（并行度 8，自动采样）
BEGINDBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree           => 8,cascade          => TRUE);
END;
/-- 2. 验证表统计信息
SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED 
FROM USER_TABLES 
WHERE TABLE_NAME = 'EMPLOYEES';-- 3. 为 SAL 列生成直方图
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname        => 'HR',tabname        => 'EMPLOYEES',method_opt     => 'FOR COLUMNS SAL SIZE 254'
);-- 4. 锁定统计信息
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');

九、总结

Oracle 统计信息是优化器高效工作的基石。通过合理配置自动收集、手动维护和监控策略，可以确保数据库始终基于最新、准确的统计信息生成最优执行计划。对于复杂场景（如分区表、高基数列），需结合直方图、增量统计信息等高级功能，进一步提升性能调优的精准度。