Oracle 数据库对象统计信息管理

统计信息的重要性

执行计划生成

统计信息是Oracle优化器生成执行计划的重要依据。优化器通过统计信息了解表的大小、索引的分布情况、列值的唯一性等信息，从而选择最优的执行计划。

性能优化

准确的统计信息可以帮助优化器做出正确的决策，避免执行效率低下的计划，如全表扫描替代索引扫描、嵌套循环连接替代哈希连接等。

资源利用

合理的执行计划可以减少CPU、内存和I/O资源的消耗，提高系统整体性能，减少响应时间。

统计信息类型

表统计信息

基本统计信息

行数：表中的记录数量
块数：表占用的数据块数量
平均行长度：表中记录的平均长度
分区统计信息：分区表各分区的统计信息

示例

sql

-- 查看表统计信息
SELECT table_name, num_rows, blocks, avg_row_len, last_analyzed
FROM dba_tables
WHERE owner = 'SCOTT' AND table_name = 'EMP';

索引统计信息

基本统计信息

叶子块数：索引叶子节点的数量
高度：索引的高度（B树层数）
不同键值数：索引中不同键值的数量
聚簇因子：索引键值与表中数据物理存储顺序的相关性

示例

sql

-- 查看索引统计信息
SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor, last_analyzed
FROM dba_indexes
WHERE owner = 'SCOTT' AND table_name = 'EMP';

列统计信息

基本统计信息

不同值数：列中不同值的数量
空值数：列中空值的数量
数据分布：列值的分布情况（直方图）

示例

sql

-- 查看列统计信息
SELECT column_name, num_distinct, num_nulls, density, last_analyzed
FROM dba_tab_col_statistics
WHERE owner = 'SCOTT' AND table_name = 'EMP';

-- 查看直方图信息
SELECT column_name, histogram, num_buckets
FROM dba_tab_col_statistics
WHERE owner = 'SCOTT' AND table_name = 'EMP';

统计信息收集方法

手动收集

使用DBMS_STATS包

sql

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

-- 收集索引统计信息
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(
    ownname => 'SCOTT',
    indname => 'EMP_IDX',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    degree => 4
  );
END;
/

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

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

参数说明

estimate_percent：采样百分比，DBMS_STATS.AUTO_SAMPLE_SIZE表示自动确定
method_opt：直方图收集选项，'FOR ALL COLUMNS SIZE AUTO'表示自动为需要的列创建直方图
cascade：是否同时收集相关索引的统计信息
degree：并行度
granularity：分区表统计信息收集粒度（'ALL', 'PARTITION', 'SUBPARTITION', 'GLOBAL'）

自动收集

自动收集任务

Oracle默认配置了自动收集统计信息的定时任务：

sql

-- 查看自动收集任务
SELECT * FROM dba_scheduler_jobs
WHERE job_name LIKE '%GATHER_STATS%';

-- 查看自动收集任务的调度
SELECT * FROM dba_scheduler_schedules
WHERE schedule_name LIKE '%GATHER_STATS%';

-- 查看自动收集任务的程序
SELECT * FROM dba_scheduler_programs
WHERE program_name LIKE '%GATHER_STATS%';

配置自动收集

sql

-- 启用自动收集统计信息任务
EXEC DBMS_SCHEDULER.ENABLE('SYS.GATHER_STATS_JOB');

-- 禁用自动收集统计信息任务
EXEC DBMS_SCHEDULER.DISABLE('SYS.GATHER_STATS_JOB');

-- 修改自动收集任务的调度
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE(
  'SYS.GATHER_STATS_SCHEDULE',
  'repeat_interval',
  'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0'
);

自定义自动收集

sql

-- 创建自定义统计信息收集任务
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'SCOTT.CUSTOM_GATHER_STATS',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''SCOTT'', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ''FOR ALL COLUMNS SIZE AUTO'', cascade => TRUE, degree => 4); END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY; BYDAY=SAT; BYHOUR=3; BYMINUTE=0; BYSECOND=0',
    enabled => TRUE
  );
END;
/

统计信息维护

统计信息状态检查

过期统计信息

sql

-- 查找过期的统计信息（超过7天未更新）
SELECT owner, table_name, last_analyzed
FROM dba_tables
WHERE last_analyzed < SYSDATE - 7
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY last_analyzed;

-- 查找最近更新的统计信息
SELECT owner, table_name, last_analyzed
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM')
ORDER BY last_analyzed DESC
FETCH FIRST 20 ROWS ONLY;

统计信息缺失

sql

-- 查找没有统计信息的表
SELECT owner, table_name
FROM dba_tables
WHERE num_rows IS NULL
  AND owner NOT IN ('SYS', 'SYSTEM')
  AND table_name NOT LIKE 'BIN$%';

-- 查找没有统计信息的索引
SELECT owner, index_name, table_name
FROM dba_indexes
WHERE blevel IS NULL
  AND owner NOT IN ('SYS', 'SYSTEM');

统计信息管理

锁定统计信息

sql

-- 锁定表统计信息
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT', 'EMP');

-- 锁定模式统计信息
EXEC DBMS_STATS.LOCK_SCHEMA_STATS('SCOTT');

-- 查看锁定状态
SELECT owner, table_name, stattype_locked
FROM dba_tab_statistics
WHERE owner = 'SCOTT' AND table_name = 'EMP';

解锁统计信息

sql

-- 解锁表统计信息
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('SCOTT', 'EMP');

-- 解锁模式统计信息
EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS('SCOTT');

删除统计信息

sql

-- 删除表统计信息
EXEC DBMS_STATS.DELETE_TABLE_STATS('SCOTT', 'EMP');

-- 删除索引统计信息
EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT', 'EMP_IDX');

-- 删除模式统计信息
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

导出和导入统计信息

sql

-- 导出表统计信息
EXEC DBMS_STATS.EXPORT_TABLE_STATS('SCOTT', 'EMP', stattab => 'STATS_TABLE', statid => 'EMP_STATS');

-- 导出模式统计信息
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('SCOTT', stattab => 'STATS_TABLE', statid => 'SCOTT_STATS');

-- 导入表统计信息
EXEC DBMS_STATS.IMPORT_TABLE_STATS('SCOTT', 'EMP', stattab => 'STATS_TABLE', statid => 'EMP_STATS');

-- 导入模式统计信息
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('SCOTT', stattab => 'STATS_TABLE', statid => 'SCOTT_STATS');

直方图管理

直方图类型

频率直方图

适用于列值数量较少且分布不均匀的情况
每个不同的值对应一个桶

高度平衡直方图

适用于列值数量较多的情况
将数据范围分成固定数量的桶（默认254个）

直方图创建

sql

-- 为指定列创建直方图
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    method_opt => 'FOR COLUMNS SIZE 100 SAL',
    cascade => TRUE
  );
END;
/

-- 为所有列自动创建直方图
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE
  );
END;
/

-- 为所有列创建固定数量的直方图
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    method_opt => 'FOR ALL COLUMNS SIZE 254',
    cascade => TRUE
  );
END;
/

直方图查看

sql

-- 查看直方图信息
SELECT column_name, histogram, num_buckets
FROM dba_tab_col_statistics
WHERE owner = 'SCOTT' AND table_name = 'EMP';

-- 查看直方图详细信息
SELECT column_name, endpoint_number, endpoint_value
FROM dba_tab_histograms
WHERE owner = 'SCOTT' AND table_name = 'EMP' AND column_name = 'SAL'
ORDER BY endpoint_number;

统计信息刷新策略

基于数据变更

增量收集

sql

-- 检查表的数据变更情况
SELECT table_name, inserts, updates, deletes, timestamp
FROM dba_tab_modifications
WHERE owner = 'SCOTT' AND table_name = 'EMP';

-- 清空修改计数
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

-- 增量收集统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE,
    degree => 4,
    incremental => TRUE
  );
END;
/

基于时间

定期收集策略

表大小	收集频率	采样率
小表（< 10,000行）	每周	100%
中表（10,000-1,000,000行）	每两周	10%
大表（> 1,000,000行）	每月	5%
超大表（> 10,000,000行）	每季度	1%

常见问题处理

统计信息不准确

症状

SQL执行计划不佳
查询性能下降
资源消耗异常

原因

统计信息过期
数据量发生显著变化
直方图信息缺失或不准确

解决方案

sql

-- 重新收集统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    estimate_percent => 100, -- 全量采样
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE,
    degree => 4
  );
END;
/

直方图问题

症状

对于有倾斜数据分布的列，执行计划选择不当
范围查询性能不佳

原因

没有为倾斜列创建直方图
直方图桶数不足
直方图过期

解决方案

sql

-- 为倾斜列创建直方图
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'EMP',
    method_opt => 'FOR COLUMNS SIZE 254 SAL', -- 为SAL列创建254个桶的直方图
    cascade => TRUE
  );
END;
/

分区表统计信息

症状

分区表查询性能下降
分区裁剪不生效

原因

全局统计信息与分区统计信息不一致
部分分区统计信息缺失

解决方案

sql

-- 收集分区表的全局和分区统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'SALES',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE,
    degree => 4,
    granularity => 'ALL' -- 收集全局和所有分区的统计信息
  );
END;
/

-- 只收集特定分区的统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'SALES',
    partition_name => 'SALES_2026_Q1',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE,
    degree => 4
  );
END;
/

版本差异

Oracle 11g

统计信息管理

支持基本的DBMS_STATS功能
自动收集任务默认启用
直方图功能相对简单

示例

sql

-- Oracle 11g 收集统计信息
BEGIN
  DBMS_STATS.GATHER_DATABASE_STATS(
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE
  );
END;
/

Oracle 12c及以上

增强功能

支持多租户环境的统计信息管理
增强了增量统计信息收集
支持自动直方图调整
新增统计信息保留策略

示例

sql

-- Oracle 12c+ 多租户环境统计信息收集
ALTER SESSION SET CONTAINER = PDB1;
BEGIN
  DBMS_STATS.GATHER_DATABASE_STATS(
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE,
    degree => 8
  );
END;
/

-- 设置统计信息保留策略
EXEC DBMS_STATS.SET_STATS_RETENTION(30); -- 保留30天

最佳实践

统计信息管理策略

日常维护

监控统计信息状态：定期检查过期和缺失的统计信息
自动收集配置：根据系统特点配置合适的自动收集策略
手动收集补充：对于关键表和频繁变更的表，在业务低峰期手动收集
统计信息备份：定期导出统计信息，以便在需要时恢复

性能优化

合理设置采样率：根据表大小选择合适的采样率，平衡准确性和收集时间
优化直方图：只为数据分布倾斜的列创建直方图
并行收集：使用并行度加速统计信息收集过程
增量收集：对于大表使用增量收集，减少收集时间

监控和告警

监控脚本

sql

-- 监控统计信息状态的脚本
SELECT owner, 
       COUNT(*) AS total_tables,
       COUNT(CASE WHEN last_analyzed > SYSDATE - 7 THEN 1 END) AS fresh_stats,
       COUNT(CASE WHEN last_analyzed < SYSDATE - 7 THEN 1 END) AS stale_stats,
       COUNT(CASE WHEN last_analyzed IS NULL THEN 1 END) AS no_stats
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY owner
ORDER BY total_tables DESC;

告警机制

设置统计信息过期告警
监控统计信息收集任务的执行状态
建立性能基线，当查询性能下降时检查统计信息

常见问题（FAQ）

Q1: 统计信息收集会影响数据库性能吗？

A1: 统计信息收集会对数据库性能产生一定影响，主要体现在：

CPU消耗：收集过程需要计算统计信息，会消耗CPU资源
I/O操作：需要读取数据块来计算统计信息，会产生I/O操作
锁竞争：收集过程可能会对表加锁，影响并发操作

缓解措施：

在业务低峰期执行统计信息收集
使用并行度加速收集过程，减少收集时间
对于大表使用增量收集或抽样收集
避免在系统负载高时执行全库统计信息收集

Q2: 如何确定统计信息是否需要更新？

A2: 可以通过以下方式判断统计信息是否需要更新：

数据变更量：当表的数据变更超过10-20%时，建议更新统计信息
时间间隔：根据表的大小和重要性，设置定期更新的时间间隔
执行计划：当SQL执行计划明显变差时，检查统计信息状态
系统监控：使用监控脚本定期检查统计信息的新鲜度

示例判断标准：

小表（< 10,000行）：每周更新
中表（10,000-1,000,000行）：每两周更新
大表（> 1,000,000行）：每月更新
关键业务表：在数据批量变更后立即更新

Q3: 直方图有什么作用？什么时候需要创建直方图？

A3: 直方图的作用：

捕获数据分布：直方图可以捕获列值的分布情况，特别是数据倾斜的情况
优化执行计划：对于有数据倾斜的列，直方图可以帮助优化器选择更准确的执行计划
提高查询性能：特别是对于范围查询和不等式查询

需要创建直方图的情况：

数据分布倾斜：列中大部分值集中在少数几个值上
频繁用于WHERE条件：列经常出现在查询的WHERE子句中
范围查询：列经常用于范围查询（如BETWEEN、>、<等）
连接条件：列经常用作表连接的条件

创建建议：

使用METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'让Oracle自动决定是否创建直方图
对于已知数据倾斜的列，可以显式指定创建直方图：METHOD_OPT => 'FOR COLUMNS SIZE 254 COLUMN_NAME'

Q4: 分区表的统计信息应该如何管理？

A4: 分区表统计信息管理的建议：

收集级别：
- 全局统计信息：描述整个表的统计信息
- 分区统计信息：描述各个分区的统计信息
- 子分区统计信息：描述各个子分区的统计信息

收集方法：

sql

-- 收集所有级别统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT',
    tabname => 'SALES',
    granularity => 'ALL', -- 收集全局、分区和子分区统计信息
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade => TRUE
  );
END;
/

维护策略：
- 对于频繁变更的分区，单独收集该分区的统计信息
- 定期重新收集全局统计信息，确保全局和分区统计信息的一致性
- 对于增量加载的分区表，使用增量统计信息收集
注意事项：
- 确保分区统计信息与全局统计信息一致
- 避免只收集部分分区的统计信息而忽略全局统计信息
- 对于大量小分区的表，考虑使用复合分区统计信息

Q5: 如何处理统计信息收集过程中的错误？

A5: 统计信息收集过程中可能遇到的错误及处理方法：

权限错误：
- 症状：ORA-00942: table or view does not exist或权限不足错误
- 处理：确保执行用户具有ANALYZE ANY和SELECT ANY DICTIONARY权限
资源不足：
- 症状：ORA-04031: unable to allocate shared memory或ORA-01652: unable to extend temp segment
- 处理：
  - 增加PGA和SGA内存
  - 增加临时表空间大小
  - 减少并行度
  - 分批次收集统计信息
锁冲突：
- 症状：ORA-00054: resource busy and acquire with NOWAIT specified
- 处理：
  - 在业务低峰期执行收集
  - 使用DBMS_STATS的NO_INVALIDATE参数
  - 对于Oracle 11g+，使用DBMS_STATS的LOCK_TABLE_STATS避免并发收集
超时：
- 症状：收集过程耗时过长，超出预期
- 处理：
  - 减少采样率
  - 增加并行度
  - 使用增量收集
  - 将大表的收集拆分为多个小任务
收集失败：
- 症状：部分表的统计信息收集失败
- 处理：
  - 检查错误日志，确定失败原因
  - 针对特定表单独收集
  - 排除有问题的表，先收集其他表的统计信息
  - 必要时重启统计信息收集任务

Oracle 数据库对象统计信息管理 ​

统计信息的重要性 ​

执行计划生成 ​

性能优化 ​

资源利用 ​

统计信息类型 ​

表统计信息 ​

基本统计信息 ​

示例 ​

索引统计信息 ​

基本统计信息 ​

示例 ​

列统计信息 ​

基本统计信息 ​

示例 ​

统计信息收集方法 ​

手动收集 ​

使用DBMS_STATS包 ​

参数说明 ​

自动收集 ​

自动收集任务 ​

配置自动收集 ​

自定义自动收集 ​

统计信息维护 ​

统计信息状态检查 ​

过期统计信息 ​

统计信息缺失 ​

统计信息管理 ​

锁定统计信息 ​

解锁统计信息 ​

删除统计信息 ​

导出和导入统计信息 ​

直方图管理 ​

直方图类型 ​

频率直方图 ​

高度平衡直方图 ​

直方图创建 ​

直方图查看 ​

统计信息刷新策略 ​

基于数据变更 ​

增量收集 ​

基于时间 ​

定期收集策略 ​

常见问题处理 ​

统计信息不准确 ​

症状 ​

原因 ​

解决方案 ​

直方图问题 ​

症状 ​

原因 ​

解决方案 ​

分区表统计信息 ​

症状 ​

原因 ​

解决方案 ​

版本差异 ​

Oracle 11g ​

统计信息管理 ​

示例 ​

Oracle 12c及以上 ​

增强功能 ​

示例 ​

最佳实践 ​

统计信息管理策略 ​

日常维护 ​

性能优化 ​

监控和告警 ​

监控脚本 ​

告警机制 ​

常见问题（FAQ） ​

Q1: 统计信息收集会影响数据库性能吗？ ​

Q2: 如何确定统计信息是否需要更新？ ​

Q3: 直方图有什么作用？什么时候需要创建直方图？ ​

Q4: 分区表的统计信息应该如何管理？ ​

Q5: 如何处理统计信息收集过程中的错误？ ​

Oracle 数据库对象统计信息管理

统计信息的重要性

执行计划生成

性能优化

资源利用

统计信息类型

表统计信息

基本统计信息

示例

索引统计信息

基本统计信息

示例

列统计信息

基本统计信息

示例

统计信息收集方法

手动收集

使用DBMS_STATS包

参数说明

自动收集

自动收集任务

配置自动收集

自定义自动收集

统计信息维护

统计信息状态检查

过期统计信息

统计信息缺失

统计信息管理

锁定统计信息

解锁统计信息

删除统计信息

导出和导入统计信息

直方图管理

直方图类型

频率直方图

高度平衡直方图

直方图创建

直方图查看

统计信息刷新策略

基于数据变更

增量收集

基于时间

定期收集策略

常见问题处理

统计信息不准确

症状

原因

解决方案

直方图问题

症状

原因

解决方案

分区表统计信息

症状

原因

解决方案

版本差异

Oracle 11g

统计信息管理

示例

Oracle 12c及以上

增强功能

示例

最佳实践

统计信息管理策略

日常维护

性能优化

监控和告警

监控脚本

告警机制

常见问题（FAQ）

Q1: 统计信息收集会影响数据库性能吗？

Q2: 如何确定统计信息是否需要更新？

Q3: 直方图有什么作用？什么时候需要创建直方图？

Q4: 分区表的统计信息应该如何管理？

Q5: 如何处理统计信息收集过程中的错误？