数据库教程FGMT10‑Oracle性能优化之统计信息管理与维护

数据库教程FGMT10‑Oracle性能优化之统计信息管理与维护

前言

在Oracle数据库CBO基于成本的优化器体系当中，统计信息是优化器生成合理执行计划的核心输入源。统计信息失真、缺失、过期是线上SQL性能抖动、执行计划漂移最主要诱因之一。大量生产故障根因均来自统计信息维护不当：大表批量DML之后统计信息未刷新、直方图丢失、自动收集任务窗口不合理、参考数据表统计信息被自动任务覆盖等。风哥教程本文围绕统计信息基础概念、各类统计信息收集手段、自动维护任务、统计信息删除重建、锁定解锁、备份迁移、pending发布机制、动态采样等核心技术展开完整讲解。风哥 itpux‑com

本套风哥教程面向DBA、数据库运维工程师、后端开发人员，全部实验标准化环境配置：主机名称fgedu‑net‑cn，硬件规格64G物理内存、8颗CPU；数据库实例名fgedudb，数据库名fgedudb，测试业务用户名fgedu，文件根目录统一为/fgedudb，整套实验基于Oracle19c企业版完成。风哥教程本文分为前言大纲介绍、核心理论知识、实战操作演练、总结四大模块；实战章节包含大量可直接复制运行的SQL脚本，读者可以在测试环境完整复现全部实验现象，掌握生产环境统计信息全套运维操作与风险规避要点。网上搜索风哥教程可以学习全套数据库教程

内容大纲

1. Oracle优化器统计信息基础概念与数据字典查看手段
2. Analyze与DBMS_STATS包的差异对比
3. DBMS_STATS包各类收集过程：数据库级、用户Schema级、表级、索引级、字典对象统计信息
4. 系统硬件统计信息收集，适配64G内存8CPU硬件环境
5. Oracle自动统计信息收集任务原理、参数配置、窗口调整
6. 统计信息删除、重建、锁定、解锁操作与生产适用场景
7. 统计信息备份、导出、导入、跨库迁移完整流程
8. Pending待发布统计信息管理发布机制，规避统计变更业务风险
9. 动态采样（动态统计信息）原理、参数配置、hint使用
10. 生产环境统计信息运维规范、风险点、故障排查思路

一、核心理论知识

本章节为本套风哥教程理论基础，只有理解统计信息底层原理，才能够看懂执行计划基数估算偏差，处理线上因为统计信息引发的SQL性能故障。风哥教程 113257174

1.1 Oracle统计信息基础概念

Oracle CBO优化器依靠统计信息计算访问路径、表连接方式的COST成本，从中选出成本最低的执行计划。统计信息存储在Oracle数据字典内部，分为多类：表统计信息、列统计信息（含直方图）、索引统计信息、系统统计信息、字典对象统计信息。

1. 表统计信息：表总行数、数据块数量、空块数量、行平均长度；优化器用来计算全表扫描IO成本，估算返回行数。
2. 列统计信息：字段不同值数量NDV、字段最大值最小值、直方图。直方图专门用来记录字段数据倾斜分布，当列数据分布不均匀，直方图是CBO做出正确基数估算的关键；缺少直方图会直接造成预估行数E‑ROWS严重偏离实际A‑ROWS，生成劣质执行计划。
3. 索引统计信息：索引叶子块数量、索引高度、聚簇因子clustering_factor；聚簇因子代表索引键值与表数据存储位置的重合程度，直接影响索引回表成本计算。
4. 系统统计信息：采集主机CPU运算开销、单块读、多块读IO代价，将硬件特性带入成本计算；本实验主机fgedu‑net‑cn为64G内存8CPU，系统统计信息采集本机真实硬件指标。
5. 字典统计信息：SYS、SYSTEM等系统数据字典对象的统计，影响数据库内部递归SQL执行效率。

失效（stale）统计信息：当表发生大量insert/update/delete，修改量超过阈值，数据库标记该对象stale_stats=YES，代表现有统计信息已经不能真实反映当前数据分布，需要重新收集。网上搜索风哥教程可以学习全套数据库教程

1.2 Analyze命令与DBMS_STATS包区别

Oracle存在两套收集统计信息的手段：传统analyze命令与dbms_stats系统包，二者能力存在巨大差异。

1. analyze命令：早期旧工具，不支持收集直方图、不支持分区表完整统计、无法收集系统统计信息；仅适合测试环境小表，生产环境不建议使用analyze做业务表统计信息收集。analyze可以用来验证表行链接、行迁移，这是它为数不多的保留使用场景。
2. DBMS_STATS包：Oracle官方推荐现代统计信息管理工具，支持表、分区表、索引、直方图、系统硬件统计、统计信息锁定、导出导入、pending待发布统计等全套能力，生产环境全部优先使用DBMS_STATS。

注意：analyze收集的统计信息，部分字段不会被CBO完整使用，生产业务表禁止用analyze替代dbms_stats。

1.3 DBMS_STATS各类收集粒度说明

DBMS_STATS提供不同粒度存储过程，适配不同运维场景：

1. gather_database_stats：收集全库所有对象统计，耗资源，一般仅新库初始化使用，业务运行数据库不建议频繁执行。
2. gather_schema_stats：收集指定Schema下全部表、索引统计，适合业务版本上线，大批量对象变更后使用。
3. gather_table_stats：单张表统计收集，包含列直方图，可同时收集索引统计；生产故障排查最频繁使用。
4. gather_index_stats：只收集索引统计，不处理表与列。
5. gather_dictionary_stats：收集SYS等系统字典对象统计，维护数据库内部递归SQL性能。
6. gather_system_stats：采集主机CPU、IO硬件系统统计信息。

关键参数解释（适配64G内存8CPU生产环境）

• estimate_percent：采样比例，DBMS_STATS.AUTO_SAMPLE_SIZE由Oracle自动选择最优采样，19c推荐优先使用。
• method_opt：控制直方图收集策略；FOR ALL COLUMNS SIZE AUTO自动识别倾斜列创建直方图，业务生产标准配置。
• degree：并行度，8CPU主机，业务收集建议设置degree=>4‑6，不能超过CPU核数。
• cascade：TRUE代表同步收集关联索引统计信息。
• no_invalidate：FALSE，收集完成直接使化相关游标，让新统计立刻生效；TRUE不会使化游标，旧游标继续沿用旧统计。
• granularity：针对分区表，AUTO自动处理分区、子分区、全局统计。

1.4 自动统计信息收集任务原理

Oracle自动维护任务AutoTask，在维护窗口执行auto optimizer stats collection任务，自动识别标记stale失效的对象，优先对失效对象收集统计信息。
默认维护窗口为每周七天夜间时间段。并不是全库全部重收集，优先处理无统计、统计过期失效对象。
生产环境常见问题：业务批量数据变更发生在白天，夜间窗口才会刷新统计，会出现白天业务运行统计信息已经失效；部分参考基础数据表，不希望自动任务覆盖已经调优完成的直方图，需要执行统计锁定。

1.5 统计信息锁定、删除、备份迁移、Pending发布、动态采样

风哥数据库教程 itpux‑com

1. 统计信息锁定lock：锁定对象统计，无论自动任务还是手工gather，都不能覆盖已锁定统计。适合基础码表、已经人工调优直方图，防止自动任务破坏调优成果。
2. 统计信息删除：删除对象全部统计；删除之后对象无统计，CBO依赖动态采样估算基数，一般用于测试环境，生产谨慎操作。
3. 统计信息备份迁移：dbms_stats支持导出统计信息存入普通中转表，expdp导出该表，可以把一套经过验证良好统计信息迁移到测试库、预发布库。
4. Pending待发布统计：收集统计信息不直接生效，存入pending区域；DBA手工验证业务SQL性能没有退化，再执行发布操作，把统计信息切换为正式使用，用于重大变更前风险隔离。
5. 动态采样（动态统计信息）：当对象缺失统计，或者统计可信度低，优化器在SQL硬解析阶段，实时扫描少量数据块，动态估算基数，弥补统计缺失；受初始化参数optimizer_dynamic_sampling控制，默认级别2。动态采样是补偿手段，不能替代完整统计信息收集。

风险点：动态采样采样块数有限，数据高度倾斜场景，动态采样估算依旧会出现巨大偏差。

二、实战操作演练

本套风哥教程全部实战操作，操作主机fgedu‑net‑cn，数据库fgedudb，业务用户fgedu，目录/fgedudb，硬件规格64G内存8CPU。

环境说明：操作系统登录oracle用户，设置环境变量指向fgedudb实例；大部分统计管理操作需要sysdba权限，测试业务对象使用fgedu用户。

2.1 操作系统与数据库环境校验

2.1.1操作系统层面检查（主机fgedu‑net‑cn）

登录操作系统oracle用户执行shell命令

#确认主机名称
hostname
#确认ORACLE_HOME路径全部指向/fgedudb
echo $ORACLE_HOME
#查看内存硬件，确认64G内存
free -h
#查看CPU，确认8CPU
lscpu

校验输出：hostname输出fgedu‑net‑cn，总内存64G，逻辑CPU数量8。

2.1.2 数据库关键初始化参数确认（64G内存8CPU规格）

登录sqlplus / as sysdba查看相关参数

show parameter statistics_level;
show parameter optimizer_dynamic_sampling;
show parameter memory_target;
show parameter sga_target;
show parameter pga_aggregate_target;

适配64G内存主机spfile标准配置，内存分配48G留给Oracle，剩余留给操作系统。

alter system set memory_max_target=48G scope=spfile;
alter system set memory_target=48G scope=spfile;
alter system set statistics_level=TYPICAL scope=spfile;
alter system set optimizer_dynamic_sampling=2 scope=spfile;

statistics_level=TYPICAL是生产标准，开启表监控，自动统计任务依赖该参数；设置为BASIC会关闭表修改监控，自动统计无法识别stale对象。

2.1.3 创建测试用户fgedu，授予统计管理相关权限

create user fgedu identified by fgedudb default tablespace users temporary tablespace temp;
grant connect,resource to fgedu;
grant select_catalog_role to fgedu;
grant analyze any to fgedu;
grant execute on dbms_stats to fgedu;

2.2 构造测试数据表，制造数据倾斜场景

切换fgedu用户，创建测试表，构造字段数据倾斜，用于复现直方图缺失带来的基数估算错误。

conn fgedu/fgedudb@fgedudb

create table t_stat_test(id number,status number,info varchar2(200));

--插入倾斜测试数据，status=1 100000行，status其他值各10行
declare
begin
  for i in 1..100000 loop
    insert into t_stat_test values(i,1,'test_info_'||i);
  end loop;
  for j in 2..100 loop
    for k in 1..10 loop
      insert into t_stat_test values(100000+j*10+k,j,'otherdata'||j);
    end loop;
  end loop;
  commit;
end;
/

create index idx_tstat_status on t_stat_test(status);

2.3 使用数据字典查看统计信息实战

2.3.1 检查表、列、索引统计信息

conn / as sysdba
--查看表统计信息，行数、块、最后收集时间、是否失效
select owner,table_name,num_rows,blocks,last_analyzed,stale_stats
from dba_tab_statistics
where owner='FGEDU' and table_name='T_STAT_TEST';

--查看列统计，NDV最大最小值
select owner,table_name,column_name,num_distinct,low_value,high_value
from dba_tab_col_statistics
where owner='FGEDU' and table_name='T_STAT_TEST';

--查看直方图信息
select table_name,column_name,endpoint_number,endpoint_value
from dba_tab_histograms
where owner='FGEDU' and table_name='T_STAT_TEST';

--查看索引统计信息，重点看聚簇因子clustering_factor
select owner,index_name,leaf_blocks,clustering_factor,last_analyzed
from dba_ind_statistics
where owner='FGEDU' and index_name='IDX_TSTAT_STATUS';

2.3.2 查询哪些表统计信息标记为stale失效

dba_tab_modifications记录表insert/update/delete变更量，数据库以此判断stale状态。

SELECT
  s.owner,
  s.table_name,
  s.last_analyzed,
  s.stale_stats,
  m.inserts,m.updates,m.deletes,
  ROUND((m.inserts+m.updates+m.deletes)/NULLIF(s.num_rows,0)*100,2) pct_changed
FROM dba_tab_statistics s
LEFT JOIN dba_tab_modifications m
ON s.owner=m.table_owner AND s.table_name=m.table_name AND m.partition_name IS NULL
WHERE s.owner='FGEDU'
ORDER BY pct_changed DESC NULLS LAST;

2.4 统计信息收集实操，analyze与dbms_stats对比演示

2.4.1 analyze命令演示（仅用于测试，生产业务表禁止使用）

conn fgedu/fgedudb@fgedudb
analyze table t_stat_test compute statistics;
--analyze不会生成直方图，查询dba_tab_histograms无记录
select * from dba_tab_histograms where owner='FGEDU' and table_name='T_STAT_TEST';

可以观察到analyze收集完成，不会生成直方图，倾斜字段无法拿到倾斜分布数据，CBO基数估算会出错。

2.4.2 DBMS_STATS单表收集，生产标准参数（8CPU主机degree=4）

conn / as sysdba
exec dbms_stats.gather_table_stats(
ownname=>'FGEDU',
tabname=>'T_STAT_TEST',
estimate_percent=>dbms_stats.auto_sample_size,
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
degree=>4,
cascade=>true,
no_invalidate=>false
);

执行完毕，再次查询dba_tab_histograms，status字段会自动生成直方图。

2.4.3 Schema级别统计收集

exec dbms_stats.gather_schema_stats(
ownname=>'FGEDU',
estimate_percent=>dbms_stats.auto_sample_size,
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
degree=>4,
cascade=>true
);

2.4.4 只收集索引统计

exec dbms_stats.gather_index_stats(ownname=>'FGEDU',indname=>'IDX_TSTAT_STATUS',degree=>4);

2.4.5 收集系统硬件统计信息，主机fgedu‑net‑cn（64G/8CPU）

系统统计分noworkload与workload模式，workload模式需要业务运行一段时间采集真实IO、CPU负载。

--开始采集workload系统统计，业务运行一段时间
exec dbms_stats.gather_system_stats(gathering_mode=>'START');

--业务运行一段时间之后停止采集并保存
exec dbms_stats.gather_system_stats(gathering_mode=>'STOP');

--查看已经采集的系统统计
select * from sys.aux_stats$;

2.5 自动统计信息任务运维实操

2.5.1 查看自动统计任务状态

SELECT client_name,status,group_id
FROM dba_autotask_client
WHERE client_name='auto optimizer stats collection';

2.5.2 查看自动任务维护窗口时间

select window_name,repeat_interval,enabled,resource_plan
from dba_scheduler_windows;

2.5.3 启用、关闭自动统计收集任务（生产谨慎关闭）

--关闭自动统计任务
begin
dbms_autotask_admin.disable(client_name=>'auto optimizer stats collection');
end;
/

--开启自动统计任务
begin
dbms_autotask_admin.enable(client_name=>'auto optimizer stats collection');
end;
/

生产环境不建议直接关闭自动统计任务；对于个别不需要自动刷新的表，优先使用LOCK_TABLE_STATS锁定单表统计，而不是全局关闭任务。

2.6 统计信息删除、锁定、解锁完整实操

2.6.1 删除表统计信息

⚠生产环境谨慎执行，删除之后对象无统计，依赖动态采样估算。

exec dbms_stats.delete_table_stats(ownname=>'FGEDU',tabname=>'T_STAT_TEST');

2.6.2 锁定表统计信息

锁定之后自动任务、手工gather_table_stats均不能覆盖该表统计，适合码表、已经调优好直方图的对象。

begin
dbms_stats.lock_table_stats(ownname=>'FGEDU',tabname=>'T_STAT_TEST');
end;
/

锁定之后再执行gather_table_stats会报ORA‑20005对象统计信息被锁定。

2.6.3 解锁表统计信息

begin
dbms_stats.unlock_table_stats(ownname=>'FGEDU',tabname=>'T_STAT_TEST');
end;
/

2.6.4 查询库内哪些表统计处于锁定状态

select owner,table_name,stattype_locked
from dba_tab_statistics
where stattype_locked is not null;

2.7 统计信息备份导出、导入跨库迁移实操

原理：创建一张普通中转表stattab，把统计信息导出存入这张表，expdp把这张表导出dmp文件传输到目标库impdp导入，再执行import把统计信息写回数据字典。风哥数据库教程 itpux‑com

2.7.1 创建统计信息中转存储表

conn / as sysdba
exec dbms_stats.create_stattab(stattab=>'STATS_STG_TAB',ownname=>'FGEDU',tblspace=>'USERS');

2.7.2 将单张表统计导出到中转表

begin
dbms_stats.export_table_stats(
ownname=>'FGEDU',
tabname=>'T_STAT_TEST',
stattab=>'STATS_STG_TAB',
statown=>'FGEDU'
);
end;
/

之后使用expdp导出FGEDU.STATS_STG_TAB这张普通表，dmp文件路径指定为/fgedudb/dump。
expdp简要示例：

expdp fgedu/fgedudb@fgedudb directory=DATA_PUMP_DIR dumpfile=stats_stg.dmp tables=STATS_STG_TAB logfile=stats_stg.log

dmp文件拷贝到目标数据库服务器，impdp导入该表。

2.7.3 在目标库，从中转表导入统计信息

begin
dbms_stats.import_table_stats(
ownname=>'FGEDU',
tabname=>'T_STAT_TEST',
stattab=>'STATS_STG_TAB',
statown=>'FGEDU'
);
end;
/

2.8 Pending待发布统计信息实操（变更风险隔离）

pending模式收集统计不会立刻生效，先放在pending区域；DBA验证业务SQL性能没有退化，再执行发布，切换为正式统计。

2.8.1 开启pending统计模式

exec dbms_stats.set_global_prefs('PUBLISH','FALSE');

此时后续所有gather收集的统计，全部存入pending待发布区域，不会直接启用。

2.8.2 收集测试表统计

exec dbms_stats.gather_table_stats(
ownname=>'FGEDU',
tabname=>'T_STAT_TEST',
estimate_percent=>dbms_stats.auto_sample_size,
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
degree=>4,
cascade=>true
);

收集完成，查询正式dba_tab_statistics不会更新；查询pending视图查看待发布统计。

select table_name,num_rows,last_analyzed from dba_tab_pending_stats where owner='FGEDU';

2.8.3 验证业务SQL执行计划，确认性能无退化，执行发布

--发布单表pending统计，正式生效
begin
dbms_stats.publish_pending_stats(ownname=>'FGEDU',tabname=>'T_STAT_TEST');
end;
/

--关闭全局pending模式，恢复默认收集直接发布
exec dbms_stats.set_global_prefs('PUBLISH','TRUE');

2.9 动态采样（动态统计信息）实操

动态采样参数optimizer_dynamic_sampling，级别0关闭，默认2。

2.9.1 会话级别修改参数测试

alter session set optimizer_dynamic_sampling=0;

此时关闭动态采样，如果表没有统计，CBO完全没有额外补偿，基数估算偏差巨大。

2.9.2 SQL内部hint指定动态采样级别

explain plan for
select /*+ dynamic_sampling(t 2) */ * from fgedu.t_stat_test t where status=1;
select * from table(dbms_xplan.display());

2.9.3 查看执行计划里动态采样是否生效

执行计划Note部分会输出‑ dynamic statistics used: dynamic sampling (level=2)代表动态采样已启用。

2.10 生产故障排查完整操作流程

1. SQL出现执行计划异常，先通过dbms_xplan.display_cursor拿到真实执行计划，对比E‑ROWS预估行数与A‑ROWS实际行数；二者差距巨大优先怀疑统计信息问题。
2. 查询dba_tab_statistics查看last_analyzed、stale_stats；查询dba_tab_modifications确认表DML变更占比。
3. 检查直方图dba_tab_histograms，倾斜字段是否缺少直方图。
4. 检查表是否被lock锁定统计，查询stattype_locked字段。
5. 确认pending待发布统计是否存在未发布对象。
6. 优先使用dbms_stats重新收集统计，生产8CPU主机degree建议设置4；method_opt使用FOR ALL COLUMNS SIZE AUTO。
7. 重大变更优先使用pending待发布模式，验证业务性能之后再发布统计；
8. 统计变更之后观察业务SQL响应时间，保留回退手段：导出原有统计信息，一旦新统计引发性能问题，立刻import回退旧统计。

三、风哥针对本文总结

本套风哥教程完整覆盖Oracle优化器统计信息整套运维知识，从统计信息分类、analyze与dbms_stats差异、各类粒度统计收集操作、系统统计信息，到自动维护任务、统计信息锁定解锁、备份迁移、pending待发布机制、动态采样理论与全套实战命令。

1. 生产环境业务表禁止使用analyze收集统计，优先全套使用DBMS_STATS包；analyze仅保留用于检测行迁移、行链接场景。
2. 直方图是处理字段数据倾斜的关键，method_opt生产标准配置FOR ALL COLUMNS SIZE AUTO，自动识别倾斜列生成直方图；不要固定size 1关闭直方图，极易引发基数估算错误。
3. 64G内存8CPU硬件环境，手工收集统计信息并行度degree建议设置为4，不要直接设置等于CPU全部核数，避免收集操作耗尽主机CPU资源挤压业务。
4. 自动统计收集任务不建议全局关闭；个别参考码表、已经人工调优直方图的对象，使用LOCK_TABLE_STATS锁定单表统计，防止自动任务覆盖调优成果。
5. 重大版本上线、大批量数据变更场景，优先使用Pending待发布统计机制；收集完成先验证业务SQL性能，确认无性能退化之后再执行发布，隔离统计变更带来业务风险。
6. 统计信息变更前务必备份导出原有统计；一旦新统计造成SQL性能恶化，可以快速import回退旧统计，这是生产DBA必须准备的回退预案。
7. 动态采样只是补偿手段，不能替代正式统计信息收集；当数据高度倾斜，动态采样采样块有限，基数估算依旧会出现较大偏差。
8. 统计信息只是CBO优化器输入源头；统计修复完成后，部分旧游标依旧沿用旧统计，参数no_invalidate=>false会使化游标，让新统计快速生效，但会带来短暂硬解析压力，需要评估业务高峰窗口。

全部脚本建议读者在测试环境完整复现，理解每一条命令的输出、风险边界，再应用到真实生产运维工作。