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.