liuziyi

1. AWR簡介與開啟方法

AWR（Automatic Workload Repository）是Oracle數據庫的性能診斷工具，它能自動收集、處理和維護性能統計信息。AWR默認情況下是開啟的，但我們可以通過以下方式確認和管理其狀態。

檢查AWR狀態

-- 檢查AWR快照設置
SELECT * FROM DBA_HIST_WR_CONTROL;

-- 檢查快照保留策略
SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID;

開啟/關閉AWR

-- 修改AWR快照設置（開啟）
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 11520,    -- 保留時間（分鐘），8天
    interval  => 60        -- 快照間隔（分鐘）
  );
END;
/

-- 關閉AWR快照（不推薦）
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    interval  => 0         -- 設置為0關閉自動快照
  );
END;
/

2. AWR快照管理

手動創建快照

-- 創建手動快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- 創建快照并返回快照ID
DECLARE
  snap_id NUMBER;
BEGIN
  snap_id := DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
  DBMS_OUTPUT.PUT_LINE('創建的快照ID: ' || snap_id);
END;
/

查看快照詳情

-- 查看所有快照信息
SELECT snap_id, 
       begin_interval_time, 
       end_interval_time,
       startup_time
FROM sys.wrm$_snapshot 
ORDER BY snap_id;

-- 查看最近24小時的快照
SELECT snap_id, 
       begin_interval_time, 
       end_interval_time
FROM sys.wrm$_snapshot 
WHERE begin_interval_time >= SYSDATE - 1
ORDER BY snap_id;

刪除快照

-- 刪除單個快照
EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 612, high_snap_id => 612);

-- 刪除快照范圍（如示例中的612到700）
EXEC DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 612, high_snap_id => 700);

-- 刪除特定時間范圍的快照
DECLARE
  min_snap_id NUMBER;
  max_snap_id NUMBER;
BEGIN
  SELECT MIN(snap_id), MAX(snap_id)
  INTO min_snap_id, max_snap_id
  FROM sys.wrm$_snapshot
  WHERE begin_interval_time BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') 
                              AND TO_DATE('2024-01-31', 'YYYY-MM-DD');
  
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
    low_snap_id => min_snap_id, 
    high_snap_id => max_snap_id
  );
END;
/

3. 生成AWR報告的方法

方法一：使用awrrpt.sql腳本（最常用）

-- 在SQL*Plus中執行
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- 或者指定完整路徑
@?/rdbms/admin/awrrpt.sql

執行步驟：

1. 選擇報告類型（HTML或TEXT）
2. 輸入快照天數或直接按回車查看所有可用快照
3. 選擇開始快照ID
4. 選擇結束快照ID
5. 指定報告輸出文件名

方法二：使用DBMS_WORKLOAD_REPOSITORY包

-- 生成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),
  l_bid => 612,        -- 開始快照ID
  l_eid => 700         -- 結束快照ID
));

-- 生成TEXT格式的AWR報告
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
  l_dbid => (SELECT dbid FROM v$database),
  l_inst_num => (SELECT instance_number FROM v$instance),
  l_bid => 612,
  l_eid => 700
));

方法三：生成指定時間段的AWR報告

-- 基于時間范圍生成AWR報告
VARIABLE report_clob CLOB;

BEGIN
  :report_clob := DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    (SELECT dbid FROM v$database),
    (SELECT instance_number FROM v$instance),
    NULL,  -- 使用時間范圍而不是快照ID
    TO_DATE('2024-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    TO_DATE('2024-01-01 11:00:00', 'YYYY-MM-DD HH24:MI:SS')
  );
END;
/

-- 查看報告內容
SELECT :report_clob FROM DUAL;

方法四：保存AWR報告到文件

-- 使用SPOOL命令保存報告
SET PAGESIZE 0
SET LINESIZE 1000
SET LONG 1000000
SET LONGCHUNKSIZE 1000000

SPOOL /tmp/awr_report_612_700.html

SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
  (SELECT dbid FROM v$database),
  (SELECT instance_number FROM v$instance),
  612,
  700
));

SPOOL OFF

4. 其他類型的AWR報告

生成比較AWR報告（AWR Diff Report）

-- 比較兩個時間段的性能
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

-- 或者使用包
SELECT OUTPUT
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(
  (SELECT dbid FROM v$database),
  (SELECT instance_number FROM v$instance),
  600, 610,  -- 第一個時間段
  700, 710   -- 第二個時間段
));

生成SQL報告

-- 針對特定SQL生成報告
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

生成Segment報告

-- 生成段級統計信息報告
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

5. AWR配置最佳實踐

推薦配置

-- 設置合理的AWR參數
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 40320,    -- 28天保留期
    interval  => 60,       -- 每小時快照
    topnsql   => 100       -- 保留TOP 100 SQL
  );
END;
/

-- 對于高負載系統
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 20160,    -- 14天保留期
    interval  => 30,       -- 每30分鐘快照
    topnsql   => 200
  );
END;
/

空間管理

-- 檢查AWR空間使用情況
SELECT * FROM DBA_HIST_SNAPSHOT 
ORDER BY snap_id DESC;

-- 估算AWR空間需求
SELECT * FROM DBA_HIST_SNAPSHOT 
WHERE begin_interval_time >= SYSDATE - 7;

6. 實用監控腳本

快照監控

-- 監控快照生成頻率
SELECT TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') as snapshot_time,
       snap_id,
       (end_interval_time - begin_interval_time) * 24 * 60 as duration_minutes
FROM sys.wrm$_snapshot 
WHERE begin_interval_time >= SYSDATE - 1
ORDER BY snap_id DESC;

-- 驗證刪除操作后的快照狀態
SELECT snap_id, begin_interval_time, end_interval_time 
FROM sys.wrm$_snapshot 
ORDER BY snap_id;

查找可用快照

-- 查找特定時間段的快照
SELECT snap_id, 
       begin_interval_time, 
       end_interval_time
FROM dba_hist_snapshot
WHERE begin_interval_time BETWEEN SYSDATE - 7 AND SYSDATE
ORDER BY snap_id;

7. 注意事項

1. 權限要求：操作AWR需要DBA角色或相應的系統權限
2. 空間考慮：定期清理舊快照以避免SYSAUX表空間膨脹
3. 性能影響：頻繁的快照可能對系統性能產生輕微影響
4. 業務時段：避免在業務高峰期進行大量快照刪除操作
5. 報告解讀：AWR報告包含大量信息，需要熟悉關鍵指標如：
   • 負載配置文件（Load Profile）
   • 實例效率百分比（Instance Efficiency Percentages）
   • 等待事件（Top 5 Timed Events）
   • SQL統計信息（SQL Statistics）

通過合理配置和管理AWR快照，并熟練掌握AWR報告的生成方法，可以有效地監控和診斷數據庫性能問題，同時控制存儲空間的合理使用。