Oracle AWR 常用筆記

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54


--手動執行快照
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- 設定AWR 快照區間
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 259200,   -- 以分鐘為單位 (7天 = 7 * 24 * 60)
    interval  => 10        -- 以分鐘為單位
  );
END;

-- 查詢 AWR 資料範圍
select SNAP_ID,DBID,INSTANCE_NUMBER,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME 
FROM awr_pdb_snapshot 
WHERE
END_INTERVAL_TIME >= to_date('2025-12-27 22:55:00','yyyy-mm-dd hh24:mi:ss') 
AND BEGIN_INTERVAL_TIME <= to_date('2025-12-28 05:20:00','yyyy-mm-dd hh24:mi:ss') 
AND INSTANCE_NUMBER=1 
ORDER BY SNAP_ID;

-- 輸出 AWR HTML
SELECT 
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1979277899,1,88, 89)
FROM dual;

--輸出AWR TEXT
SELECT output FROM TABLE(
   DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(1979277899,1,88, 89)
  );

SELECT output FROM TABLE(
   DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(1979277899,1,88, 89)
  );

--匯出 AWR 所有資料
 
EXECUTE DBMS_WORKLOAD_REPOSITORY.AWR_EXP(
dmpfile => ‘awrdat’,
dmpdir => ‘DATA_PUMP_DIR’,
bid => 1,
eid => 1000000
);

--匯入 AWR 所有資料

EXECUTE DBMS_WORKLOAD_REPOSITORY.AWR_IMP(
dmpfile => ‘awrdat’,
dmpdir => ‘DATA_PUMP_DIR’
);

-- AWR 空間使用率
SELECT space_usage_kbytes/1024 MB_used 
FROM v$sysaux_occupants 
WHERE occupant_name='SM/AWR';

generate_awr.sh

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


#!/bin/bash
ORACLE_SID=ORCLCDB
ORACLE_HOME=/u01/app/oracle/product/19.3/dbhome_1
REPORT_FILE="awr_report_$(date +%Y%m%d).html"

sqlplus -s / as sysdba <<EOF
set pages 0 lines 200 feedback off
spool $REPORT_FILE
SELECT dbms_workload_repository.awr_report_html(
  (SELECT dbid FROM v\$database),
  (SELECT instance_number FROM v\$instance),
  (SELECT MAX(snap_id)-1 FROM dba_hist_snapshot),
  (SELECT MAX(snap_id) FROM dba_hist_snapshot)
) FROM dual;
spool off
EOF

echo "AWR報告路徑：$REPORT_FILE"

Reference #

Reference
#