Oracle统计信息管理：DBMS_STATS包实战技巧

一、统计信息核心概念

1.1 统计信息的作用与组成

Oracle优化器依赖统计信息来生成高效的执行计划。统计信息主要包括：

• 表统计信息：行数、块数、行平均长度等
• 列统计信息：不同值数量(NDV)、空值数量、数据分布等
• 索引统计信息：索引层级、叶块数、聚簇因子等
• 系统统计信息：CPU速度、I/O性能等

1.2 统计信息收集方法对比

二、DBMS_STATS基础操作

2.1 收集表统计信息

-- 基本表统计收集
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'HR',
    tabname          => 'EMPLOYEES',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    degree           => 4,
    cascade          => TRUE
  );
END;
/

-- 带特定参数的收集
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'SH',
    tabname          => 'SALES',
    estimate_percent => 10,  -- 10%采样
    block_sample     => TRUE, -- 使用块采样
    method_opt       => 'FOR COLUMNS SIZE 1 CUST_ID, FOR ALL COLUMNS SIZE AUTO',
    granularity      => 'ALL', -- 包括子分区
    cascade          => TRUE,
    no_invalidate    => FALSE -- 立即失效相关游标
  );
END;
/

2.2 收集模式统计信息

-- 收集整个模式统计信息
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => 'HR',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    degree           => 8,
    options         => 'GATHER AUTO',
    cascade         => TRUE
  );
END;
/

-- 只收集过期的统计信息
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => 'SH',
    options         => 'GATHER STALE',
    cascade         => TRUE
  );
END;
/

三、高级统计信息管理

3.1 增量统计信息收集

-- 启用表的增量统计
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(
    ownname => 'SH',
    tabname => 'SALES',
    pname   => 'INCREMENTAL',
    pvalue  => 'TRUE'
  );
  
  DBMS_STATS.SET_TABLE_PREFS(
    ownname => 'SH',
    tabname => 'SALES',
    pname   => 'INCREMENTAL_STALENESS',
    pvalue  => 'USE_STALE_PERCENT, 5'
  );
END;
/

-- 执行增量收集
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SH',
    tabname => 'SALES',
    granularity => 'AUTO',
    incremental => TRUE
  );
END;
/

3.2 系统统计信息管理

-- 收集系统统计信息(需要SYSDBA权限)
BEGIN
  DBMS_STATS.GATHER_SYSTEM_STATS(
    gathering_mode => 'NOWORKLOAD',
    interval      => 60
  );