開發者也能玩轉 AWR：用 Oracle 自帶的"體檢報告"給 SQL 做靶向優化

不是 DBA，如何進行慢sql排查？ —— 一篇寫給開發者的 AWR 實戰筆記

"索引加了、hint 也寫了，為什么線上還是慢？" 作為業務開發，我們對 SQL 的調優常常止步于 "看執行計劃、加索引、再跑一遍" 三板斧?？梢坏祿可先ァ⒉l飆高，這套組合拳就失靈了。

其實 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. 幫忙收集最新統計信息，解鎖分區動態采樣?！?/li>