开发者也能玩转 AWR：用 Oracle 自带的"体检报告"给 SQL 做靶向优化

不是 DBA，如何进行慢sql排查？ —— 一篇写给开发者的 AWR 实战笔记

"索引加了、hint 也写了，为什么线上还是慢？" 作为业务开发，我们对 SQL 的调优常常止步于 "看执行计划、加索引、再跑一遍" 三板斧。可一旦数据量上去、并发飙高，这套组合拳就失灵了。

其实 Oracle 早就给咱们备好了一份 **"体检报告"**——AWR（Automatic Workload Repository）。它把数据库最近一小时 / 一天 / 一周做了什么事、哪条 SQL 最耗 CPU、哪段等待事件最扎眼，全都拍成了 X 光片。

本篇不讲 DBA 的深邃参数，只谈 开发者最常用、最看得懂、最能立刻改代码 的 AWR 姿势。读完你能：

1. 2 分钟快速获取AWR报告；
2. 5 分钟定位"最该被优化"的 TopSQL；
3. 读懂执行计划与等待事件的"暗号"；
4. 给出一套可落地的代码级优化清单（索引、重写、分区、绑定变量…）。

带上你的慢 SQL，咱们直接开干！

1. AWR 到底是个啥 (开发者该怎么看)

一句话总结：AWR 就是 Oracle 帮你存好的"监控日志"，我们只需要学会"读日志"即可。

2. 一键生成 AWR：两种姿势

2.1 交互式，SQLPLUS命令导出

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

一路回车：
① 选 html ② 选 2 天内的快照编号（begin snap_id & end snap_id），需要排查问题时段的快照编号，建议选择间隔 1h 的快照。

间隔时间太长没有参考价值，关键问题会被稀释

③ 给出报告文件名：awrrpt_2025_24_17_18.html
④ 把文件拉回本地浏览器查看。

2.2 一条 SQL 秒出 HTML

适合自动化任务进行收集报告

SELECT DBMS_WORKLOAD_REPOSITORY.awr_report_html(
         l_dbid    => (select dbid from v$database),
         l_inst_num=> 1,
         l_bid     => 1234,   -- 开始 snap_id
         l_eid     => 1238,   -- 结束 snap_id
         l_options => 0)
FROM dual;

把返回的 CLOB 直接重定向到 .html 文件，即可得到和官方脚本一模一样的报告。

2.3 SQL脚本

-- Oracle AWR报告生成SQL脚本
-- 数据库: ORCL
-- 实例: oracle_dba
-- 快照范围: 1234 - 1235
-- 报告文件: awr_report_ORCL_20251224_1234_1235.html

-- 设置SQL*Plus输出格式
set pagesize 0
set linesize 1000
set feedback off
set heading off
set trimspool on
set markup html on

-- 设置输出文件
spool awr_report_ORCL_20251224_1234_1235.html

-- 生成AWR报告
-- 注意：请根据实际情况修改快照ID
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 
                   where instance_name = 'oracle_dba'),
    l_bid      => 1234,
    l_eid      => 1235
  )
);

spool off
set markup html off

-- 恢复默认设置
set pagesize 24
set linesize 80
set feedback on
set heading on

-- 显示完成信息
prompt AWR报告生成完成！
prompt 报告文件: awr_report_ORCL_20251224_1234_1235.html
prompt 
prompt 如需查看快照列表，请执行：
prompt SELECT snap_id, begin_interval_time, end_interval_time 
prompt FROM dba_hist_snapshot 
prompt WHERE instance_number = (SELECT instance_number FROM v$instance)
prompt ORDER BY snap_id DESC;

① 先获取需要的快照

sqlplus / as sysdba
SQL> SELECT snap_id, 
       TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI:SS') as begin_time,
       TO_CHAR(end_interval_time, 'YYYY-MM-DD HH24:MI:SS') as end_time
FROM dba_hist_snapshot 
WHERE instance_number = (SELECT instance_number FROM v$instance)
ORDER BY snap_id DESC;

   SNAP_ID BEGIN_TIME          END_TIME
---------- ------------------- -------------------
      3410 2025-12-24 20:00:45 2025-12-24 21:00:55
      3409 2025-12-24 19:00:35 2025-12-24 20:00:45
      3408 2025-12-24 18:00:24 2025-12-24 19:00:35
      3407 2025-12-24 17:00:14 2025-12-24 18:00:24
      3406 2025-12-24 16:00:04 2025-12-24 17:00:14
      3405 2025-12-24 15:00:54 2025-12-24 16:00:04
      3404 2025-12-24 14:00:43 2025-12-24 15:00:54
      3403 2025-12-24 13:00:33 2025-12-24 14:00:43
      3402 2025-12-24 12:00:23 2025-12-24 13:00:33
      ---额外的略去不表，这里我们就取3409-3410区间的，并将快照id替换上面的sql脚本（25-26行）的快照区间

② 将脚本保存为 awr_export.sql

③ 在保存脚本的文件夹打开 sqlplus

sqlplus / as sysdba
SQL> @awr_export.sql
<p>
<table border='1' width='90%' align='center' summary='Script output'>
<tr>
<td>
&lt;html lang=&quot;en&quot;&gt;&lt;head&gt;&lt;title&gt;AWR Report for DB: ORCL, Inst: orcl, Snaps: 3409-3410&lt;/title&gt;
</td>
...
</td>
</tr>
</table>
<p>
AWR报告生成完成！
报告文件: awr_report_ORCL_20251224_1234_1235.html

如需查看快照列表，请执行：
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
WHERE instance_number = (SELECT instance_number FROM v$instance)
ORDER BY snap_id DESC

④ 将文件拉取到本地浏览器查看

2.4 shell脚本

使用方式与sql脚本类似，将脚本保存为 awr_export.sh

chmod +x awr_export.sh
sh awr_export.sh

优化：可以从控制台读取用户输入参数

#!/bin/bash
# Oracle AWR报告生成脚本 (Linux/Unix)
# 使用说明：请确保已设置Oracle环境变量

# 数据库连接参数
export ORACLE_SID=ORCL
export DB_NAME="ORCL"
export INSTANCE_NAME="oracle_dba"
export BEGIN_SNAP="1234"
export END_SNAP="1235"
export REPORT_NAME="awr_report_ORCL_20251224_1234_1235.html"

# 检查Oracle环境
if [ -z "$ORACLE_HOME" ]; then
    echo "错误: ORACLE_HOME 环境变量未设置"
    exit 1
fi

# 检查必需参数
if [ -z "$BEGIN_SNAP" ] || [ -z "$END_SNAP" ]; then
    echo "错误: 请设置开始和结束快照ID"
    echo "使用方法: $0"
    echo "请先修改脚本中的BEGIN_SNAP和END_SNAP变量"
    exit 1
fi

echo "正在生成AWR报告..."
echo "数据库: $DB_NAME"
echo "实例: $INSTANCE_NAME" 
echo "快照范围: $BEGIN_SNAP - $END_SNAP"
echo "报告文件: $REPORT_NAME"

# 生成AWR报告的SQL脚本
cat > /tmp/generate_awr.sql << 'EOF'
-- 设置页面大小和格式
set pagesize 0
set linesize 1000
set feedback off
set heading off
set markup html on
spool awr_report_ORCL_20251224_1234_1235.html

-- 生成AWR报告
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 where instance_name = 'oracle_dba'),
    l_bid => 1234,
    l_eid => 1235
));

spool off
set markup html off
exit;
EOF

# 执行SQL脚本
sqlplus -s / as sysdba @/tmp/generate_awr.sql

# 检查报告是否生成成功
if [ -f "$REPORT_NAME" ]; then
    echo "AWR报告生成成功: $REPORT_NAME"
    echo "文件大小: $(ls -lh $REPORT_NAME | awk '{print $5}')"
    
    # 清理临时文件
    rm -f /tmp/generate_awr.sql
    
    echo "完成！请查看生成的AWR报告文件。"
else
    echo "错误: AWR报告生成失败"
    exit 1
fi

3. 5 分钟速读 AWR：开发者"三眼定位法"

打开报告，先别看密密麻麻的数字，按顺序盯 3 个区域：

3.1 Load Profile

3.2 Top 10 Foreground Events

3.3 SQL ordered by CPU

4. 实战案例：一条 30 秒 SQL 如何被砍到 0.2 秒

4.1 awr的现象

AWR 片段（关键信息已脱敏处理）：

SQL ordered by CPU     Snap: 3409~3410
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CPU (s)     Executions  CPU per Exec (s)  SQL Id
---------   ----------  ----------------  -------------
10,657.49   23,697      0.45              6m6dmhy5dryad

4.2 拿到 SQL 文本

SELECT /*+ index(t IDX_CUST_01) */ *
FROM orders t
WHERE status = :1
  AND create_time >= to_date(:2,'yyyy-mm-dd')
  AND substr(mobile,1,3)='138';

注意：开发在代码里把 mobile 字段做成了 函数索引，但实际传入的 :2 是字符串，导致 隐式类型转换；同时 substr(mobile,1,3) 让普通索引失效。

4.3 验证问题

EXPLAIN PLAN FOR
SELECT …  -- 原SQL
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
----------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  3248 |   400K|
|*  1 |  TABLE ACCESS FULL| ORDERS      |  3248 |   400K|
----------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------
   1 - filter(SUBSTR("MOBILE",1,3)='138'
          AND "STATUS"=TO_NUMBER(:1)
          AND "CREATE_TIME">=TO_DATE(:2,'yyyy-mm-dd'))

• 全表扫描 1.2 亿行
• STATUS 列出现 TO_NUMBER(:1)，说明代码里把字符串传给了 NUMBER 字段 → 隐式转换 → 索引失效

4.4 优化三步走

4.5 回归验证

新快照区间（3411~3412）同业务时段：

CPU (s)  Executions  CPU per Exec (s)  SQL Id
-------  ----------  ----------------  -------------
  138.2    17,280      0.008             6m6dmhy5dryad

• CPU 总量从 10,657.49 s → 138.2 s，下降 99%
• 单次执行从 0.45 s → 0.008 s，下降 98%

5. 开发中的常见等待事件速查表

6. 辅助：把 AWR 做成日常

6.1 定时生成快照

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 4320,   -- 30 天
    interval  => 60);    -- 60 分钟
END;
/

6.2 自动邮件推送 TopSQL

Python 伪代码（依赖 cx_Oracle、Jinja2、SMTP）：

import cx_Oracle, datetime, smtplib
from jinja2 import Template

def get_top_sql(db, bid, eid):
    sql = """
    SELECT sql_id, cpu_time_total/1e6 cpu_s, executions,
           round(cpu_time_total/executions/1e6,3) cpu_per_exec
    FROM   dba_hist_sqlstat
    WHERE  snap_id BETWEEN :bid AND :eid
    ORDER  BY cpu_time_total DESC
    FETCH FIRST 10 ROWS ONLY
    """
    return db.cursor().execute(sql, bid=bid, eid=eid).fetchall()

if __name__ == '__main__':
    db = cx_Oracle.connect(user='your_monitor', password='***', dsn='ORCL')
    bid, eid = 1234, 1238
    tops = get_top_sql(db, bid, eid)
    html = Template(open('top_sql_mail.html').read()).render(tops=tops)
    send_mail(to='dev-team@demo.com', subject='AWR TopSQL Alert', body=html)

6.3 给 DBA 提需求模板示例（直接抄）：

“AWR 显示 snap 1234~1238，SQL_ID 8xkj8fykfovzn 单次 CPU 0.71 s，表 ORDERS 全表扫描 1.2 亿行，请协助：

1. 确认列 STATUS 数据分布是否倾斜；
2. 如倾斜，请评估按 CREATE_TIME 做范围分区可行性；
3. 帮忙收集最新统计信息，解锁分区动态采样。”