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.