初探ADDM的使用

ADDM能發現定位的問題包括：

·作業系統記憶體頁入頁出問題
·由於Oracle負載和非Oracle負載導致的CPU瓶頸問題
·導致不同資源負載的Top SQL語句和物件——CPU消耗、IO頻寬佔用、潛在IO問題、RAC內部通訊繁忙
·按照PLSQL和JAVA執行時間排的Top SQL語句.
·過多地連線 (login/logoff).
·過多硬解析問題——由於shared pool過小、書寫問題、繫結大小不適應、解析失敗原因引起的。
·過多軟解析問題
·索引查詢過多導致資源爭用.
·由於使用者鎖導致的過多的等待時間 (通過包dbms_lock加的鎖)
·由於DML鎖導致的過多等待時間(例如鎖住表了)
·由於管道輸出導致的過多等待時間(如通過包dbms_pipe.put進行管道輸出)
·由於併發更新同一個記錄導致的過多等待時間(行級鎖等待)
·由於ITL不夠導致的過多等待時間（大量的事務操作同一個資料塊）
·系統中過多的commit和rollback(logfile sync事件).
·由於磁碟頻寬太小和其他潛在問題（如由於logfile太小導致過多的checkpoint，MTTR設定問題，過多的undo操作等等）導致的IO效能問題
·對於DBWR程式寫資料塊，磁碟IO吞吐量不足
·由於歸檔程式無法跟上redo日至產生的速度，導致系統變慢
·redo資料檔案太小導致的問題
·由於擴充套件磁碟分配導致的爭用
·由於移動一個物件的高水位導致的爭用問題
·記憶體太小問題——SGA Target, PGA, Buffer Cache, Shared Pool
·在一個例項或者一個機群環境中存在頻繁讀寫爭用的熱塊
·在一個例項或者一個機群環境中存在頻繁讀寫爭用的熱物件
·RAC環境中內部通訊問題
·LMS程式無法跟上導致鎖請求阻塞
·在RAC環境中由於阻塞和爭用導致的例項傾斜
·RMAN導致的IO和CPU問題
·Streams和AQ問題
·資源管理等待事件

有一點要記住：AWR收集的資料時放到記憶體中（share pool），通過一個新的後臺程式MMON定期寫到磁碟中。所以10g的share pool要求比以前版本更大，一般推薦比以前大15-20%。另外，還要求系統引數STATISTICS_LEVEL設定為TYPICAL（推薦）或ALL;

ALTER SESSION SET STATISTICS_LEVEL= TYPICAL;

第一步：建立測試用的表

SQL> CREATE TABLE bigtab AS SELECT rownum as "id", a.* FROM dba_objects a;
 
Table created.

SQL> create table smalltab as select rownum as "id", a.* FROM dba_tables a;

Table created.

SQL> DECLARE
2       n NUMBER;
3    BEGIN
4       FOR n IN 1..100
5       LOOP
6           INSERT INTO bigtab SELECT rownum as "id", a.* FROM dba_objects a;
7           COMMIT;
8       END LOOP;
9   END;
/
 
PL/SQL procedure successfully completed.

第二步：採集一次工作量快照

SQL> begin
  2   dbms_workload_repository.create_snapshot('TYPICAL');
  3  end;
  4  /
 
PL/SQL procedure successfully completed.

第三步：進行一些高負荷操作

DECLARE
    v_var number;
BEGIN
    FOR n IN 1..6
    LOOP
        select count(*) into v_var from bigtab b, smalltab a;
    END LOOP;
END;
 /
PL/SQL procedure successfully completed.
 
第四步：再次採集一次工作量快照

要注意的是：兩次快照之間的間隔時間必須足夠（一般推薦30分鐘左右），否則得到的ADDM報告中就會提示：THERE WAS NOT ENOUGH DATABASE TIME FOR ADDM ANALYSIS.

SQL> begin
  2   dbms_workload_repository.create_snapshot('TYPICAL');
  3  end;
  4  /
 
PL/SQL procedure successfully completed.

第五步：建立一個優化診斷任務並執行

先獲取到兩次快照的ID:

SQL> select snap_id from
  2  (SELECT * FROM dba_hist_snapshot
  3  ORDER BY snap_id desc)
  4  where rownum <=2;
 
 SNAP_ID
--------
      99
      98
 
然後建立優化任務，並執行。
DECLARE
    task_name VARCHAR2(30) := 'DEMO_ADDM01';
    task_desc VARCHAR2(30) := 'ADDM Feature Test';
    task_id NUMBER;
BEGIN
    dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
    dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', 65);
    dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 66);
    dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 1);
    dbms_advisor.set_task_parameter(task_name, 'DB_ID', 1712);
    dbms_advisor.execute_task(task_name);
END;

第六步：檢視優化建議結果

通知函式dbms_advisor.get_task_report可以得到優化建議結果。
SQL> SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000
SQL> COLUMN get_clob FORMAT a80
SQL> SELECT dbms_advisor.get_task_report('DEMO_ADDM01', 'TEXT', 'ALL') FROM DUAL;

說明：
其中第五步到第六步可以直接執行$ORACLE_HOME/rdbms/admin/addmrpt.sql來得到，這個指令碼的執行過程和statspack指令碼執行過程類似：
此外，如果是RAC環境下，可以執行$ORACLE_HOME/rdbms/admin/addmrpti.sql，這指令碼的執行，會多出要求輸入DB ID和instance ID的要求。

以下就是測試的ADDM報告，供參考:

         DETAILED ADDM REPORT FOR TASK '任務_557' WITH ID 557
          --------------------------------------------------

              Analysis Period: 29-1月 -2008 from 14:51:50 to 17:07:09
         Database ID/Instance: 3051507877/1
      Database/Instance Names: IRMDB/irmdb
                    Host Name: IBM-L3YMBNP
             Database Version: 10.2.0.3.0
               Snapshot Range: from 476 to 479
                Database Time: 8175 seconds
        Average Database Load: 1 active sessions

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

FINDING 1: 100% impact (38109 seconds)
--------------------------------------
I/O 子系統的吞吐量比預期吞吐量小得多。

   RECOMMENDATION 1: Host Configuration, 100% benefit (38109 seconds)
      ACTION: 考慮增加 I/O 子系統的吞吐量。Oracle 建議的解決方案是使用 SAME
         方法將所有資料檔案條帶化。可能還需要增加磁碟數量以獲得更好的效能。或者,
 考慮使用 Oracle 的自動儲存管理解決方案。
      RATIONALE: 分析期間, 資料檔案的平均 I/O 吞吐量, 對於讀取為每秒 196 K, 對於
寫入為每秒 2.2
         K。單個塊讀取的平均響應時間為 34 毫秒。

   RECOMMENDATION 2: Host Configuration, 100% benefit (38076 seconds)
      ACTION: 檔案 D:\ORACLE\PRODUCT\10.2.0\ORADATA\IRMDB\TEMP01.DBF
         的效能比其它檔案低得多。如果不能使用 SAME 方法將所有的檔案條帶化, 可考
慮將此檔案條帶化至多個磁碟。
         RELEVANT OBJECT: database file
         "D:\ORACLE\PRODUCT\10.2.0\ORADATA\IRMDB\TEMP01.DBF"
      RATIONALE: 對於此檔案, 單個塊讀取的平均響應時間為 27 毫秒。

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: 等待類別 "使用者 I/O" 消耗了大量資料庫時間。 (25% impact [2043 seco
nds])

FINDING 2: 100% impact (8175 seconds)
-------------------------------------
在主機作業系統中檢測到大量虛擬記憶體寫入/寫出。

   RECOMMENDATION 1: Host Configuration, 100% benefit (8175 seconds)
      ACTION: 主機作業系統出現大量記憶體寫入/寫出, 但未檢測到根本原因。請研究不屬
於 (在消耗了大量虛擬記憶體的主機上執行的)
         此例項的程式。還可以考慮在主機中新增更多實體記憶體。

FINDING 3: 100% impact (8175 seconds)
-------------------------------------
發現 SQL 語句消耗了大量資料庫時間。

   RECOMMENDATION 1: SQL Tuning, 99% benefit (8102 seconds)
      ACTION: 優化 SQL_ID 為 "58s659jjhwz8r" 的 PL/SQL 塊。請參閱 Oracle 的 "PL/
SQL
         User\'s Guide and Reference" 中的 "Tuning PL/SQL Applications" 一章。
         RELEVANT OBJECT: SQL statement with SQL_ID 58s659jjhwz8r
         declare
         v_var number;
         begin
         for n in 1..6
         loop
         select count(*) into v_var from bigtab b,smalltab a;
         end loop;
         end;

   RECOMMENDATION 2: SQL Tuning, 92% benefit (7525 seconds)
      ACTION: 對 SQL_ID 為 "5v0mjsunmm5s4" 的 SQL 語句執行 SQL Tuning Advisor。

         RELEVANT OBJECT: SQL statement with SQL_ID 5v0mjsunmm5s4 and
         PLAN_HASH 3281046854
         SELECT COUNT(*) FROM BIGTAB B,SMALLTAB A
      RATIONALE: SQL_ID 為 "5v0mjsunmm5s4" 的 SQL 語句執行了 4 次, 每次執行平均
用時 2025 秒。

FINDING 4: 68% impact (5529 seconds)
------------------------------------
例項在 CPU 上花費的時間佔據了資料庫時間中的大部分。

   RECOMMENDATION 1: SQL Tuning, 92% benefit (7525 seconds)
      ACTION: 對 SQL_ID 為 "5v0mjsunmm5s4" 的 SQL 語句執行 SQL Tuning Advisor。

         RELEVANT OBJECT: SQL statement with SQL_ID 5v0mjsunmm5s4 and
         PLAN_HASH 3281046854
         SELECT COUNT(*) FROM BIGTAB B,SMALLTAB A
      RATIONALE: SQL_ID 為 "5v0mjsunmm5s4" 的 SQL 語句執行了 4 次, 每次執行平均
用時 2025 秒。
      RATIONALE: 每次執行使用 CPU 的平均時間為 1382 秒。

FINDING 5: 24% impact (1997 seconds)
------------------------------------
發現個別資料庫段造成了大量的使用者 I/O 等待。

   RECOMMENDATION 1: Segment Tuning, 24% benefit (1997 seconds)
      ACTION: 調查涉及 TABLE "SYS.BIGTAB" (物件 ID 為 55305) 的 I/O 的應用程式邏
輯。
         RELEVANT OBJECT: database object with id 55305
      RATIONALE: 物件的 I/O 使用統計資訊為: 5 完整物件掃描, 197509 物理讀取, 0
物理寫入和 0 直接讀取。
      RATIONALE: SQL_ID 為 "5v0mjsunmm5s4" 的 SQL 語句在等待熱物件的使用者 I/O 上
消耗了大量時間。
         RELEVANT OBJECT: SQL statement with SQL_ID 5v0mjsunmm5s4
         SELECT COUNT(*) FROM BIGTAB B,SMALLTAB A

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: 等待類別 "使用者 I/O" 消耗了大量資料庫時間。 (25% impact [2043 seco
nds])

FINDING 6: 24% impact (1997 seconds)
------------------------------------
發現個別 SQL 語句造成了大量的使用者 I/O 等待。

   RECOMMENDATION 1: SQL Tuning, 92% benefit (7525 seconds)
      ACTION: 對 SQL_ID 為 "5v0mjsunmm5s4" 的 SQL 語句執行 SQL Tuning Advisor。

         RELEVANT OBJECT: SQL statement with SQL_ID 5v0mjsunmm5s4 and
         PLAN_HASH 3281046854
         SELECT COUNT(*) FROM BIGTAB B,SMALLTAB A
      RATIONALE: SQL_ID 為 "5v0mjsunmm5s4" 的 SQL 語句執行了 4 次, 每次執行平均
用時 2025 秒。
      RATIONALE: 每次執行在使用者 I/O 等待事件上花費的平均時間為 499 秒。

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: 等待類別 "使用者 I/O" 消耗了大量資料庫時間。 (25% impact [2043 seco
nds])

FINDING 7: 23% impact (1919 seconds)
------------------------------------
PGA 大小不合適導致了對臨時表空間的附加 I/O, 從而消耗了大量資料庫時間。

   NO RECOMMENDATIONS AVAILABLE

   ADDITIONAL INFORMATION:
      分析期間, 引數 "pga_aggregate_target" 的值為 "143 M"。

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: 等待類別 "使用者 I/O" 消耗了大量資料庫時間。 (25% impact [2043 seco
nds])

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          ADDITIONAL INFORMATION
          ----------------------

等待類別 "應用程式" 並未消耗大量資料庫時間。
等待類別 "提交" 並未消耗大量資料庫時間。
等待類別 "併發" 並未消耗大量資料庫時間。
等待類別 "配置" 並未消耗大量資料庫時間。
等待類別 "網路" 並未消耗大量資料庫時間。
會話連線和斷開連線的呼叫並未消耗大量資料庫時間。
對 SQL 語句的硬語法分析並未消耗大量資料庫時間。

The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.

An explanation of the terminology used in this report is available when you
run the report with the 'ALL' level of detail.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-164243/，如需轉載，請註明出處，否則將追究法律責任。