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.