ADDM Report for Task 'TASK_10395'
          ---------------------------------
 
Analysis Period
---------------
AWR snapshot range from 3352 to 3355.
Time period starts at 07-DEC-10 12.00.50 PM
Time period ends at 07-DEC-10 03.00.04 PM
 
Analysis Target
---------------
Database 'SUSASTG' with DB ID 2407826999.
Database version 11.1.0.7.0.
ADDM performed an analysis of all instances.
 
Activity During the Analysis Period
-----------------------------------
Total database time was 1088 seconds.
The average number of active sessions was .1.
 
Summary of Findings
-------------------
   Description                   Active Sessions      Recommendations
                                 Percent of Activity   
   ----------------------------  -------------------  ---------------
1  Top SQL by "Cluster" Wait     .1 | 100             5
2  Top SQL by DB Time            .03 | 27.22          5
3  Unusual "Other" Wait Event    .02 | 24.47          4
4  Unusual "Network" Wait Event  0 | 3.89             1
5  Commits and Rollbacks         0 | 3.83             2
6  Interconnect Messaging        0 | 2.21             0
7  I/O Throughput                0 | 1.57             3
 
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 
          Findings and Recommendations
          ----------------------------
 
Finding 1: Top SQL by "Cluster" Wait
Impact is .1 active sessions, 100% of total activity.
-----------------------------------------------------
SQL statements responsible for significant inter-instance messaging were 
found.
 
   Recommendation 1: SQL Tuning
   Estimated benefit is .1 active sessions, 100% of total activity.
   ----------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "1jdwzzar077j1" for possible 
      performance improvements.
      Related Object
         SQL statement with SQL_ID 1jdwzzar077j1.
         SELECT COMPOSITE_KEY FROM MGMT_METRICS_COMPOSITE_KEYS WHERE 
         TARGET_GUID = :B7 AND KEY_PART1_VALUE = NVL(:B5 ,' ') AND 
         KEY_PART2_VALUE = NVL(:B4 ,' ') AND ((:B6 < 3 AND 
         NVL(KEY_PART3_VALUE,' ') = ' ') OR (:B6 >=3 AND KEY_PART3_VALUE = 
         NVL(:B3 ,' ') ) ) AND ((:B6 < 4 AND NVL(KEY_PART4_VALUE,' ') = ' ') 
         OR (:B6 >=4 AND KEY_PART4_VALUE = NVL(:B2 ,' ') ) ) AND ((:B6 < 5 AND 
         NVL(KEY_PART5_VALUE,' ') = ' ') OR (:B6 >=5 AND KEY_PART5_VALUE = 
         NVL(:B1 ,' ') ) ) 
   Rationale
      SQL statement with SQL_ID "1jdwzzar077j1" was executed 1144925509 times 
      and had an average elapsed time of 0.013 seconds.
   Rationale
      At least one execution of the statement ran in parallel.
   Rationale
      Average time spent in Cluster wait events per execution was 0.011 
      seconds.
 
   Recommendation 2: SQL Tuning
   Estimated benefit is .01 active sessions, 6.1% of total activity.
   -----------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "d5uz9k7utxukt".
      Related Object
         SQL statement with SQL_ID d5uz9k7utxukt.
         select decode(bitand(a.flags, 16384), 0, a.next_run_date,             
         a.last_enabled_time),         a.obj#, decode(bitand(a.flags, 16384), 
         0, 0, 1), a.sch_job  from  (select p.obj# obj#, p.flags flags, 
         p.next_run_date next_run_date,      p.job_status job_status, 
         p.class_oid class_oid,      p.last_enabled_time last_enabled_time, 
         p.instance_id instance_id,      1 sch_job   from sys.scheduler$_job p 
         UNION ALL   select q.obj#, q.flags, q.next_run_date, q.job_status, 
         q.class_oid,      q.last_enabled_time, q.instance_id, 1   from 
         sys.scheduler$_lightweight_job q   UNION ALL   select j.job, 0, 
         from_tz(cast(j.next_date as timestamp),      
         to_char(systimestamp,'TZH:TZM')), 1, NULL,      
         from_tz(cast(j.next_date as 
         timestamp),to_char(systimestamp,'TZH:TZM')),     NULL, 0   from 
         sys.job$ j   where (:1 = 1)     and (j.field1 is null or j.field1 = 
         0)     and j.job not in (select v.id2 from v$lock v where v.type = 
         'JQ')) a  where bitand(a.job_status, 3) = 1    and ((bitand(a.flags, 
         134217728 + 268435456) = 0) or         (bitand(a.job_status, 1024) <> 
         0))    and bitand(a.flags, 4096) = 0    and a.instance_id is NULL    
         and (a.class_oid is null      or (a.class_oid is not null      and 
         a.class_oid in (select b.obj# from sys.scheduler$_class b             
         where b.affinity is null)))    and decode(bitand(a.flags, 16384), 0, 
         a.next_run_date,               a.last_enabled_time) =  (select 
         min(decode(bitand(c.flags, 16384), 0, c.next_run_date,             
         c.last_enabled_time))   from   (select r.flags flags, r.next_run_date 
         next_run_date,      r.job_status job_status, r.class_oid class_oid,   
         r.last_enabled_time last_enabled_time, r.instance_id instance_id    
         from sys.scheduler$_job r    UNION ALL    select s.flags, 
         s.next_run_date, s.job_status, s.class_oid,      s.last_enabled_time, 
         s.instance_id    from sys.scheduler$_lightweight_job s    UNION ALL   
         select 0, from_tz(cast(k.next_date as timestamp),                     
         to_char(systimestamp,'TZH:TZM')), 1, NULL,      
         from_tz(cast(k.next_date as timestamp),            
         to_char(systimestamp,'TZH:TZM')), NULL     from sys.job$ k     where 
         (:2 = 1)       and (k.field1 is null or k.field1 = 0)       and k.job 
         not in (select w.id2 from v$lock w where w.type = 'JQ')) c   where 
         bitand(c.job_status, 3) = 1     and ((bitand(c.flags, 134217728 + 
         268435456) = 0) or          (bitand(c.job_status, 1024) <> 0))     
         and bitand(c.flags, 4096) = 0     and c.instance_id is NULL     and 
         (c.class_oid is null       or (c.class_oid is not null       and 
         c.class_oid in (select d.obj# from sys.scheduler$_class d             
         where d.affinity is null))))
   Rationale
      SQL statement with SQL_ID "d5uz9k7utxukt" was executed 749 times and had 
      an average elapsed time of 0.085 seconds.
   Rationale
      Average time spent in Cluster wait events per execution was 0.000097 
      seconds.
 
   Recommendation 3: SQL Tuning
   Estimated benefit is 0 active sessions, 2.32% of total activity.
   ----------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "b5pr3074667jx".
      Related Object
         SQL statement with SQL_ID b5pr3074667jx.
         select * from econsumer.skinid_users where given_name ='ck'
   Rationale
      SQL statement with SQL_ID "b5pr3074667jx" was executed 2 times and had 
      an average elapsed time of 12 seconds.
   Rationale
      Average time spent in Cluster wait events per execution was 0.00049 
      seconds.
 
   Recommendation 4: SQL Tuning
   Estimated benefit is 0 active sessions, .35% of total activity.
   ---------------------------------------------------------------
   Action
      Tune the PL/SQL block with SQL_ID "6gvch1xu9ca3g". Refer to the "Tuning 
      PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and 
      Reference".
      Related Object
         SQL statement with SQL_ID 6gvch1xu9ca3g.
         DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  
         broken BOOLEAN := FALSE; BEGIN 
         EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF 
         broken THEN :b := 1; ELSE :b := 0; END IF; END; 
   Rationale
      SQL statement with SQL_ID "6gvch1xu9ca3g" was executed 179 times and had 
      an average elapsed time of 0.25 seconds.
   Rationale
      Average time spent in Cluster wait events per execution was 0.021 
      seconds.
 
   Recommendation 5: SQL Tuning
   Estimated benefit is 0 active sessions, .01% of total activity.
   ---------------------------------------------------------------
   Action
      Tune the PL/SQL block with SQL_ID "2b064ybzkwf1y". Refer to the "Tuning 
      PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and 
      Reference".
      Related Object
         SQL statement with SQL_ID 2b064ybzkwf1y.
         BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
   Rationale
      SQL statement with SQL_ID "2b064ybzkwf1y" was executed 358 times and had 
      an average elapsed time of 0.079 seconds.
   Rationale
      Average time spent in Cluster wait events per execution was 0.00031 
      seconds.
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Cluster" was consuming significant database time.
      Impact is 0 active sessions, 2.22% of total activity.
 
 
Finding 2: Top SQL by DB Time
Impact is .03 active sessions, 27.22% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found.
 
   Recommendation 1: SQL Tuning
   Estimated benefit is .01 active sessions, 11.48% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "7frqszzdu8sgg" for possible 
      performance improvements.
      Related Object
         SQL statement with SQL_ID 7frqszzdu8sgg.
         select DECODE(UPPER(d.OPEN_MODE),'READ WRITE','R','N') 
         ,DECODE(UPPER(i.STATUS),'OPEN','O','N') into :b0,:b1  from v$database 
         d ,gv$instance i where i.INSTANCE_NAME=:b2
   Rationale
      SQL statement with SQL_ID "7frqszzdu8sgg" was executed 2117 times and 
      had an average elapsed time of 0.058 seconds.
   Rationale
      At least one execution of the statement ran in parallel.
   Rationale
      Waiting for event "reliable message" in wait class "Other" accounted for 
      46% of the database time spent in processing the SQL statement with 
      SQL_ID "7frqszzdu8sgg".
 
   Recommendation 2: SQL Tuning
   Estimated benefit is .01 active sessions, 6.1% of total activity.
   -----------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "d5uz9k7utxukt".
      Related Object
         SQL statement with SQL_ID d5uz9k7utxukt.
         select decode(bitand(a.flags, 16384), 0, a.next_run_date,             
         a.last_enabled_time),         a.obj#, decode(bitand(a.flags, 16384), 
         0, 0, 1), a.sch_job  from  (select p.obj# obj#, p.flags flags, 
         p.next_run_date next_run_date,      p.job_status job_status, 
         p.class_oid class_oid,      p.last_enabled_time last_enabled_time, 
         p.instance_id instance_id,      1 sch_job   from sys.scheduler$_job p 
         UNION ALL   select q.obj#, q.flags, q.next_run_date, q.job_status, 
         q.class_oid,      q.last_enabled_time, q.instance_id, 1   from 
         sys.scheduler$_lightweight_job q   UNION ALL   select j.job, 0, 
         from_tz(cast(j.next_date as timestamp),      
         to_char(systimestamp,'TZH:TZM')), 1, NULL,      
         from_tz(cast(j.next_date as 
         timestamp),to_char(systimestamp,'TZH:TZM')),     NULL, 0   from 
         sys.job$ j   where (:1 = 1)     and (j.field1 is null or j.field1 = 
         0)     and j.job not in (select v.id2 from v$lock v where v.type = 
         'JQ')) a  where bitand(a.job_status, 3) = 1    and ((bitand(a.flags, 
         134217728 + 268435456) = 0) or         (bitand(a.job_status, 1024) <> 
         0))    and bitand(a.flags, 4096) = 0    and a.instance_id is NULL    
         and (a.class_oid is null      or (a.class_oid is not null      and 
         a.class_oid in (select b.obj# from sys.scheduler$_class b             
         where b.affinity is null)))    and decode(bitand(a.flags, 16384), 0, 
         a.next_run_date,               a.last_enabled_time) =  (select 
         min(decode(bitand(c.flags, 16384), 0, c.next_run_date,             
         c.last_enabled_time))   from   (select r.flags flags, r.next_run_date 
         next_run_date,      r.job_status job_status, r.class_oid class_oid,   
         r.last_enabled_time last_enabled_time, r.instance_id instance_id    
         from sys.scheduler$_job r    UNION ALL    select s.flags, 
         s.next_run_date, s.job_status, s.class_oid,      s.last_enabled_time, 
         s.instance_id    from sys.scheduler$_lightweight_job s    UNION ALL   
         select 0, from_tz(cast(k.next_date as timestamp),                     
         to_char(systimestamp,'TZH:TZM')), 1, NULL,      
         from_tz(cast(k.next_date as timestamp),            
         to_char(systimestamp,'TZH:TZM')), NULL     from sys.job$ k     where 
         (:2 = 1)       and (k.field1 is null or k.field1 = 0)       and k.job 
         not in (select w.id2 from v$lock w where w.type = 'JQ')) c   where 
         bitand(c.job_status, 3) = 1     and ((bitand(c.flags, 134217728 + 
         268435456) = 0) or          (bitand(c.job_status, 1024) <> 0))     
         and bitand(c.flags, 4096) = 0     and c.instance_id is NULL     and 
         (c.class_oid is null       or (c.class_oid is not null       and 
         c.class_oid in (select d.obj# from sys.scheduler$_class d             
         where d.affinity is null))))
   Rationale
      SQL statement with SQL_ID "d5uz9k7utxukt" was executed 749 times and had 
      an average elapsed time of 0.085 seconds.
 
   Recommendation 3: SQL Tuning
   Estimated benefit is 0 active sessions, 3.94% of total activity.
   ----------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "ds094pf4fk1qr" for possible 
      performance improvements.
      Related Object
         SQL statement with SQL_ID ds094pf4fk1qr.
         Select   owner owner, synonym_name, table_owner, table_name
         FROM SYS.ALL_SYNONYMS
         WHERE synonym_name IS NOT NULL
         AND owner = :owner
         ORDER BY 1
   Rationale
      SQL statement with SQL_ID "ds094pf4fk1qr" was executed 1 times and had 
      an average elapsed time of 42 seconds.
 
   Recommendation 4: SQL Tuning
   Estimated benefit is 0 active sessions, 3.61% of total activity.
   ----------------------------------------------------------------
   Action
      Tune the PL/SQL block with SQL_ID "6gvch1xu9ca3g". Refer to the "Tuning 
      PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and 
      Reference".
      Related Object
         SQL statement with SQL_ID 6gvch1xu9ca3g.
         DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  
         broken BOOLEAN := FALSE; BEGIN 
         EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF 
         broken THEN :b := 1; ELSE :b := 0; END IF; END; 
   Rationale
      SQL statement with SQL_ID "6gvch1xu9ca3g" was executed 179 times and had 
      an average elapsed time of 0.25 seconds.
 
   Recommendation 5: SQL Tuning
   Estimated benefit is 0 active sessions, 2.32% of total activity.
   ----------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SQL statement with SQL_ID "b5pr3074667jx".
      Related Object
         SQL statement with SQL_ID b5pr3074667jx.
         select * from econsumer.skinid_users where given_name ='ck'
   Rationale
      SQL statement with SQL_ID "b5pr3074667jx" was executed 2 times and had 
      an average elapsed time of 12 seconds.
 
 
Finding 3: Unusual "Other" Wait Event
Impact is .02 active sessions, 24.47% of total activity.
--------------------------------------------------------
Wait event "reliable message" in wait class "Other" was consuming significant 
database time.
 
   Recommendation 1: Application Analysis
   Estimated benefit is .02 active sessions, 24.47% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the cause for high "reliable message" waits. Refer to 
      Oracle's "Database Reference" for the description of this wait event. 
      Use given SQL for further investigation.
   Rationale
      The SQL statement with SQL_ID "7frqszzdu8sgg" was found waiting for 
      "reliable message" wait event.
      Related Object
         SQL statement with SQL_ID 7frqszzdu8sgg.
         select DECODE(UPPER(d.OPEN_MODE),'READ WRITE','R','N') 
         ,DECODE(UPPER(i.STATUS),'OPEN','O','N') into :b0,:b1  from v$database 
         d ,gv$instance i where i.INSTANCE_NAME=:b2
 
   Recommendation 2: Application Analysis
   Estimated benefit is .02 active sessions, 24.47% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the cause for high "reliable message" waits in Service 
      "SYS$USERS".
 
   Recommendation 3: Application Analysis
   Estimated benefit is .01 active sessions, 11.75% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the cause for high "reliable message" waits in Module 
      "racgimon@stage-rac02 (TNS V1-V3)".
 
   Recommendation 4: Application Analysis
   Estimated benefit is .01 active sessions, 9.79% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the cause for high "reliable message" waits in Module 
      "racgimon@stage-rac01 (TNS V1-V3)".
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Other" was consuming significant database time.
      Impact is .06 active sessions, 63.24% of total activity.
 
 
Finding 4: Unusual "Network" Wait Event
Impact is 0 active sessions, 3.89% of total activity.
-----------------------------------------------------
Wait event "SQL*Net more data to client" in wait class "Network" was consuming 
significant database time.
 
   Recommendation 1: Application Analysis
   Estimated benefit is 0 active sessions, 3.89% of total activity.
   ----------------------------------------------------------------
   Action
      Investigate the cause for high "SQL*Net more data to client" waits. 
      Refer to Oracle's "Database Reference" for the description of this wait 
      event.
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Network" was consuming significant database time.
      Impact is 0 active sessions, 3.92% of total activity.
 
 
Finding 5: Commits and Rollbacks
Impact is 0 active sessions, 3.83% of total activity.
-----------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations 
were consuming significant database time.
 
Instances that were significantly affected by this finding:
    Number  Name      Percent Impact  ADDM Task Name
    ------  --------  --------------  --------------
    2       SUSASTG2  62.37           TASK_10395$2
    1       SUSASTG1  37.63           TASK_10395$1
 
   Recommendation 1: Application Analysis
   Estimated benefit is 0 active sessions, 3.83% of total activity.
   ----------------------------------------------------------------
   Action
      Investigate application logic for possible reduction in the number of 
      COMMIT operations by increasing the size of transactions.
   Rationale
      The application was performing 63 transactions per minute with an 
      average redo size of 4693 bytes per transaction.
 
   Recommendation 2: Host Configuration
   Estimated benefit is 0 active sessions, 3.83% 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 4 K and the 
      average time per write was 3 milliseconds.
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Commit" was consuming significant database time.
      Impact is 0 active sessions, 3.82% of total activity.
 
 
Finding 6: Interconnect Messaging
Impact is 0 active sessions, 2.21% of total activity.
-----------------------------------------------------
Inter-instance messaging was consuming significant database time.
The network latency of the cluster interconnect was within acceptable limits 
of 1 milliseconds.
Read and write contention on database blocks was not consuming significant 
database time in the cluster.
Global Cache Service Processes (LMSn) were performing within acceptable limits 
of 1 milliseconds.
Waits on "buffer busy" events were not consuming significant database time.
 
Instances that were significantly affected by this finding:
    Number  Name      Percent Impact  ADDM Task Name
    ------  --------  --------------  --------------
    1       SUSASTG1  72.9            TASK_10395$1
 
   No recommendations are available.
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Cluster" was consuming significant database time.
      Impact is 0 active sessions, 2.22% of total activity.
 
 
Finding 7: I/O Throughput
Impact is 0 active sessions, 1.57% of total activity.
-----------------------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
 
Instances that were significantly affected by this finding:
    Number  Name      Percent Impact  ADDM Task Name
    ------  --------  --------------  --------------
    1       SUSASTG1  52.12           TASK_10395$1
 
   Recommendation 1: Host Configuration
   Estimated benefit is 0 active sessions, 1.57% of total activity.
   ----------------------------------------------------------------
   Action
      Consider increasing the throughput of the I/O subsystem. Oracle's 
      recommended solution is to stripe all data file using the SAME 
      methodology. You might also need to increase the number of disks for 
      better performance. Alternatively, consider using Oracle's Automatic 
      Storage Management solution.
   Rationale
      During the analysis period, the average data files' I/O throughput was 
      106 K per second for reads and 20 K per second for writes. The average 
      response time for single block reads was 19 milliseconds.
 
   Recommendation 2: Host Configuration
   Estimated benefit is 0 active sessions, 1.1% of total activity.
   ---------------------------------------------------------------
   Action
      The performance of file +SUSASTG/susastg/s_econsumer_d.dbf was 
      significantly worse than other files. If striping all files using the 
      SAME methodology is not possible, consider striping this file over 
      multiple disks.
      Related Object
         Database file
         "+SUSASTG/susastg/s_econsumer_d.dbf"
   Rationale
      The average response time for single block reads for this file was 30 
      milliseconds.
 
   Recommendation 3: Host Configuration
   Estimated benefit is 0 active sessions, .64% of total activity.
   ---------------------------------------------------------------
   Action
      The performance of file +SUSASTG/susastg/system01.dbf was significantly 
      worse than other files. If striping all files using the SAME methodology 
      is not possible, consider striping this file over multiple disks.
      Related Object
         Database file
         "+SUSASTG/susastg/system01.dbf"
   Rationale
      The average response time for single block reads for this file was 49 
      milliseconds.
 
   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "User I/O" was consuming significant database time.
      Impact is 0 active sessions, 3.74% of total activity.
 
 
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
          Additional Information
          ----------------------
 
Instances that were analyzed:
-----------------------------
    Number  Name      Host Name    Active Sessions  Percent of Activity
    ------  --------  -----------  ---------------  -------------------
    2       SUSASTG2  stage-rac02  .05              52.07
    1       SUSASTG1  stage-rac01  .05              47.93
 
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
The network latency of the cluster interconnect was within acceptable limits 
of 1 milliseconds.
Session connect and disconnect calls were not consuming significant database 
time.