為了診斷資料庫效能問題，首先檢視ADDM分析報告，通常它是在生成AWR快照時自動建立的。如果缺 省的分析不能滿足，可以手動執行ADDM分析操作。ADDM可以對任何兩個AWR快執行分析，只要快照仍然儲存在資料庫中而沒有被清除掉。當在生成AWR快 照時如果出現了嚴重錯誤，ADDM將不會對例項進行分析。在這種情況下，ADDM將只能對例項的最大子集（沒有出錯的部分）進行分析。

手動執行ADDM分析可以使用dbms_addm包來執行操作，ADDM分析主要包括以下幾種模式:
.以資料庫模式來執行ADDM分析
.以例項模式來執行ADDM分析
.以部分模式來執行ADDM分析
.顯式ADDM分析報告

以資料庫模式來執行ADDM分析
對於Oracle RAC來說，可以以資料庫模式來執行ADDM來分析資料庫的所有例項。對於單例項資料庫， 仍然可以以資料庫模式來執行ADDM分析，如果以例項模式執行那麼ADDM將簡化其行為。

使用dbms_addm.analyze_db過程來以資料庫模式執行ADDM:

begin
  dbms_addm.analyze_db(
    task_name       in out varchar2,
    begin_snapshot  in     number,
    end_snapshot    in     number,
    db_id           in     number :=null
    );
end;
/

task_name引數指定將要被建立的分析任務名稱。begin_snapshot引數指定分析週期的開始快照。 end_snapshot引數指定分析週期的快照。db_id引數指定將要被分析的資料庫標識。如果沒有指定， 這個引數將使用當前所連線的資料庫標識。

下面的例了建立一個以資料庫模式來執行ADDM的任務，並且對快照481到484之間的時間週期對整個數 據庫執行效能診斷。

SQL> var tname varchar2(30)
SQL> begin
  2    :tname:='ADDM for snapshot 481 to 484';
  3    dbms_addm.analyze_db(:tname,481,484);
  4  end;
  5  /

PL/SQL procedure successfully completed.

以例項模式執行ADDM
為了對資料庫的特定例項進行分析，可以以例項模式來執行ADDM。使用dbms_addm.analyze_inst過程 來進行操作:

begin
  dbms_addm.analyze_inst(
    task_name       in out varchar2,
    begin_snapshot  in     number,
    end_snapshot    in     number,
    instance_number in     number :=null,
    db_id           in     number :=null
    );
end;
/

task_name引數指定將要被建立的分析任務名稱。begin_snapshot引數指定分析週期的開始快照。 end_snapshot引數指定分析週期的快照。instance_number引數指定將會被分析的例項編號，如果沒 有指定，將會使用當前所連線的例項。db_id引數指定將要被分析的資料庫標識。如果沒有指定，這 個引數將使用當前所連線的資料庫標識。

下面的例子以例項模式來執行ADDM，並且對例項1的471到474的快照執行效能論斷:

SQL> var tname varchar2(30)
SQL> begin
  2    :tname:='my addm for 471 to 474';
  3    dbms_addm.analyze_inst(:tname,471,474,1);
  4  end;
  5  /

PL/SQL procedure successfully completed.

以部分模式來執行ADDM
為了對所有例項中的部分例項執行分析，可以以部分模式來執行ADDM。可以使用 dbms_addm.analyze_partial過程來執行:

begin
  dbms_addm.analyze_partial(
    task_name       in out varchar2,
    instance_number in     number,
    begin_snapshot  in     number,
    end_snapshot    in     number,
    db_id           in     number :=null
    );
end;
/

task_name引數指定將要被建立的分析任務名稱。instance_number引數用分號來隔開將會被分析的實 例編號。begin_snapshot引數指定分析週期的開始快照。end_snapshot引數指定分析週期的快照。 db_id引數指定將要被分析的資料庫標識。如果沒有指定，這個引數將使用當前所連線的資料庫標識 。

下面的例子將以部分模式來建立ADDM診斷任務，並且對例項1,2,4的137到145之間的快照執行效能診 斷：

var tname varchar2(30)
begin
  :tname:='my addm for 137 to 145';
  dbms_addm.analyze_partial(:tname,'1,2,4',137,145);
end;
/

顯示ADDM報告
為了以文字方式顯示一個已經執行的ADDM任務的報告，可以使用dbms_addm.get_report函式:

dbms_addm.get_report(task_name in varchar2 return clob);

下面的例子使用tname變數指定addm任務名，使用dbms_addm.get_report來以文字方式來顯示ADDM報 告:

SQL> set long 1000000 pagesize 0;
SQL> select dbms_addm.get_report(:tname) from dual;
          ADDM Report for Task 'my addm for 471 to 474'
          ---------------------------------------------

Analysis Period
---------------
AWR snapshot range from 471 to 474.
Time period starts at 28-SEP-16 03.00.18 AM
Time period ends at 28-SEP-16 06.00.39 AM

Analysis Target
---------------
Database 'SJJH' with DB ID 4134995129.
Database version 11.2.0.4.0.
ADDM performed an analysis of instance sjjh, numbered 1 and hosted at
localhost.localdomain.

Activity During the Analysis Period
-----------------------------------
Total database time was 13999 seconds.
The average number of active sessions was 1.29.

Summary of Findings
-------------------
   Description                               Active Sessions      Recommendation
s                                              Percent of Activity
   ----------------------------------------  -------------------  --------------
- 1  Top SQL Statements                        .91 | 69.99          6
2  Top Segments by "User I/O" and "Cluster"  .17 | 13.43          3
3  Undersized Redo Log Buffer                .13 | 10.23          1
4  Log File Switches                         .1 | 7.59            2
5  Buffer Busy - Hot Objects                 .06 | 4.37           3
6  Commits and Rollbacks                     .04 | 2.72           1
7  "Network" Wait Class                      .03 | 2.24           0


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


          Findings and Recommendations
          ----------------------------

Finding 1: Top SQL Statements
Impact is .91 active sessions, 69.99% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.

   Recommendation 1: SQL Tuning
   Estimated benefit is .27 active sessions, 21.07% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the CREATE MATERIALIZED VIEW  statement with SQL_ID
      "7v89hvfv38196" 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 7v89hvfv38196.
         create materialized view mt_fee_fin build immediate
         refresh fast with primary key on demand
         start with sysdate next sysdate+1
         as select * from mt_fee_fin@dbl_yb
   Rationale
      The SQL Tuning Advisor cannot operate on CREATE MATERIALIZED VIEW
      statements.
   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.

   Recommendation 2: SQL Tuning
   Estimated benefit is .17 active sessions, 13.41% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the INSERT statement with SQL_ID
      "f64qufxuu0r5g".
      Related Object
         SQL statement with SQL_ID f64qufxuu0r5g.
         /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO
         "SJGX_YB"."DWGRBTXX"("個人保險號","姓名","身份證","性別","人員類別","單
位程式碼","單位名稱","應繳
         期間","險種","款項","補退金額","計算基數") SELECT
         "I"."INSR_CODE","I"."NAME","I"."IDCARD",DECODE("I"."SEX",0,'女','男'),"
          BPT"."PERS_NAME","C"."CORP_CODE","C"."CORP_NAME","IP"."PERIOD","PDI".
         "INSR_DETAIL_NAME","PMI"."MONEY_NAME","IP"."PAY_MONEY","IP"."CALC_BAS
         E" FROM "LV_INDIPAR" "IP","LV_CROPFUNDPAR" "CF","LV_INSR_TOPAY"
         "IT","BS_CORP" "C","BS_INSURED" "I","BS_PERSON_TYPE"
         "BPT","PFS_INSUR_DETAIL_INFO" "PDI","PFS_MONEY_INFO" "PMI" WHERE
         "IT"."PAY_INFO_NO"="CF"."PAY_INFO_NO" AND
         "CF"."MONEY_NO"="IP"."MONEY_NO" AND "PMI"."MONEY_ID"="CF"."MONEY_ID"
         AND "C"."CORP_ID"="IT"."CORP_ID" AND "I"."INDI_ID"="IP"."INDI_ID" AND
         "IT"."INSR_DETAIL_CODE"="PDI"."INSR_DETAIL_CODE"(+) AND
         ("IT"."BUSI_ASG_NO"=(-999) OR "IT"."BUSI_ASG_NO"=(-998) OR
         "IT"."BUSI_ASG_NO"=(-997) OR "IT"."BUSI_ASG_NO"=(-981) OR
         "IT"."BUSI_ASG_NO"=(-980) OR NVL("IT"."BUSI_ASG_NO",0)=0) AND
         "IT"."INDI_PAY_FLAG"=0 AND "BPT"."PERS_TYPE"="I"."PERS_TYPE" AND
         "BPT"."CENTER_ID"='430701' AND "IT"."TOPAY_TYPE"=3 AND
         "IT"."INSR_DETAIL_CODE"<>'21'
   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 "f64qufxuu0r5g" was executed 18 times and had
      an average elapsed time of 87 seconds.
   Rationale
      Full scan of TABLE "SJGX_YB.LV_INDIPAR" with object ID 89473 consumed
      77% of the database time spent on this SQL statement.
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "2wkfgbnhtqcj9" are responsible for 100% of the database time spent on
      the INSERT statement with SQL_ID "f64qufxuu0r5g".
      Related Object
         SQL statement with SQL_ID 2wkfgbnhtqcj9.
         DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
         broken BOOLEAN := FALSE; BEGIN
         dbms_refresh.refresh('"SJGX_YB"."DWGRBTXX"'); :mydate := next_date;
         IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

   Recommendation 3: SQL Tuning
   Estimated benefit is .16 active sessions, 12.12% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the INSERT statement with SQL_ID
      "c1fw0514uxxrs".
      Related Object
         SQL statement with SQL_ID c1fw0514uxxrs.
         INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SJGX_YB"."V_DWJKXX" select
         單位名稱,
         單位編碼,
         組織機構程式碼,
         業務單號,
         繳款來源,
         經辦人,
         繳款日期,
         繳款金額,
         險種名稱,
         分配金額,
         使用待轉金額,
         生成待轉金額,
         稽核狀態,
         憑證號
         from
         (
         select    distinct(a.busi_bill_sn) as 業務單號,
         bc.corp_name 單位名稱,
         bc.corp_code 單位編碼,
         bc.insur_org_code 組織機構程式碼,
         pm.pay_method_name as 繳款來源,
         a.make_bill 經辦人,
         to_char(a.make_bill_tm, 'yyyy-mm-dd') 繳款日期,
         c.pay_money 繳款金額,
         d.insr_detail_name 險種名稱,
         c.pay_money-c.bld_wait_money+c.use_wait_money as 分配金額,
         c.use_wait_money 使用待轉金額,
         c.bld_wait_money 生成待轉金額,
         (case a.audit_flag
         when 1 then
         '已稽核'
         else
         '未稽核'
         end) 稽核狀態,
         id.cred_no 憑證號
         from lv_busi_bill a,
         lv_busi_record b,
         lv_busi_assign c,
         (select insr_detail_code, insr_detail_name
         from pfs_insur_detail_info
         union
         select 999, '鋪底險種' from dual) d,
         inte_data id,
         bs_corp bc,
         bs_pay_method pm
         where a.busi_bill_sn = b.busi_bill_sn
         and bc.corp_id = b.pay_object_id
         and bc.center_id = id.center_id
         and id.obj_code=bc.corp_id
         -- and a.center_id='430701'
         and a.pay_object = 1
         -- and b.pay_object_id='989'
         and id.bill_no = to_char(a.busi_bill_sn)
         and c.busi_reco_no = b.busi_reco_no
         and d.insr_detail_code = c.insr_detail_code
         and a.pay_method = pm.pay_method(+)
         --  and to_char(a.make_bill_tm, 'yyyymm') between '201601' and
         '201601'
         --and to_char(b.fact_pay_date, 'yyyymm') between '201601' and
         '201601'
         order by  to_char(a.make_bill_tm,'yyyy-mm-dd'))
   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
      I/O and Cluster wait for TABLE "SJGX_YB.INTE_DATA" with object ID 89405
      consumed 100% of the database time spent on this SQL statement.
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "2h0f0svtyt4c7" are responsible for 100% of the database time spent on
      the INSERT statement with SQL_ID "c1fw0514uxxrs".
      Related Object
         SQL statement with SQL_ID 2h0f0svtyt4c7.
         DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
         broken BOOLEAN := FALSE; BEGIN
         dbms_refresh.refresh('"SJGX_YB"."V_DWJKXX"'); :mydate := next_date;
         IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

   Recommendation 4: SQL Tuning
   Estimated benefit is .16 active sessions, 12.04% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the DELETE statement with SQL_ID
      "3bvqft1u53xqz". Additionally, investigate this statement 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 3bvqft1u53xqz.
         /* MV_REFRESH (DEL) */ delete from "SJGX_YB"."GRCBXX"
   Rationale
      The SQL spent 51% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
      Look at data given below and an ASH report for further performance
      improvements.
   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 "3bvqft1u53xqz" was executed 18 times and had
      an average elapsed time of 79 seconds.
   Rationale
      Waiting for event "log buffer space" in wait class "Configuration"
      accounted for 46% of the database time spent in processing the SQL
      statement with SQL_ID "3bvqft1u53xqz".
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "8fn8wsfvd344t" are responsible for 100% of the database time spent on
      the DELETE statement with SQL_ID "3bvqft1u53xqz".
      Related Object
         SQL statement with SQL_ID 8fn8wsfvd344t.
         DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
         broken BOOLEAN := FALSE; BEGIN
         dbms_refresh.refresh('"SJGX_YB"."GRCBXX"'); :mydate := next_date; IF
         broken THEN :b := 1; ELSE :b := 0; END IF; END;

   Recommendation 5: SQL Tuning
   Estimated benefit is .1 active sessions, 7.74% of total activity.
   -----------------------------------------------------------------
   Action
      Investigate the INSERT statement with SQL_ID "0b6acnpktxcqd" 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 0b6acnpktxcqd.
         /* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO
         "SJGX_YB"."GRCBXX"("姓名","身份證號","社保編號","險種子項類別","開始時
間","視同繳費月數","個人險種狀
         態") SELECT "BI"."NAME","BI"."IDCARD","BI"."INSR_CODE","DI"."INSR_DETA
         IL_NAME",TO_CHAR("PI"."BEGIN_DATE",'yyyy-mm-dd'),"PI"."ALI_PAY_MONS",
         CASE "PI"."INDI_JOIN_STA" WHEN 0 THEN '無效' ELSE '有效' END  FROM
         "BS_PRES_INSUR" "PI","PFS_INSUR_DETAIL_INFO" "DI","BS_INSURED" "BI"
         WHERE "PI"."INDI_ID"="BI"."INDI_ID" AND
         "PI"."INSR_DETAIL_CODE"="DI"."INSR_DETAIL_CODE"
   Rationale
      The SQL spent only 22% of its database time on CPU, I/O and Cluster
      waits. Therefore, the SQL Tuning Advisor is not applicable in this case.
      Look at performance data for the SQL to find potential improvements.
   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 "0b6acnpktxcqd" was executed 18 times and had
      an average elapsed time of 49 seconds.
   Rationale
      Waiting for event "log buffer space" in wait class "Configuration"
      accounted for 51% of the database time spent in processing the SQL
      statement with SQL_ID "0b6acnpktxcqd".
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "8fn8wsfvd344t" are responsible for 100% of the database time spent on
      the INSERT statement with SQL_ID "0b6acnpktxcqd".
      Related Object
         SQL statement with SQL_ID 8fn8wsfvd344t.
         DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
         broken BOOLEAN := FALSE; BEGIN
         dbms_refresh.refresh('"SJGX_YB"."GRCBXX"'); :mydate := next_date; IF
         broken THEN :b := 1; ELSE :b := 0; END IF; END;

   Recommendation 6: SQL Tuning
   Estimated benefit is .05 active sessions, 3.61% of total activity.
   ------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "35vy818ghp687".
      Related Object
         SQL statement with SQL_ID 35vy818ghp687.
         SELECT
         lutt.name 姓名,
         lutt.idcard 身份證,
         '城鎮居民醫療保險' as 險種,
         lutt.calc_prd as 計算年月,
         lutt.curr_year 所屬期間,
         lutt.pay_money as 繳費基數,
         lutt.pay_money as 繳費金額,
         lutt.urban_type_name as 繳款型別,
         lutt.policy_item_name as 款項類別,
         Case
         When Nvl(lutt.busi_asg_no, 0) = 0 Then
         '未繳'
         When lutt.busi_asg_no In (-999, -998, -997, -981, -980) Then
         '未繳'
         Else
         '已繳'
         End as 繳費標誌,
         pt.pers_name as 人員類別,
         '個體' as 個體繳費標誌,
         bc.corp_name 繳費單位,
         bc.corp_code  單位編碼,
         lbr.reco_time 計算時間,
         to_char(lutt.fac_pay_date, 'yyyy-mm-dd') as 實際繳款時間,
         Case
         When Nvl(lutt.busi_asg_no, 0) = 0 Then
         '未注資'
         When lutt.busi_asg_no In (-999, -998, -997, -981, -980) Then
         '未注資'
         Else
         '已注資'
         End as 注資標誌,
         lbr.reco_staff 計算人
         FROM lv_urban_topay_tmp lutt,
         bs_corp bc,
         bs_person_type pt,
         /* lv_busi_bill lbb,*/
         lv_busi_record lbr,
         lv_busi_assign lba
         WHERE
         bc.corp_id=lutt.corp_id
         and pt.pers_type=lutt.pers_type and pt.center_id=lutt.center_id
         and lba.busi_asg_no(+)=lutt.busi_asg_no
         and lba.busi_reco_no=lbr.busi_reco_no(+)
         ORDER BY lutt.curr_year, lutt.src_type, lutt.policy_item_code
   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 "35vy818ghp687" was executed 1 times and had
      an average elapsed time of 427 seconds.
   Rationale
      I/O and Cluster wait for TABLE "SJGX_YB.LV_URBAN_TOPAY_TMP" with object
      ID 90256 consumed 100% of the database time spent on this SQL statement.


Finding 2: Top Segments by "User I/O" and "Cluster"
Impact is .17 active sessions, 13.43% of total activity.
--------------------------------------------------------
Individual database segments responsible for significant "User I/O" and
"Cluster" waits were found.

   Recommendation 1: Segment Tuning
   Estimated benefit is .12 active sessions, 9.1% of total activity.
   -----------------------------------------------------------------
   Action
      Investigate application logic involving I/O on TABLE "SJGX_YB.INTE_DATA"
      with object ID 89405.
      Related Object
         Database object with ID 89405.
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant I/O on this segment. For example, the INSERT statement with
      SQL_ID "c1fw0514uxxrs" is responsible for 100% of "User I/O" and
      "Cluster" waits for this segment.
   Rationale
      The I/O usage statistics for the object are: 1 full object scans, 263894
      physical reads, 414 physical writes and 0 direct reads.

   Recommendation 2: Segment Tuning
   Estimated benefit is .03 active sessions, 2.35% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate application logic involving I/O on TABLE
      "SJGX_YB.LV_URBAN_TOPAY_TMP" with object ID 90256.
      Related Object
         Database object with ID 90256.
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant I/O on this segment. For example, the SELECT statement with
      SQL_ID "35vy818ghp687" is responsible for 100% of "User I/O" and
      "Cluster" waits for this segment.
   Rationale
      The I/O usage statistics for the object are: 4 full object scans,
      1361966 physical reads, 682018 physical writes and 680863 direct reads.

   Recommendation 3: Segment Tuning
   Estimated benefit is .03 active sessions, 1.98% of total activity.
   ------------------------------------------------------------------
   Action
      Run "Segment Advisor" on TABLE "SJGX_YB.LV_INDIPAR" with object ID
      89473.
      Related Object
         Database object with ID 89473.
   Action
      Investigate application logic involving I/O on TABLE
      "SJGX_YB.LV_INDIPAR" with object ID 89473.
      Related Object
         Database object with ID 89473.
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant I/O on this segment. For example, the INSERT statement with
      SQL_ID "f64qufxuu0r5g" is responsible for 100% of "User I/O" and
      "Cluster" waits for this segment.
   Rationale
      The I/O usage statistics for the object are: 18 full object scans,
      36784620 physical reads, 0 physical writes and 0 direct reads.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "User I/O" was consuming significant database time.
      Impact is .25 active sessions, 19.52% of total activity.


Finding 3: Undersized Redo Log Buffer
Impact is .13 active sessions, 10.23% of total activity.
--------------------------------------------------------
Waits for redo log buffer space were consuming significant database time.

   Recommendation 1: Host Configuration
   Estimated benefit is .13 active sessions, 10.23% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the possibility of improving the performance of I/O to the
      online redo log files.
   Rationale
      The average size of writes to the online redo log files was 1590 K and
      the average time per write was 129 milliseconds.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Configuration" was consuming significant database time.
      Impact is .23 active sessions, 17.94% of total activity.


Finding 4: Log File Switches
Impact is .1 active sessions, 7.59% of total activity.
------------------------------------------------------
Log file switch operations were consuming significant database time while
waiting for checkpoint completion.
This problem can be caused by use of hot backup mode on tablespaces.  DML to
tablespaces in hot backup mode causes generation of additional redo.

   Recommendation 1: Database Configuration
   Estimated benefit is .1 active sessions, 7.59% of total activity.
   -----------------------------------------------------------------
   Action
      Verify whether incremental shipping was used for standby databases.

   Recommendation 2: Database Configuration
   Estimated benefit is .1 active sessions, 7.59% of total activity.
   -----------------------------------------------------------------
   Action
      Increase the size of the log files to 2048 M to hold at least 20 minutes
      of redo information.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Configuration" was consuming significant database time.
      Impact is .23 active sessions, 17.94% of total activity.


Finding 5: Buffer Busy - Hot Objects
Impact is .06 active sessions, 4.37% of total activity.
-------------------------------------------------------
Read and write contention on database blocks was consuming significant
database time.

   Recommendation 1: Schema Changes
   Estimated benefit is 0 active sessions, .35% of total activity.
   ---------------------------------------------------------------
   Action
      Consider using ORACLE's recommended solution of automatic segment space
      management in a locally managed tablespace for the tablespace "SYSTEM"
      containing the TABLE "SYS.AUD$" with object ID 407. Alternatively, you
      can move this object to a different tablespace that is locally managed
      with automatic segment space management.
      Related Object
         Database object with ID 407.
   Rationale
      There was significant read and write contention on TABLE "SYS.AUD$" with
      object ID 407.
      Related Object
         Database object with ID 407.

   Recommendation 2: Schema Changes
   Estimated benefit is 0 active sessions, .35% of total activity.
   ---------------------------------------------------------------
   Action
      Consider partitioning the TABLE "SYS.AUD$" with object ID 407 in a
      manner that will evenly distribute concurrent DML across multiple
      partitions.
      Related Object
         Database object with ID 407.

   Recommendation 3: Schema Changes
   Estimated benefit is 0 active sessions, .35% of total activity.
   ---------------------------------------------------------------
   Action
      A temporary solution may be achieved by increasing the number of free
      lists in segment "SYS.AUD$".
      Related Object
         Database object with ID 407.
   Action
      A temporary solution may be achieved by increasing the number of free
      list groups in segment "SYS.AUD$".
      Related Object
         Database object with ID 407.
   Rationale
      There was significant read and write contention on TABLE "SYS.AUD$" with
      object ID 407.
      Related Object
         Database object with ID 407.

   Symptoms That Led to the Finding:
   ---------------------------------
      Read and write contention on database blocks was consuming significant
      database time.
      Impact is .06 active sessions, 4.37% of total activity.
         Wait class "Concurrency" was consuming significant database time.
         Impact is .06 active sessions, 4.38% of total activity.


Finding 6: Commits and Rollbacks
Impact is .04 active sessions, 2.72% of total activity.
-------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.

   Recommendation 1: Host Configuration
   Estimated benefit is .04 active sessions, 2.72% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the possibility of improving the performance of I/O to the
      online redo log files.
   Rationale
      The average size of writes to the online redo log files was 1590 K and
      the average time per write was 129 milliseconds.
   Rationale
      The total I/O throughput on redo log files was 0 K per second for reads
      and 2.9 M per second for writes.
   Rationale
      The redo log I/O throughput was divided as follows: 0% by RMAN and
      recovery, 100% by Log Writer, 0% by Archiver, 0% by Streams AQ and 0% by
      all other activity.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Commit" was consuming significant database time.
      Impact is .04 active sessions, 2.72% of total activity.


Finding 7: "Network" Wait Class
Impact is .03 active sessions, 2.24% of total activity.
-------------------------------------------------------
Wait class "Network" was consuming significant database time.

   No recommendations are available.



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

          Additional Information
          ----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.