印刻数据库

外观

Oracle 统计信息管理 ​

文档目的 ​

本文档详细介绍 Oracle 数据库统计信息的管理、收集和优化方法，帮助数据库管理员理解统计信息的重要性，掌握统计信息的收集策略，提高查询性能和执行计划质量，确保数据库系统的高效运行。

统计信息收集方法 ​

自动统计信息收集 ​

  • 自动维护任务：
    • Oracle 11g+ 自动维护窗口
    • 默认在每晚和周末运行
    • 收集对象：缺少统计信息或统计信息过时的对象
  • 配置自动收集：
    sql
    -- 检查自动统计信息收集任务状态
SELECT client_name, status FROM dba_autotask_client;

-- 启用/禁用自动统计信息收集
EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection');
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection');

手动统计信息收集 ​

  • 使用 DBMS_STATS 包：
    sql
    -- 收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE,
  degree => 4
);

-- 收集模式统计信息
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
  ownname => 'SCHEMA_NAME',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE,
  degree => 4
);

-- 收集数据库统计信息
EXEC DBMS_STATS.GATHER_DATABASE_STATS(
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE,
  degree => 4
);

并行收集 ​

  • 设置并行度：
    sql
    -- 在收集时指定并行度
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME',
  degree => 8
);

-- 设置系统默认并行度
ALTER SYSTEM SET parallel_degree_policy = 'AUTO';

增量统计信息收集 ​

  • 适用于分区表：
    sql
    -- 启用增量统计信息收集
EXEC DBMS_STATS.SET_TABLE_PREFS(
  'SCHEMA_NAME',
  'TABLE_NAME',
  'INCREMENTAL',
  'TRUE'
);

-- 收集分区表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME',
  incremental => TRUE
);

统计信息管理策略 ​

统计信息偏好设置 ​

  • 设置表级偏好：
    sql
    -- 设置表级统计信息偏好
EXEC DBMS_STATS.SET_TABLE_PREFS(
  'SCHEMA_NAME',
  'TABLE_NAME',
  'ESTIMATE_PERCENT',
  'DBMS_STATS.AUTO_SAMPLE_SIZE'
);

-- 设置直方图生成策略
EXEC DBMS_STATS.SET_TABLE_PREFS(
  'SCHEMA_NAME',
  'TABLE_NAME',
  'METHOD_OPT',
  'FOR ALL COLUMNS SIZE AUTO'
);

统计信息锁定 ​

  • 锁定统计信息：
    sql
    -- 锁定表统计信息
EXEC DBMS_STATS.LOCK_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME'
);

-- 解锁表统计信息
EXEC DBMS_STATS.UNLOCK_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME'
);

统计信息导出和导入 ​

  • 导出统计信息：
    sql
    -- 创建统计信息表
EXEC DBMS_STATS.CREATE_STAT_TABLE(
  ownname => 'SCHEMA_NAME',
  stat_table => 'STATS_TABLE'
);

-- 导出表统计信息
EXEC DBMS_STATS.EXPORT_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME',
  stat_table => 'STATS_TABLE'
);

-- 导入表统计信息
EXEC DBMS_STATS.IMPORT_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME',
  stat_table => 'STATS_TABLE'
);

统计信息历史管理 ​

  • 查看统计信息历史：
    sql
    -- 查看表统计信息历史
SELECT * FROM dba_tab_stats_history
WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
ORDER BY snapshot_id DESC;

-- 恢复统计信息到特定时间点
EXEC DBMS_STATS.RESTORE_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME',
  as_of_timestamp => SYSTIMESTAMP - INTERVAL '1' DAY
);

统计信息监控 ​

统计信息状态检查 ​

  • 检查表统计信息状态：
    sql
    -- 检查表统计信息
SELECT owner, table_name, num_rows, blocks, last_analyzed
FROM dba_tables
WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME';

-- 检查索引统计信息
SELECT owner, index_name, blevel, leaf_blocks, distinct_keys, last_analyzed
FROM dba_indexes
WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME';

-- 检查列统计信息
SELECT owner, table_name, column_name, num_distinct, density, last_analyzed
FROM dba_tab_col_statistics
WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME';

统计信息过期检查 ​

  • 识别需要收集统计信息的对象：
    sql
    -- 查找缺少统计信息的表
SELECT owner, table_name
FROM dba_tables
WHERE num_rows IS NULL AND owner NOT IN ('SYS', 'SYSTEM');

-- 查找统计信息过时的表（超过30天）
SELECT owner, table_name, last_analyzed
FROM dba_tables
WHERE last_analyzed < SYSDATE - 30
AND owner NOT IN ('SYS', 'SYSTEM');

统计信息质量检查 ​

  • 检查直方图：
    sql
    -- 检查表的直方图信息
SELECT owner, table_name, column_name, histogram
FROM dba_tab_col_statistics
WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME';

-- 检查直方图详细信息
SELECT * FROM dba_tab_histograms
WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';

统计信息优化 ​

直方图优化 ​

  • 直方图类型：
    • 频率直方图：适用于少量不同值
    • 高度均衡直方图：适用于大量不同值
    • 混合直方图：结合前两种类型
  • 直方图设置：
    sql
    -- 为特定列创建直方图
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME',
  method_opt => 'FOR COLUMNS SIZE 254 COLUMN_NAME'
);

采样率优化 ​

  • 采样率设置：
    sql
    -- 使用不同的采样率
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME',
  estimate_percent => 10
);

-- 使用自动采样率
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
);

并行度优化 ​

  • 并行度选择：
    • 小表：低并行度或串行
    • 大表：高并行度
    • 考虑系统负载
  • 并行度设置：
    sql
    -- 设置并行度为CPU核心数的一半
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'SCHEMA_NAME',
  tabname => 'TABLE_NAME',
  degree => 4
);

系统统计信息 ​

  • 收集系统统计信息：
    sql
    -- 收集系统统计信息（工作量统计）
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(
  gathering_mode => 'INTERVAL',
  interval => 30,
  stattab => 'SYS_STATS',
  statid => 'SYSTEM_STATS'
);

-- 收集系统统计信息（现在）
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'NOWORKLOAD');

统计信息维护最佳实践 ​

日常维护 ​

  • 监控统计信息状态：定期检查统计信息状态
  • 设置合理的自动收集窗口：根据系统负载调整
  • 处理例外情况：对特殊对象手动收集统计信息

大规模表维护 ​

  • 分区表策略：
    • 使用增量统计信息收集
    • 只收集变化的分区
  • 大表处理：
    • 使用并行收集
    • 选择合适的采样率
    • 在维护窗口进行

应用变更维护 ​

  • 架构变更后：
    • 收集受影响对象的统计信息
    • 验证执行计划
  • 数据加载后：
    • 收集加载表的统计信息
    • 考虑使用 NO_INVALIDATE 选项

性能问题排查 ​

  • 执行计划问题：
    • 检查统计信息是否准确
    • 重新收集统计信息
    • 使用 SQL 调优顾问
  • 统计信息相关的性能问题：
    • 执行计划抖动
    • 全表扫描过多
    • 索引未被使用

常见问题（FAQ） ​

Q1: 如何确定何时需要收集统计信息？ ​

A1: 确定需要收集统计信息的时机：

  • 数据量变化：数据量变化超过 10-15%
  • 架构变更：表结构变更后
  • 执行计划问题：执行计划不理想
  • 定期维护：按照维护计划定期收集
  • 自动检测：依赖 Oracle 的自动统计信息收集

Q2: 如何选择合适的采样率？ ​

A2: 选择合适的采样率的方法：

  • 使用自动采样：DBMS_STATS.AUTO_SAMPLE_SIZE（推荐）
  • 小表：100% 采样
  • 大表：1-5% 采样
  • 特殊情况：
    • 数据分布不均匀：增加采样率
    • 时间紧迫：减少采样率

Q3: 如何处理分区表的统计信息？ ​

A3: 处理分区表统计信息的方法：

  • 使用增量统计信息收集：只收集变化的分区
  • 收集全局统计信息：确保全局统计信息准确
  • 分区级收集：对特定分区单独收集
  • 并行收集：提高大分区表的收集效率

Q4: 如何避免统计信息收集对系统性能的影响？ ​

A4: 避免统计信息收集影响系统性能的方法：

  • 在维护窗口进行：利用自动维护窗口
  • 使用并行度：合理设置并行度，避免系统过载
  • 限制资源使用：使用资源管理器限制收集任务的资源使用
  • 增量收集：减少需要收集的对象数量
  • 监控系统负载：在系统负载低时进行

Q5: 如何处理执行计划抖动问题？ ​

A5: 处理执行计划抖动的方法：

  • 锁定统计信息：对关键表锁定统计信息
  • 使用基线：创建 SQL 计划基线
  • 稳定统计信息：定期收集统计信息，避免统计信息突然变化
  • 使用提示：在必要时使用 SQL 提示
  • 调整优化器参数：如 optimizer_dynamic_sampling

Q6: 如何验证统计信息的准确性？ ​

A6: 验证统计信息准确性的方法：

  • 与实际数据比较：
    sql
    -- 比较统计信息与实际行数
SELECT 
  (SELECT num_rows FROM dba_tables WHERE owner = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME') AS stats_rows,
  (SELECT COUNT(*) FROM SCHEMA_NAME.TABLE_NAME) AS actual_rows
FROM dual;
  • 检查直方图：确保直方图反映真实的数据分布
  • 执行计划验证：检查执行计划是否合理

Q7: 如何使用 DBMS_STATS 包的高级功能？ ​

A7: 使用 DBMS_STATS 包高级功能的方法：

  • 设置首选项：
    sql
    -- 设置全局首选项
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_STAT_EXTENSIONS', 'ON');

-- 设置模式级首选项
EXEC DBMS_STATS.SET_SCHEMA_PREFS('SCHEMA_NAME', 'ESTIMATE_PERCENT', '10');
  • 导出/导入统计信息：在环境间迁移统计信息
  • 恢复统计信息：从历史统计信息中恢复
  • 删除统计信息：
    sql
    -- 删除表统计信息
EXEC DBMS_STATS.DELETE_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

Q8: 如何优化系统统计信息？ ​

A8: 优化系统统计信息的方法：

  • 收集工作负载统计信息：更准确地反映系统性能
  • 定期更新：当硬件或系统配置变更时
  • 比较不同时段的统计信息：了解系统性能变化
  • 使用 EXECUTE_CATALOG_ROLE 权限：确保有足够的权限收集系统统计信息
  • 监控系统统计信息：
    sql
    -- 查看系统统计信息
SELECT * FROM dba_system_stats;