数据库教程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命令
“`bash
#确认主机名称
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`查看相关参数
“`sql
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，剩余留给操作系统。
“`sql
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，授予统计管理相关权限
“`sql
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用户，创建测试表，构造字段数据倾斜，用于复现直方图缺失带来的基数估算错误。
“`sql
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 检查表、列、索引统计信息
“`sql
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状态。
“`sql
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命令演示（仅用于测试，生产业务表禁止使用）
“`sql
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）
“`sql
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级别统计收集
“`sql
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 只收集索引统计
“`sql
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负载。
“`sql
–开始采集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 查看自动统计任务状态
“`sql
SELECT client_name,status,group_id
FROM dba_autotask_client
WHERE client_name=’auto optimizer stats collection’;
“`

#### 2.5.2 查看自动任务维护窗口时间
“`sql
select window_name,repeat_interval,enabled,resource_plan
from dba_scheduler_windows;
“`

#### 2.5.3 启用、关闭自动统计收集任务（生产谨慎关闭）
“`sql
–关闭自动统计任务
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 删除表统计信息
>⚠生产环境谨慎执行，删除之后对象无统计，依赖动态采样估算。
“`sql
exec dbms_stats.delete_table_stats(ownname=>’FGEDU’,tabname=>’T_STAT_TEST’);
“`

#### 2.6.2 锁定表统计信息
锁定之后自动任务、手工gather_table_stats均不能覆盖该表统计，适合码表、已经调优好直方图的对象。
“`sql
begin
dbms_stats.lock_table_stats(ownname=>’FGEDU’,tabname=>’T_STAT_TEST’);
end;
/
“`
锁定之后再执行gather_table_stats会报ORA‑20005对象统计信息被锁定。

#### 2.6.3 解锁表统计信息
“`sql
begin
dbms_stats.unlock_table_stats(ownname=>’FGEDU’,tabname=>’T_STAT_TEST’);
end;
/
“`

#### 2.6.4 查询库内哪些表统计处于锁定状态
“`sql
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 创建统计信息中转存储表
“`sql
conn / as sysdba
exec dbms_stats.create_stattab(stattab=>’STATS_STG_TAB’,ownname=>’FGEDU’,tblspace=>’USERS’);
“`

#### 2.7.2 将单张表统计导出到中转表
“`sql
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简要示例：
“`bash
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 在目标库，从中转表导入统计信息
“`sql
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统计模式
“`sql
exec dbms_stats.set_global_prefs(‘PUBLISH’,’FALSE’);
“`
此时后续所有gather收集的统计，全部存入pending待发布区域，不会直接启用。

#### 2.8.2 收集测试表统计
“`sql
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视图查看待发布统计。
“`sql
select table_name,num_rows,last_analyzed from dba_tab_pending_stats where owner=’FGEDU’;
“`

#### 2.8.3 验证业务SQL执行计划，确认性能无退化，执行发布
“`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 会话级别修改参数测试
“`sql
alter session set optimizer_dynamic_sampling=0;
“`
此时关闭动态采样，如果表没有统计，CBO完全没有额外补偿，基数估算偏差巨大。

#### 2.9.2 SQL内部hint指定动态采样级别
“`sql
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`会使化游标，让新统计快速生效，但会带来短暂硬解析压力，需要评估业务高峰窗口。

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