ADDM Report for Task 'TASK_10078'
          ---------------------------------
 
Analysis Period
---------------
AWR snapshot range from 3237 to 3239.
Time period starts at 02-DEC-10 05.00.26 PM
Time period ends at 02-DEC-10 07.00.35 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 500 seconds.
The average number of active sessions was .07.
 
Summary of Findings
-------------------
   Description                 Active Sessions      Recommendations
                               Percent of Activity   
   --------------------------  -------------------  ---------------
1  Unusual "Other" Wait Event  .02 | 31.79          4
2  Top SQL by DB Time          .02 | 25.82          3
 
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 
          Findings and Recommendations
          ----------------------------
 
Finding 1: Unusual "Other" Wait Event
Impact is .02 active sessions, 31.79% 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, 31.79% 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.
 
   Recommendation 2: Application Analysis
   Estimated benefit is .02 active sessions, 31.79% 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, 16.96% 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, 14.84% 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, 84.22% of total activity.
 
 
Finding 2: Top SQL by DB Time
Impact is .02 active sessions, 25.82% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found.
 
   Recommendation 1: SQL Tuning
   Estimated benefit is .01 active sessions, 14.17% 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 1420 times and 
      had an average elapsed time of 0.049 seconds.
   Rationale
      At least one execution of the statement ran in parallel.
 
   Recommendation 2: SQL Tuning
   Estimated benefit is .01 active sessions, 9.09% 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 504 times and had 
      an average elapsed time of 0.087 seconds.
 
   Recommendation 3: SQL Tuning
   Estimated benefit is 0 active sessions, 2.84% 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 120 times and had 
      an average elapsed time of 0.15 seconds.
 
 
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
          Additional Information
          ----------------------
 
Instances that were analyzed:
-----------------------------
    Number  Name      Host Name    Active Sessions  Percent of Activity
    ------  --------  -----------  ---------------  -------------------
    1       SUSASTG1  stage-rac01  .03              50.32
    2       SUSASTG2  stage-rac02  .03              49.68
 
Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" 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.
Wait class "Network" was not consuming significant database time.
Wait class "User I/O" 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.