Oracle 批量產生 ADDM 報告自動化腳本：每日分時段分析資料庫效能

ADDM 能偵測的問題範疇

• 作業系統分頁/換頁、CPU 瓶頸（Oracle/非 Oracle 負載）
• Top SQL（CPU、I/O、RAC 內部通訊）
• 過多連線建立/斷開、硬/軟解析、索引爭用、各類鎖等待（User/DML/Row/ITL）
• Commit/Rollback 過多、I/O 效能（Log file sync、DBWR、Archiver、Redo log 過小）
• 記憶體不足（SGA/PGA/Buffer Cache/Shared Pool）、熱塊/熱物件、RAC 通訊/傾斜
• RMAN/Streams/AQ/Resource Manager 等待事件

部署架構

在專用目錄（如 E:\addm\）放置三個檔案：

1. addm.cmd：批次檔呼叫 SQL*Plus
2. addm.sql：主控腳本，分時段呼叫參數化腳本
3. myaddm.sql：參數化 ADDM 產生邏輯（接受起訖小時參數）

前置權限

GRANT SELECT ANY DICTIONARY TO your_user;
GRANT ADVISOR TO your_user;

檔案內容

addm.cmd

cmd.exe /c sqlplus username/password@servername @addm.sql

addm.sql

@@E:\addm\myaddm.sql 0 8
@@E:\addm\myaddm.sql 8 12
@@E:\addm\myaddm.sql 12 14
@@E:\addm\myaddm.sql 14 18
@@E:\addm\myaddm.sql 18 24
EXIT

myaddm.sql 核心邏輯

SET ECHO OFF VERIFY OFF FEEDBACK OFF TERMOUT ON HEADING OFF LINESIZE 300
VAR dbid NUMBER; VAR inst_num NUMBER; VAR bid NUMBER; VAR eid NUMBER;
VAR bhour VARCHAR2(10); VAR ehour VARCHAR2(10); VAR task_name VARCHAR2(40);

BEGIN
  :bhour := &1; :ehour := &2;
  IF LENGTH(:bhour)=1 THEN :bhour := '0'||:bhour; END IF;
  IF LENGTH(:ehour)=1 THEN :ehour := '0'||:ehour; END IF;
END;
/

BEGIN
  SELECT MIN(snap_id) INTO :bid FROM dba_hist_snapshot
   WHERE TO_CHAR(sysdate-1,'yyyy-mm-dd')||' '||:bhour <= TO_CHAR(end_interval_time,'yyyy-mm-dd HH24')
     AND TO_CHAR(sysdate-1,'yyyy-mm-dd')||' '||:ehour >= TO_CHAR(end_interval_time,'yyyy-mm-dd HH24');
  SELECT MAX(snap_id) INTO :eid FROM dba_hist_snapshot
   WHERE TO_CHAR(sysdate-1,'yyyy-mm-dd')||' '||:bhour <= TO_CHAR(end_interval_time,'yyyy-mm-dd HH24')
     AND TO_CHAR(sysdate-1,'yyyy-mm-dd')||' '||:ehour >= TO_CHAR(end_interval_time,'yyyy-mm-dd HH24');
  IF :ehour='24' THEN
    SELECT MAX(snap_id) INTO :eid FROM dba_hist_snapshot
     WHERE TO_CHAR(sysdate,'yyyy-mm-dd')||' 00' = TO_CHAR(end_interval_time,'yyyy-mm-dd HH24');
  END IF;
  SELECT dbid INTO :dbid FROM v$database;
  SELECT instance_number INTO :inst_num FROM v$instance;
END;
/

COLUMN report_name NEW_VALUE report_name NOPRINT
SELECT 'PMS_'||:inst_num||'_'||TO_CHAR(sysdate-1,'yyyymmdd')||'_'||:bhour||'-'||:ehour||'.txt' report_name FROM dual;

BEGIN
  DECLARE id NUMBER; name VARCHAR2(100); descr VARCHAR2(500);
  BEGIN
    name := ''; descr := 'ADDM run: snapshots ['||:bid||','||:eid||'], instance '||:inst_num||', dbid '||:dbid;
    DBMS_ADVISOR.CREATE_TASK('ADDM',id,name,descr,NULL);
    :task_name := name;
    DBMS_ADVISOR.SET_TASK_PARAMETER(name,'START_SNAPSHOT',:bid);
    DBMS_ADVISOR.SET_TASK_PARAMETER(name,'END_SNAPSHOT',:eid);
    DBMS_ADVISOR.SET_TASK_PARAMETER(name,'INSTANCE',:inst_num);
    DBMS_ADVISOR.SET_TASK_PARAMETER(name,'DB_ID',:dbid);
    DBMS_ADVISOR.EXECUTE_TASK(name);
  END;
END;
/

SPOOL &report_name
SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000
COLUMN get_clob FORMAT A80
SELECT DBMS_ADVISOR.GET_TASK_REPORT(:task_name,'TEXT','TYPICAL') FROM dual;
SPOOL OFF
PROMPT Report written to &report_name.

排程建議

使用 Windows 工作排程器或 Linux cron 每日凌晨 00:10 執行 addm.cmd，即可自動產生前一天分時段的 ADDM 報告（文字檔），利於例行效能巡檢。