72、Oracle性能优化：执行计划、SQL调优顾问、AWR报告、等待事件分析
说实话，Oracle性能优化这个话题，我做了十几年数据库开发，每次遇到新人都觉得他们有点「怕」这个领域。其实没那么玄乎。你想想看，数据库慢，无非就是SQL写得不好、索引没建对、或者系统资源在打架。今天这一章，我就把四个最核心的工具掰开揉碎了讲给你听。
一、执行计划：读懂Oracle的「内心独白」
执行计划是什么？说白了，就是Oracle告诉你：「我打算怎么执行你的SQL」。我个人的习惯是，任何SQL上线前，先看执行计划。不看执行计划就上线，那跟闭着眼睛开车差不多。
1.1 怎么获取执行计划
最常用的方式就两个：
- EXPLAIN PLAN FOR：只生成计划，不真正执行SQL
- DBMS_XPLAN.DISPLAY：把计划格式化输出，看着舒服
-- 方式一：EXPLAIN PLAN
EXPLAIN PLAN FOR
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.sal > 3000;
-- 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 方式二：从共享池抓取真实执行计划（推荐）
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', 0, 'ALLSTATS LAST')
);1.2 执行计划里看什么
拿到执行计划后，我一般按这个顺序扫一眼：
- 操作类型：是全表扫描（TABLE ACCESS FULL）还是索引扫描（INDEX RANGE SCAN）？全表扫描不一定慢，但大表全扫基本就是灾难。
- 代价（Cost）：Oracle估算的代价，数值越大越慢。但注意，这只是估算，别全信。
- 行数（Rows）：优化器预估的行数。如果预估和实际差太多，说明统计信息可能过期了。
- 谓词信息：Access Predicate 和 Filter Predicate。前者是「通过索引直接定位」，后者是「扫到数据后再过滤」。能走Access就别走Filter。
重点： 执行计划里最怕看到的是「TABLE ACCESS FULL」配合大表，以及「SORT ORDER BY」没有走索引排序。遇到这两种情况，基本可以断定SQL需要优化了。
二、SQL调优顾问：Oracle帮你「看病」
SQL Tuning Advisor（STA）是Oracle 10g以后自带的一个工具。它就像个老中医，你给它一个SQL，它给你开药方。我在项目中用过很多次，大部分建议靠谱，但偶尔也会开错方子。
2.1 怎么用STA
-- 第一步：创建调优任务
DECLARE
l_task_name VARCHAR2(100) := 'TUNE_TASK_001';
l_sql_id VARCHAR2(13) := '&sql_id'; -- 从v$sql或AWR中获取
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => l_task_name);
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => l_sql_id,
task_name => l_task_name,
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
time_limit => 300,
description => '调优任务示例'
);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_name);
END;
/
-- 第二步：查看调优建议
SET LONG 1000000
SET PAGESIZE 1000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_TASK_001') AS report
FROM dual;2.2 STA的常见建议
| 建议类型 | 说明 | 我的态度 |
|---|---|---|
| 创建索引 | 建议新建某个索引来加速查询 | 大部分情况采纳，但注意索引对DML的影响 |
| 收集统计信息 | 表的统计信息过旧，需要重新收集 | 几乎100%采纳，这是基本功 |
| 改写SQL | 建议用其他写法替代原SQL | 谨慎采纳，先测试性能提升是否明显 |
| 创建物化视图 | 对于复杂聚合查询，建议建物化视图 | 看场景，物化视图维护成本不低 |
三、AWR报告：数据库的「体检报告」
AWR（Automatic Workload Repository）报告，是Oracle自动收集的性能快照。我每次接手一个慢数据库，第一件事就是拉一份AWR报告看看。它就像体检报告，告诉你哪里「血压高」、哪里「血糖高」。
3.1 生成AWR报告
-- 方式一：使用脚本（最常用）
@?/rdbms/admin/awrrpt.sql
-- 方式二：手动生成（适合自动化）
SELECT * FROM TABLE(
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
l_dbid => (SELECT dbid FROM v$database),
l_instnum => 1,
l_bid => &begin_snap, -- 起始快照ID
l_eid => &end_snap -- 结束快照ID
)
);3.2 AWR报告的核心指标
拿到AWR报告后，我一般只看这几个部分：
- DB Time：数据库总耗时。如果DB Time远大于Elapsed Time，说明CPU或IO有瓶颈。
- Top 5 Timed Events：等待事件排名。这是最关键的，后面会细说。
- SQL Statistics：按消耗资源排序的SQL。看看哪些SQL是「吃资源大户」。
- Instance Efficiency Percentages：缓冲区命中率、库缓存命中率等。低于95%就要注意了。
我的经验： 有一次客户说系统慢，我拉了一份1小时的AWR报告，发现DB Time是3600秒，而Elapsed Time只有1800秒。这意味着CPU利用率超过100%（多核），系统确实在「硬扛」。后来加了CPU资源，问题就解决了。有时候问题不在SQL，在硬件。
四、等待事件分析：找到「卡点」
等待事件，就是Oracle在等什么。它可能是等磁盘IO、等锁、等网络、等日志写入。我个人认为，等待事件分析是性能优化的最高境界——你不需要猜，Oracle直接告诉你它卡在哪里。
4.1 常见的等待事件
| 等待事件 | 含义 | 常见原因 | 解决方向 |
|---|---|---|---|
| db file sequential read | 单块读，通常是索引读取 | 索引效率低、SQL走错索引 | 优化SQL、重建索引 |
| db file scattered read | 多块读，通常是全表扫描 | 大表全扫、缺少索引 | 加索引、分区表 |
| log file sync | 等待日志写入磁盘 | 频繁提交、磁盘IO慢 | 减少提交频率、换SSD |
| enq: TX - row lock contention | 行锁等待 | 并发更新同一行 | 优化业务逻辑、减少锁冲突 |
| latch free | 内部锁竞争 | CPU瓶颈、SQL解析过多 | 使用绑定变量、优化SQL |
4.2 实时查看等待事件
-- 查看当前会话的等待事件
SELECT sid, event, wait_class, wait_time, seconds_in_wait
FROM v$session
WHERE wait_class != 'Idle'
ORDER BY seconds_in_wait DESC;
-- 查看历史等待事件（从AWR中）
SELECT event, total_waits, time_waited_micro, average_wait
FROM dba_hist_system_event
WHERE begin_interval_time > SYSDATE - 1
ORDER BY time_waited_micro DESC;一个小技巧： 我经常用 v$session 实时监控。如果某个等待事件持续超过10秒，基本可以断定有问题。特别是「enq: TX - row lock contention」，一旦出现，说明有人在「死锁」边缘试探了。
五、四者如何配合使用
这四个工具不是孤立的。我一般按这个流程走：
- 先看AWR报告：了解整体健康状况，找到Top SQL和Top等待事件。
- 分析等待事件：从AWR的Top 5事件入手，确定瓶颈类型（IO、CPU、锁）。
- 定位具体SQL：从AWR的SQL Statistics中找到消耗最大的SQL。
- 查看执行计划：分析这个SQL的执行计划，找出全表扫描、排序、Nested Loops等问题。
- 使用SQL调优顾问：让STA给出建议，人工审核后实施。
这个流程我用了很多年，基本能覆盖90%的性能问题。剩下的10%，可能是Bug、配置问题或者硬件故障，那就需要更深入的分析了。
公众号:蓝海资料掘金营，微信deep3321