【kingsql分享】ADDM的研究和分析

Oracle 10G推出ADDM，和同時推出的STA結合使用，可以節省很多最佳化的時間，ADDM收集相關統計資料到AWR中，而STA根據這些資料，給出合理的最佳化建議。

  

ADDM具有一整套最佳化方案，基於某一段時間內的AWR快照可以透過ADDM來分析，可以診斷在該段時間內資料庫有可能存在的瓶頸。

 

ADDM分析的目標是減少DB TIME的值，DB TIME決定吞吐量，是資料庫處理使用者請求花費時間的一個總和，包括等到時間和CPU處理時間，可以透過v$sess_time_model和v$sys_time_model來查詢它們的值。

 

透過減少DB TIME的值，在同樣的系統資源背景下，資料庫可以處理相對更多的請求，也就相當於提高了資料庫的吞吐能力。同時，ADDM報告中的問題，也是透過DB TIME來進行排序的。

 

在預設情況下，ADDM是自動開啟的，由STATISTICS_LEVEL引數來控制，這個引數應該被設定為TYPICAL或ALL，如果設定為BASIC，則將會關閉很多特性。

SYS@hzh%11gR2>show parameter STATISTICS_LEVEL;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

statistics_level                     string      TYPICAL

 

ADDM對讀寫效能的分析一部分是依賴於引數DBIO_EXPECTED（讀取一個資料庫塊，所需要花費的平均時間）單位是微秒，預設為10毫秒，該引數是透過DBMS_ADVISOR.SET_DEFAULT_PARAMETER來設定的，例如

SYS@hzh%11gR2>EXECUTE DBMS_ADVISOR.SET_DEFAULT_PARAMETER ('ADDM','DBIO_EXPECTED',10000);

 

為能診斷資料庫效能方面的問題，ADDM可以跨越任意2個快照來分析，只要這兩個快照在建立過程中沒有錯誤並且沒有被刪除，並且期間資料庫不能發生關閉和啟動的事件。

 

下面來做個實驗，進行ADDM採集和診斷

1.建立測試表

SYS@hzh%11gR2>create table hzh1 (x number);

 

Table created.

 

2.進行一些負荷操作

SYS@hzh%11gR2>declare

  2  n number;

  3  begin

  4  for n in 1..888888

  5  loop

  6  insert into hzh1 values (n);

  7  commit;

  8  end loop;

  9  end;

 10  /

 

PL/SQL procedure successfully completed.

 

3.採集一次工作量快照

SYS@hzh%11gR2>begin

  2  dbms_workload_repository.create_snapshot('TYPICAL');

  3  end;

  4  /

 

PL/SQL procedure successfully completed.

 

4.進行高負荷操作

SYS@hzh%11gR2>declare

  2  n number;

  3  begin

  4  for n in 1..88888888

  5  loop

  6  insert into hzh1 values (n);

  7  commit;

  8  end loop;

  9  end;

 10  /

 

PL/SQL procedure successfully completed.

5.再次採集一次工作量快照

SYS@hzh%11gR2>begin

  2  dbms_workload_repository.create_snapshot('TYPICAL');

  3  end;

  4  /

 

PL/SQL procedure successfully completed.

 

6.建立一個最佳化診斷任務並執行

SYS@hzh%11gR2>select snap_id from (SELECT * FROM dba_hist_snapshot ORDER BY snap_id desc) where rownum <=2;

 

   SNAP_ID

----------

        14

        13

 

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', 13);

    dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 14);

    dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 1);

    dbms_advisor.set_task_parameter(task_name, 'DB_ID', 609310700);

    dbms_advisor.execute_task(task_name);

END;

/

 

7.檢視最佳化建議結果
透過函式dbms_advisor.get_task_report可以得到最佳化建議結果

SYS@hzh%11gR2>SET LONG 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000

SYS@hzh%11gR2>COLUMN get_clob FORMAT a80

SYS@hzh%11gR2>SELECT dbms_advisor.get_task_report('DEMO_ADDM01', 'TEXT', 'ALL') FROM DUAL;

          ADDM Report for Task 'DEMO_ADDM01'

          ----------------------------------

 

Analysis Period

---------------

AWR snapshot range from 13 to 14.

Time period starts at 16-OCT-14 02.49.16 PM

Time period ends at 16-OCT-14 03.19.55 PM

 

Analysis Target

---------------

Database 'HZH' with DB ID 609310700.

Database version 11.2.0.1.0.

ADDM performed an analysis of instance hzh, numbered 1 and hosted at

SLES-10-01.

 

Activity During the Analysis Period

-----------------------------------

Total database time was 1374 seconds.

The average number of active sessions was .75.

 

//這一部分包括一些基礎資訊，分析時間段、DB和instance ID&名字、主機名字、Oracle版本、快照範圍、資料庫消耗時間、多少個活動會話

 

Summary of Findings

-------------------

   Description                    Active Sessions      Recommendations

                                  Percent of Activity

   -----------------------------  -------------------  ---------------

1  Top SQL Statements             .71 | 95.59          2

2  Unusual "User I/O" Wait Event  .02 | 3.02           1

//ADDM發現的問題，並給出的相應建議

 

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

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

 

 

          Findings and Recommendations

          ----------------------------

 

Finding 1: Top SQL Statements

Impact is .71 active sessions, 95.59% of total activity.

--------------------------------------------------------

SQL statements consuming significant database time were found. These

statements offer a good opportunity for performance improvement.

 

   Recommendation 1: SQL Tuning

   //建議1

   Estimated benefit is .54 active sessions, 72.79% of total activity.

   -------------------------------------------------------------------

   Action

      Run SQL Tuning Advisor on the INSERT statement with SQL_ID

      "bcmbpq6aums4r".

      Related Object

         SQL statement with SQL_ID bcmbpq6aums4r.

         INSERT INTO HZH1 VALUES (:B1 )

   Rationale

      The SQL spent 100% of its database time on CPU, I/O and Cluster waits.

      This part of database time may be improved by the SQL Tuning Advisor.

   Rationale

      Database time for this SQL was divided as follows: 100% for SQL

      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java

      execution.

   Rationale

      SQL statement with SQL_ID "bcmbpq6aums4r" was executed 9539999 times and

      had an average elapsed time of 0.000098 seconds.

   Rationale

      Top level calls to execute the PL/SQL statement with SQL_ID

      "5c4dk552wu1df" are responsible for 100% of the database time spent on

      the INSERT statement with SQL_ID "bcmbpq6aums4r".

      Related Object

         SQL statement with SQL_ID 5c4dk552wu1df.

         declare

         n number;

         begin

         for n in 1..88888888

         loop

         insert into hzh1 values (n);

         commit;

         end loop;

         end;

 

   Recommendation 2: SQL Tuning

   //建議2

   Estimated benefit is .17 active sessions, 22.79% of total activity.

   -------------------------------------------------------------------

   Action

      Investigate the PL/SQL statement with SQL_ID "5c4dk552wu1df" for

      possible performance improvements. You can supplement the information

      given here with an ASH report for this SQL_ID.

      Related Object

         SQL statement with SQL_ID 5c4dk552wu1df.

         declare

         n number;

         begin

         for n in 1..88888888

         loop

         insert into hzh1 values (n);

         commit;

         end loop;

         end;

   Rationale

      The SQL Tuning Advisor cannot operate on PL/SQL statements.

   Rationale

      Database time for this SQL was divided as follows: 88% for SQL

      execution, 0% for parsing, 12% for PL/SQL execution and 0% for Java

      execution.

   Rationale

      SQL statement with SQL_ID "5c4dk552wu1df" was executed 1 times and had

      an average elapsed time of 1348 seconds.

 

 

Finding 2: Unusual "User I/O" Wait Event

Impact is .02 active sessions, 3.02% of total activity.

-------------------------------------------------------

Wait event "Data file init write" in wait class "User I/O" was consuming

significant database time.

 

   Recommendation 1: Application Analysis

   //建議1

   Estimated benefit is .02 active sessions, 3.02% of total activity.

   ------------------------------------------------------------------

   Action

      Investigate the cause for high "Data file init write" waits. Refer to

      Oracle's "Database Reference" for the description of this wait event.

 

   Symptoms That Led to the Finding:

   ---------------------------------

      Wait class "User I/O" was consuming significant database time.

      Impact is .03 active sessions, 3.51% of total activity.

 

 

 

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

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

 

          Additional Information

          ----------------------

 

Miscellaneous Information

-------------------------

Wait class "Application" was not consuming significant database time.

Wait class "Commit" was not consuming significant database time.

Wait class "Concurrency" was not consuming significant database time.

Wait class "Configuration" was not consuming significant database time.

CPU was not a bottleneck for the instance.

Wait class "Network" was not consuming significant database time.

Session connect and disconnect calls were not consuming significant database

time.

Hard parsing of SQL statements was not consuming significant database time.

//內容是關於此次最佳化建議的一些附加資訊