DETAILED ADDM REPORT FOR TASK 'TASK_26297' WITH ID 26297
          --------------------------------------------------------
 
              Analysis Period: from 26-MAR-2011 00:00 to 27-MAR-2011 23:00
         Database ID/Instance: 1860772457/1
      Database/Instance Names: SKINSTG/SKINSTG1
                    Host Name: stage-rac01
             Database Version: 10.2.0.4.0
               Snapshot Range: from 12835 to 12882
                Database Time: 609 seconds
        Average Database Load: 0 active sessions
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 
FINDING 1: 100% impact (896 seconds)
------------------------------------
Wait event "Streams AQ: qmn coordinator waiting for slave to start" in wait 
class "Other" was consuming significant database time.
 
   RECOMMENDATION 1: Application Analysis, 100% benefit (896 seconds)
      ACTION: Investigate the cause for high "Streams AQ: qmn coordinator 
         waiting for slave to start" waits. Refer to Oracle's "Database 
         Reference" for the description of this wait event.
 
   RECOMMENDATION 2: Application Analysis, 100% benefit (896 seconds)
      ACTION: Investigate the cause for high "Streams AQ: qmn coordinator 
         waiting for slave to start" waits in Service "SYS$BACKGROUND".
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Wait class "Other" was consuming significant database time. 
               (100% impact [1753 seconds])
 
FINDING 2: 100% impact (673 seconds)
------------------------------------
Wait event "reliable message" in wait class "Other" was consuming significant 
database time.
 
   RECOMMENDATION 1: Application Analysis, 100% benefit (673 seconds)
      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 "4gd6b1r53yt88" was found 
         waiting for "reliable message" wait event.
         RELEVANT OBJECT: SQL statement with SQL_ID 4gd6b1r53yt88
         
 
   RECOMMENDATION 2: Application Analysis, 100% benefit (673 seconds)
      ACTION: Investigate the cause for high "reliable message" waits in 
         Service "SYS$BACKGROUND".
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Wait class "Other" was consuming significant database time. 
               (100% impact [1753 seconds])
 
FINDING 3: 29% impact (176 seconds)
-----------------------------------
SQL statements consuming significant database time were found.
 
   RECOMMENDATION 1: SQL Tuning, 9.3% benefit (57 seconds)
      ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for 
         possible performance improvements.
         RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3
         call dbms_stats.gather_database_stats_job_proc (  )
      RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 1 
         times and had an average elapsed time of 56 seconds.
 
   RECOMMENDATION 2: SQL Tuning, 7.4% benefit (45 seconds)
      ACTION: Investigate the SQL statement with SQL_ID "7vgmvmy8vvb9s" for 
         possible performance improvements.
         RELEVANT OBJECT: SQL statement with SQL_ID 7vgmvmy8vvb9s and 
         PLAN_HASH 43914496
         insert into wrh$_tempstatxs   (snap_id, dbid, instance_number, file#, 
         creation_change#, phyrds,    phywrts, singleblkrds, readtim, 
         writetim, singleblkrdtim, phyblkrd,    phyblkwrt, wait_count, time)  
         select    :snap_id, :dbid, :instance_number,    tf.tfnum, 
         to_number(tf.tfcrc_scn) creation_change#,    ts.kcftiopyr, 
         ts.kcftiopyw, ts.kcftiosbr, ts.kcftioprt, ts.kcftiopwt,    
         ts.kcftiosbt, ts.kcftiopbr, ts.kcftiopbw, fw.count, fw.time  from    
         x$kcftio ts, x$kcctf tf, x$kcbfwait fw  where    tf.tfdup != 0 and    
         tf.tfnum  = ts.kcftiofno and    fw.indx+1 = (ts.kcftiofno + 
         :db_files)
      RATIONALE: SQL statement with SQL_ID "7vgmvmy8vvb9s" was executed 47 
         times and had an average elapsed time of 0.96 seconds.
 
   RECOMMENDATION 3: SQL Tuning, 7.4% benefit (45 seconds)
      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"
         RELEVANT 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 1437 
         times and had an average elapsed time of 0.037 seconds.
 
   RECOMMENDATION 4: SQL Tuning, 5.1% benefit (31 seconds)
      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID 
         "bunssq950snhf".
         RELEVANT OBJECT: SQL statement with SQL_ID bunssq950snhf and 
         PLAN_HASH 2694099131
         insert into wrh$_sga_target_advice   (snap_id, dbid, instance_number, 
         SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS)  select 
         :snap_id, :dbid, :instance_number,    SGA_SIZE, SGA_SIZE_FACTOR, 
         ESTD_DB_TIME, ESTD_PHYSICAL_READS  from    v$sga_target_advice
      RATIONALE: SQL statement with SQL_ID "bunssq950snhf" was executed 47 
         times and had an average elapsed time of 0.67 seconds.
 
   RECOMMENDATION 5: SQL Tuning, 2.1% benefit (13 seconds)
      ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID 
         "8vkjh60z1g287".
         RELEVANT OBJECT: SQL statement with SQL_ID 8vkjh60z1g287 and 
         PLAN_HASH 2584193492
         select OBJOID,  CLSOID, RUNTIME, PRI, JOBTYPE,  SCHLIM,  WT, INST,  
         RUNNOW, ENQ_SCHLIM from ( select a.obj# OBJOID, a.class_oid CLSOID,   
         decode(bitand(a.flags, 16384), 0, a.next_run_date, 
         a.last_enabled_time) RUNTIME,    (2*a.priority +     
         decode(bitand(a.job_status, 4), 0, 0,            
         decode(a.running_instance, :1, -1, 1))) PRI,    1 JOBTYPE, 
         a.schedule_limit SCHLIM, a.job_weight WT,    
         decode(a.running_instance, NULL, 0, a.running_instance) INST,    
         decode(bitand(a.flags, 16384), 0, 0, 1) RUNNOW,    
         decode(bitand(a.job_status, 8388608), 0, 0, 1) ENQ_SCHLIM  from 
         sys.scheduler$_job a, v$instance i   where bitand(a.job_status, 515) 
         = 1    and ( i.logins = 'ALLOWED' or bitand(a.flags, 2147483648) <> 0 
         )    and ((bitand(a.flags, 134217728 + 268435456) = 0) or         
         (bitand(a.job_status, 1024) <> 0))    and bitand(a.flags, 4096) = 0   
         and (a.next_run_date <= :2       or bitand(a.flags, 16384) <> 0)    
         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))) union all  select c.obj#, 0, 
         c.next_start_date, 0, 2, c.duration, 1, 0, 0, 0  from 
         sys.scheduler$_window c,  v$instance i  where bitand(c.flags, 1) <> 0 
         and bitand(c.flags, 2) = 0    and bitand(c.flags, 64) = 0    and 
         c.next_start_date <= :4    and c.next_start_date + c.duration > :5    
         and i.logins = 'ALLOWED' union all  select d.obj#, 0, 
         d.next_start_date + d.duration,         0, 3, 
         numtodsinterval(0,'minute'), 1, 0, 0, 0  from sys.scheduler$_window 
         d, v$instance i  where bitand(d.flags, 1) <> 0    and bitand(d.flags, 
         2) = 0    and bitand(d.flags, 64) = 0    and d.next_start_date <= :6  
         and d.next_start_date + d.duration > :7    and i.logins = 'ALLOWED' 
         union all  select f.obj#, 0, e.attr_tstamp, 0,         
         decode(bitand(e.flags, 131072), 0, 2, 4),         e.attr_intv, 1, 0, 
         0, 0  from sys.scheduler$_global_attribute e, sys.obj$ f,       
         sys.obj$ g, v$instance i  where e.obj# = g.obj#    and g.name = 
         'CURRENT_OPEN_WINDOW'    and e.value = f.name    and e.attr_tstamp is 
         not null    and e.attr_intv is not null    and i.logins = 'ALLOWED' 
         union all  select i.obj#, 0, h.attr_tstamp + h.attr_intv, 0,         
         decode(bitand(h.flags, 131072), 0, 3, 5),         numtodsinterval(0, 
         'minute'), 1, 0, 0, 0  from sys.scheduler$_global_attribute h, 
         sys.obj$ i,         sys.obj$ j, v$instance ik  where h.obj# = j.obj#  
         and ik.logins = 'ALLOWED'    and j.name = 'CURRENT_OPEN_WINDOW'    
         and h.value = i.name    and h.attr_tstamp is not null    and 
         h.attr_intv is not null) order by RUNTIME, CLSOID, PRI, WT DESC, 
         OBJOID
      RATIONALE: SQL statement with SQL_ID "8vkjh60z1g287" was executed 123 
         times and had an average elapsed time of 0.1 seconds.
 
   RECOMMENDATION 6: SQL Tuning, 1.7% benefit (10 seconds)
      ACTION: Use bigger fetch arrays while fetching results from the SELECT 
         statement with SQL_ID "8vkjh60z1g287".
         RELEVANT OBJECT: SQL statement with SQL_ID 8vkjh60z1g287 and 
         PLAN_HASH 2584193492
         select OBJOID,  CLSOID, RUNTIME, PRI, JOBTYPE,  SCHLIM,  WT, INST,  
         RUNNOW, ENQ_SCHLIM from ( select a.obj# OBJOID, a.class_oid CLSOID,   
         decode(bitand(a.flags, 16384), 0, a.next_run_date, 
         a.last_enabled_time) RUNTIME,    (2*a.priority +     
         decode(bitand(a.job_status, 4), 0, 0,            
         decode(a.running_instance, :1, -1, 1))) PRI,    1 JOBTYPE, 
         a.schedule_limit SCHLIM, a.job_weight WT,    
         decode(a.running_instance, NULL, 0, a.running_instance) INST,    
         decode(bitand(a.flags, 16384), 0, 0, 1) RUNNOW,    
         decode(bitand(a.job_status, 8388608), 0, 0, 1) ENQ_SCHLIM  from 
         sys.scheduler$_job a, v$instance i   where bitand(a.job_status, 515) 
         = 1    and ( i.logins = 'ALLOWED' or bitand(a.flags, 2147483648) <> 0 
         )    and ((bitand(a.flags, 134217728 + 268435456) = 0) or         
         (bitand(a.job_status, 1024) <> 0))    and bitand(a.flags, 4096) = 0   
         and (a.next_run_date <= :2       or bitand(a.flags, 16384) <> 0)    
         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))) union all  select c.obj#, 0, 
         c.next_start_date, 0, 2, c.duration, 1, 0, 0, 0  from 
         sys.scheduler$_window c,  v$instance i  where bitand(c.flags, 1) <> 0 
         and bitand(c.flags, 2) = 0    and bitand(c.flags, 64) = 0    and 
         c.next_start_date <= :4    and c.next_start_date + c.duration > :5    
         and i.logins = 'ALLOWED' union all  select d.obj#, 0, 
         d.next_start_date + d.duration,         0, 3, 
         numtodsinterval(0,'minute'), 1, 0, 0, 0  from sys.scheduler$_window 
         d, v$instance i  where bitand(d.flags, 1) <> 0    and bitand(d.flags, 
         2) = 0    and bitand(d.flags, 64) = 0    and d.next_start_date <= :6  
         and d.next_start_date + d.duration > :7    and i.logins = 'ALLOWED' 
         union all  select f.obj#, 0, e.attr_tstamp, 0,         
         decode(bitand(e.flags, 131072), 0, 2, 4),         e.attr_intv, 1, 0, 
         0, 0  from sys.scheduler$_global_attribute e, sys.obj$ f,       
         sys.obj$ g, v$instance i  where e.obj# = g.obj#    and g.name = 
         'CURRENT_OPEN_WINDOW'    and e.value = f.name    and e.attr_tstamp is 
         not null    and e.attr_intv is not null    and i.logins = 'ALLOWED' 
         union all  select i.obj#, 0, h.attr_tstamp + h.attr_intv, 0,         
         decode(bitand(h.flags, 131072), 0, 3, 5),         numtodsinterval(0, 
         'minute'), 1, 0, 0, 0  from sys.scheduler$_global_attribute h, 
         sys.obj$ i,         sys.obj$ j, v$instance ik  where h.obj# = j.obj#  
         and ik.logins = 'ALLOWED'    and j.name = 'CURRENT_OPEN_WINDOW'    
         and h.value = i.name    and h.attr_tstamp is not null    and 
         h.attr_intv is not null) order by RUNTIME, CLSOID, PRI, WT DESC, 
         OBJOID
 
FINDING 4: 9.3% impact (57 seconds)
-----------------------------------
Read and write contention on database blocks was consuming significant 
database time. However, no single object was the predominant cause for this 
contention.
 
   NO RECOMMENDATIONS AVAILABLE
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Inter-instance messaging was consuming significant database 
               time on this instance. (15% impact [91 seconds])
         SYMPTOM: Wait class "Cluster" was consuming significant database 
                  time. (15% impact [92 seconds])
      SYMPTOM: Wait class "Concurrency" was consuming significant database 
               time. (4.6% impact [28 seconds])
 
FINDING 5: 9% impact (55 seconds)
---------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations 
were consuming significant database time.
 
   RECOMMENDATION 1: Application Analysis, 9% benefit (55 seconds)
      ACTION: Investigate application logic for possible reduction in the 
         number of COMMIT operations by increasing the size of transactions.
      RATIONALE: The application was performing 70 transactions per minute 
         with an average redo size of 663 bytes per transaction.
 
   RECOMMENDATION 2: Host Configuration, 9% benefit (55 seconds)
      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 3 
         K and the average time per write was 3 milliseconds.
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Wait class "Commit" was consuming significant database time. 
               (9% impact [55 seconds])
 
FINDING 6: 8.6% impact (52 seconds)
-----------------------------------
Wait class "User I/O" was consuming significant database time.
 
   NO RECOMMENDATIONS AVAILABLE
 
   ADDITIONAL INFORMATION:
      Waits for I/O to temporary tablespaces were not consuming significant 
      database time.
      The throughput of the I/O subsystem was not significantly lower than 
      expected.
      The SGA was adequately sized.
 
FINDING 7: 8.2% impact (50 seconds)
-----------------------------------
PL/SQL execution consumed significant database time.
 
   RECOMMENDATION 1: SQL Tuning, 9.3% benefit (57 seconds)
      ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for 
         possible performance improvements.
         RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3
         call dbms_stats.gather_database_stats_job_proc (  )
      RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 1 
         times and had an average elapsed time of 56 seconds.
      RATIONALE: Average time spent in PL/SQL execution was 15 seconds.
 
   RECOMMENDATION 2: SQL Tuning, 5.8% benefit (35 seconds)
      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"
         RELEVANT 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 5602 
         times and had an average elapsed time of 0.0064 seconds.
      RATIONALE: Average time spent in PL/SQL execution was 0.0062 seconds.
 
FINDING 8: 4.6% impact (28 seconds)
-----------------------------------
Wait event "CGS wait for IPC msg" in wait class "Other" was consuming 
significant database time.
 
   RECOMMENDATION 1: Application Analysis, 4.6% benefit (28 seconds)
      ACTION: Investigate the cause for high "CGS wait for IPC msg" waits. 
         Refer to Oracle's "Database Reference" for the description of this 
         wait event.
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Wait class "Other" was consuming significant database time. 
               (100% impact [1753 seconds])
 
FINDING 9: 4% impact (24 seconds)
---------------------------------
Wait event "log switch/archive" in wait class "Other" was consuming 
significant database time.
 
   RECOMMENDATION 1: Application Analysis, 4% benefit (24 seconds)
      ACTION: Investigate the cause for high "log switch/archive" waits. Refer 
         to Oracle's "Database Reference" for the description of this wait 
         event.
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Wait class "Other" was consuming significant database time. 
               (100% impact [1753 seconds])
 
FINDING 10: 3.8% impact (23 seconds)
------------------------------------
Wait event "enq: WF - contention" in wait class "Other" was consuming 
significant database time.
 
   RECOMMENDATION 1: Application Analysis, 3.8% benefit (23 seconds)
      ACTION: Investigate the cause for high "enq: WF - contention" waits. 
         Refer to Oracle's "Database Reference" for the description of this 
         wait event.
 
   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Wait class "Other" was consuming significant database time. 
               (100% impact [1753 seconds])
 
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
          ADDITIONAL INFORMATION
          ----------------------
 
Wait class "Application" 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.
Hard parsing of SQL statements was not consuming significant database time.
 
The database's maintenance windows were active during 99% of the analysis 
period.
 
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.