Oracle 性能诊断与统计

一、核心概念速览
二、DML 修改监控：DBA_TAB_MODIFICATIONS
三、统计信息收集：GATHER_TABLE_STATS
- 3.1 为什么需要统计信息？
- 3.2 执行过程与影响
四、关键操作对比：FLUSH vs GATHER
五、AWR 快照机制
- 5.1 什么是 AWR 快照？
- 5.2 与 DBA_TAB_MODIFICATIONS 的关系
六、AWR 报告详解
七、最佳实践与 FAQ
- 7.1 自动化脚本模板
- 7.2 常见问题解答

一、核心概念速览

操作 / 机制	作用	是否清空 `DBA_TAB_MODIFICATIONS`	是否影响执行计划
`FLUSH_DATABASE_MONITORING_INFO`	将内存 DML 计数写入字典，使修改可见	❌ 否	❌ 否
`GATHER_TABLE_STATS`	收集表统计信息，供 CBO 使用	✅ 是	✅ 是
AWR 快照采集	每小时自动采集性能数据	❌ 否	❌ 否（但会触发 flush）
AWR 报告	分析两个快照间的性能差异	—	—

💡 一句话总结：
FLUSH 是为了“看见变化”，GATHER 是为了“告诉优化器变化”，AWR 是为了“诊断系统整体健康”。

二、DML 修改监控：`DBA_TAB_MODIFICATIONS`

2.1 作用与字段说明

记录自上次收集统计信息以来，表发生的 DML 操作估算数量。

字段	说明
`TABLE_OWNER`, `TABLE_NAME`	表标识
`INSERTS` / `UPDATES` / `DELETES`	估算行数（非精确）
`TRUNCATED`	是否执行过 `TRUNCATE`（YES/NO）
`TIMESTAMP`	最近一次 DML 时间

2.2 数据刷新机制

DML 计数初始在 SGA 内存中
以下操作会将其写入字典（使视图可见）：
- 手动 FLUSH_DATABASE_MONITORING_INFO
- AWR 快照采集（默认每小时）
- 执行 GATHER_TABLE_STATS（内部自动 flush）

⚠️ 只要未 gather stats，记录会持续累积。

2.3 何时需要关注？

ETL 后判断是否需更新统计信息
SQL 执行计划突变，怀疑统计信息陈旧
自动化运维中识别“高变更表”

三、统计信息收集：`GATHER_TABLE_STATS`

3.1 为什么需要统计信息？

Oracle 的 CBO（Cost-Based Optimizer优化器） 依赖统计信息生成高效执行计划。若统计信息过期（如表增长 50% 仍按旧行数估算），可能导致：

错误选择全表扫描而非索引
并行度计算错误
Join 顺序不合理

3.2 执行过程与影响

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE_NAME', CASCADE => TRUE);

内部流程：

隐式调用 FLUSH_DATABASE_MONITORING_INFO
扫描表（全表或采样），计算新统计
更新 DBA_TABLES.NUM_ROWS 等字典
清空 DBA_TAB_MODIFICATIONS 中该表记录

影响：

优化器使用新统计 → 执行计划可能改变
大表 gather 可能消耗资源（建议维护窗口执行）

四、关键操作对比：`FLUSH` vs `GATHER`

4.1 `FLUSH_DATABASE_MONITORING_INFO`

作用：让 DBA_TAB_MODIFICATIONS 显示最新 DML 计数
代价：极低（仅内存→字典写入）
适用：调试、检查、前置判断

4.2 `GATHER_TABLE_STATS`

作用：更新统计信息，重置变更跟踪起点
代价：高（需扫描数据）
适用：确认数据显著变化后（如 >10%）

4.3 推荐流程：“先 flush，再查，后 gather”

-- Step 1: 刷新
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

-- Step 2: 查询高变更表
SELECT table_owner, table_name
FROM dba_tab_modifications m
JOIN dba_tables t ON m.table_owner = t.owner AND m.table_name = t.table_name
WHERE (m.inserts + m.updates + m.deletes) > NVL(t.num_rows, 1) * 0.1
   OR m.truncated = 'YES';

-- Step 3: 对结果表执行 GATHER（可脚本化）

五、AWR 快照机制

5.1 什么是 AWR 快照？

Oracle 自动工作负载资料库（Automatic Workload Repository）
默认 每小时采集一次快照，保留 8 天
存储位置：SYSAUX 表空间的 WRH$ 系列表

5.2 与 `DBA_TAB_MODIFICATIONS` 的关系

问题	答案
AWR 快照会清空 `DBA_TAB_MODIFICATIONS` 吗？	❌ 不会
AWR 快照会让 DML 修改“可见”吗？	✅ 会（因触发 flush）
能否依赖 AWR 判断是否需 gather stats？	⚠️ 可以，但有最多 1 小时延迟

📌 结论：实时判断需手动 FLUSH；可接受延迟则可用 AWR。

六、AWR 报告详解

6.1 什么是 AWR 报告？

基于两个 AWR 快照之间的 Delta（差值）分析，生成的 HTML/TEXT 性能诊断报告。

6.2 如何提取 AWR 报告？

方法一：交互式（SQL*Plus）

sqlplus / as sysdba
SQL> @?/rdbms/admin/awrrpt.sql

按提示输入：

报告类型（HTML 推荐）
天数（如 1）
开始/结束快照 ID
文件名

方法二：脚本化（PL/SQL）

SPOOL awr_report.html
SELECT output FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid     => (SELECT dbid FROM v$database),
    l_inst_num => (SELECT instance_number FROM v$instance),
    l_bid      => 12344,
    l_eid      => 12345
  )
);
SPOOL OFF

6.3 AWR 报告能做什么？

功能	说明
🔍 定位瓶颈	Top 5 Timed Events 直接指出等待事件（如 I/O、锁）
📊 分析高负载 SQL	按 Elapsed Time / CPU / Gets 排序，找出慢 SQL
📈 评估资源使用	CPU、I/O、内存、Redo、事务率等
↔️ 对比性能变化	上线前后、故障前后对比
🤖 支撑其他工具	ADDM、ASH、SQL Tuning Advisor 的数据基础

6.4 典型结构与使用场景

典型结构（HTML）：

Report Summary（概览）
Top 5 Timed Events（关键瓶颈）
Load Profile（负载指标）
SQL Statistics（Top SQL 列表）
Wait Events（等待事件详情）

使用场景示例：

用户反馈下午 3 点系统变慢 →
提取 15:00–16:00 AWR 报告 →
发现 row lock contention + 某 UPDATE 未提交 →
联系开发修复事务逻辑。

七、最佳实践与 FAQ

7.1 自动化脚本模板

-- 自动识别并收集高变更表统计信息
BEGIN
  FOR rec IN (
    SELECT m.table_owner, m.table_name
    FROM dba_tab_modifications m
    JOIN dba_tables t ON m.table_owner = t.owner AND m.table_name = t.table_name
    WHERE (m.inserts + m.updates + m.deletes) > NVL(t.num_rows, 1) * 0.1
       OR m.truncated = 'YES'
  ) LOOP
    DBMS_STATS.GATHER_TABLE_STATS(rec.table_owner, rec.table_name, CASCADE => TRUE);
  END LOOP;
END;
/

7.2 常见问题解答

Q1：AWR 报告需要什么许可？
A：Oracle 企业版 + Diagnostic Pack（生产环境需确认）

Q2：标准版 Oracle 能用 AWR 吗？
A：不能。可用 Statspack（脚本：@?/rdbms/admin/spcreate.sql）

Q3：为什么 gather stats 后 DBA_TAB_MODIFICATIONS 消失了？
A：正常行为。语义是“自上次分析以来的修改”，分析后自然归零。

Q4：AWR 快照占空间吗？
A：是的。可通过以下命令调整保留策略：

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
  retention => 30*24*60,  -- 保留30天（分钟）
  interval  => 60         -- 每60分钟一次
);

🎯 终极建议：

日常运维：用 FLUSH + DBA_TAB_MODIFICATIONS 判断是否需 gather stats

性能问题：第一时间提取 AWR 报告，看 Top Events 和 Top SQL

自动化：结合两者，构建智能统计信息维护任务