DETAILED ADDM REPORT FOR TASK 'TASK_143407' WITH ID 143407
----------------------------------------------------------
Analysis Period: 06-JUN-2011 from 11:00:18 to 12:10:22
Database ID/Instance: 4015448600/1
Database/Instance Names: HLT1/hlt11
Host Name: prod-rac21.wp.cl.datapipe.net
Database Version: 10.2.0.5.0
Snapshot Range: from 28009 to 28011
Database Time: 273383 seconds
Average Database Load: 65 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 100% impact (273383 seconds)
---------------------------------------
Significant virtual memory paging was detected on the host operating system.
RECOMMENDATION 1: Host Configuration, 100% benefit (273383 seconds)
ACTION: Host operating system was experiencing significant paging but no
particular root cause could be detected. Investigate processes that
do not belong to this instance running on the host that are consuming
significant amount of virtual memory. Also consider adding more
physical memory to the host.
RECOMMENDATION 2: DB Configuration, 100% benefit (273383 seconds)
ACTION: Total PGA memory used was above the specified
"pga_aggregate_target" value for 30% of the time. Consider increasing
your current "pga_aggregate_target". Note that the sum of
"pga_aggregate_target" and SGA size should be less than the physical
memory available on the host.
RATIONALE: The value of parameter "pga_aggregate_target" was "2000 M"
during the analysis period.
FINDING 2: 85% impact (231754 seconds)
--------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 43% benefit (118654 seconds)
ACTION: Investigate the SQL statement with SQL_ID "7bd3r84urtm8u" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 7bd3r84urtm8u and
PLAN_HASH 607575284
select LASTSEQID from ParticIDKeeper for update
RATIONALE: SQL statement with SQL_ID "7bd3r84urtm8u" was executed 9262
times and had an average elapsed time of 12 seconds.
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 99% of the database time spent in
processing the SQL statement with SQL_ID "7bd3r84urtm8u".
RECOMMENDATION 2: SQL Tuning, 39% benefit (106191 seconds)
ACTION: Investigate the SQL statement with SQL_ID "cpw5amz5aa8hy" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID cpw5amz5aa8hy and
PLAN_HASH 1780642097
update Authorization set curcount = curcount+:"SYS_B_0" where authcd
= :1
RATIONALE: SQL statement with SQL_ID "cpw5amz5aa8hy" was executed 9358
times and had an average elapsed time of 11 seconds.
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 99% of the database time spent in
processing the SQL statement with SQL_ID "cpw5amz5aa8hy".
RECOMMENDATION 3: SQL Tuning, 2.8% benefit (7751 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"7j4jahj2b0n0w".
RELEVANT OBJECT: SQL statement with SQL_ID 7j4jahj2b0n0w
insert into SystemLog ( ResultDT ,JobCD, ParticipantID, EpisodeID,
ResultCD, ResulTX) values (:1,:2,:3,:4,:5,:6)
ACTION: Investigate the SQL statement with SQL_ID "7j4jahj2b0n0w" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 7j4jahj2b0n0w
insert into SystemLog ( ResultDT ,JobCD, ParticipantID, EpisodeID,
ResultCD, ResulTX) values (:1,:2,:3,:4,:5,:6)
RATIONALE: SQL statement with SQL_ID "7j4jahj2b0n0w" was executed 84610
times and had an average elapsed time of 0.091 seconds.
RATIONALE: Waiting for event "gc buffer busy" in wait class "Cluster"
accounted for 37% of the database time spent in processing the SQL
statement with SQL_ID "7j4jahj2b0n0w".
RATIONALE: Waiting for event "enq: TX - contention" in wait class
"Other" accounted for 19% of the database time spent in processing
the SQL statement with SQL_ID "7j4jahj2b0n0w".
RATIONALE: Waiting for event "enq: TX - index contention" in wait class
"Concurrency" accounted for 12% of the database time spent in
processing the SQL statement with SQL_ID "7j4jahj2b0n0w".
FINDING 3: 81% impact (222456 seconds)
--------------------------------------
SQL statements were found waiting for row lock waits.
RECOMMENDATION 1: Application Analysis, 43% benefit (117563 seconds)
ACTION: Significant row contention was detected in the TABLE
"VHBA.PARTICIDKEEPER" with object id 81183. Trace the cause of row
contention in the application logic using the given blocked SQL.
RELEVANT OBJECT: database object with id 81183
RATIONALE: The SQL statement with SQL_ID "7bd3r84urtm8u" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID 7bd3r84urtm8u
select LASTSEQID from ParticIDKeeper for update
RATIONALE: Sessions with Service "hlt1" and Module "JDBC Thin Client"
were the blocking sessions responsible for 52% of this
recommendation's benefit.
RECOMMENDATION 2: Application Analysis, 38% benefit (104892 seconds)
ACTION: Significant row contention was detected in the TABLE
"VHBA.AUTHORIZATION" with object id 80572. Trace the cause of row
contention in the application logic using the given blocked SQL.
RELEVANT OBJECT: database object with id 80572
RATIONALE: The SQL statement with SQL_ID "cpw5amz5aa8hy" was blocked on
row locks.
RELEVANT OBJECT: SQL statement with SQL_ID cpw5amz5aa8hy
update Authorization set curcount = curcount+:"SYS_B_0" where authcd
= :1
RATIONALE: Sessions with Service "hlt1" and Module "JDBC Thin Client"
were the blocking sessions responsible for 67% of this
recommendation's benefit.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Application" was consuming significant database
time. (81% impact [222464 seconds])
FINDING 4: 9% impact (24540 seconds)
------------------------------------
Read and write contention on database blocks was consuming significant
database time.
RECOMMENDATION 1: Schema, 1.3% benefit (3413 seconds)
ACTION: Consider partitioning the INDEX "VHBA.RESULTDT_IDX" with object
id 81456 in a manner that will evenly distribute concurrent DML
across multiple partitions.
RELEVANT OBJECT: database object with id 81456
RATIONALE: The INSERT statement with SQL_ID "7j4jahj2b0n0w" was
significantly affected by "buffer busy" waits.
RELEVANT OBJECT: SQL statement with SQL_ID 7j4jahj2b0n0w
insert into SystemLog ( ResultDT ,JobCD, ParticipantID, EpisodeID,
ResultCD, ResulTX) values (:1,:2,:3,:4,:5,:6)
RECOMMENDATION 2: Schema, 0.48% benefit (1305 seconds)
ACTION: Consider partitioning the INDEX "VHBA.MESSAGEQUEUE_IDX" with
object id 81064 in a manner that will evenly distribute concurrent
DML across multiple partitions.
RELEVANT OBJECT: database object with id 81064
RATIONALE: The INSERT statement with SQL_ID "db7qh8puap919" was
significantly affected by "buffer busy" waits.
RELEVANT OBJECT: SQL statement with SQL_ID db7qh8puap919
insert into MessageQueue
(msgid,msgtype,instrumentCD,episodeID,participantID,tailored,expirydt
,senddt) values ( :1,:2,:3,:4,:5,:6,:7,:8)
RECOMMENDATION 3: Schema, 0.45% benefit (1228 seconds)
ACTION: Consider hash partitioning the INDEX
"VHBA.PARTICIPANT_LASTMODIFIEDT_IDX" with object id 373405 in a
manner that will evenly distribute concurrent DML across multiple
partitions.
RELEVANT OBJECT: database object with id 373405
RATIONALE: The UPDATE statement with SQL_ID "1mpay0j24hjr5" was
significantly affected by "buffer busy" waits.
RELEVANT OBJECT: SQL statement with SQL_ID 1mpay0j24hjr5
update Participant set City = :1 where ParticipantID = :2
RATIONALE: The UPDATE statement with SQL_ID "274a35up63ujh" was
significantly affected by "buffer busy" waits.
RELEVANT OBJECT: SQL statement with SQL_ID 274a35up63ujh
update Participant set FirstName = :1 where ParticipantID = :2
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Inter-instance messaging was consuming significant database
time on this instance. (9% impact [24655 seconds])
SYMPTOM: Wait class "Cluster" was consuming significant database
time. (9.4% impact [25752 seconds])
SYMPTOM: Wait class "Concurrency" was consuming significant database
time. (3% impact [8151 seconds])
FINDING 5: 3.4% impact (9250 seconds)
-------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.
RECOMMENDATION 1: Application Analysis, 3.4% benefit (9250 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 25345 transactions per minute
with an average redo size of 1022 bytes per transaction.
RECOMMENDATION 2: Host Configuration, 3.4% benefit (9250 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 1
K and the average time per write was 1 milliseconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Commit" was consuming significant database time.
(3.1% impact [8476 seconds])
FINDING 6: 2.4% impact (6592 seconds)
-------------------------------------
Contention on index block splits was consuming significant database time.
However, no single index was the predominant cause for this contention.
NO RECOMMENDATIONS AVAILABLE
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Concurrency" was consuming significant database
time. (3% impact [8151 seconds])
FINDING 7: 2% impact (5349 seconds)
-----------------------------------
SQL statements responsible for significant inter-instance messaging were found
RECOMMENDATION 1: SQL Tuning, 43% benefit (118654 seconds)
ACTION: Investigate the SQL statement with SQL_ID "7bd3r84urtm8u" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 7bd3r84urtm8u and
PLAN_HASH 607575284
select LASTSEQID from ParticIDKeeper for update
RATIONALE: SQL statement with SQL_ID "7bd3r84urtm8u" was executed 9262
times and had an average elapsed time of 12 seconds.
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 99% of the database time spent in
processing the SQL statement with SQL_ID "7bd3r84urtm8u".
RATIONALE: Average time spent in Cluster wait events per execution was
0.069 seconds.
RECOMMENDATION 2: SQL Tuning, 39% benefit (106191 seconds)
ACTION: Investigate the SQL statement with SQL_ID "cpw5amz5aa8hy" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID cpw5amz5aa8hy and
PLAN_HASH 1780642097
update Authorization set curcount = curcount+:"SYS_B_0" where authcd
= :1
RATIONALE: SQL statement with SQL_ID "cpw5amz5aa8hy" was executed 9358
times and had an average elapsed time of 11 seconds.
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 99% of the database time spent in
processing the SQL statement with SQL_ID "cpw5amz5aa8hy".
RATIONALE: Average time spent in Cluster wait events per execution was
0.099 seconds.
RECOMMENDATION 3: SQL Tuning, 2.8% benefit (7751 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"7j4jahj2b0n0w".
RELEVANT OBJECT: SQL statement with SQL_ID 7j4jahj2b0n0w
insert into SystemLog ( ResultDT ,JobCD, ParticipantID, EpisodeID,
ResultCD, ResulTX) values (:1,:2,:3,:4,:5,:6)
ACTION: Investigate the SQL statement with SQL_ID "7j4jahj2b0n0w" for
possible performance improvements.
RELEVANT OBJECT: SQL statement with SQL_ID 7j4jahj2b0n0w
insert into SystemLog ( ResultDT ,JobCD, ParticipantID, EpisodeID,
ResultCD, ResulTX) values (:1,:2,:3,:4,:5,:6)
RATIONALE: SQL statement with SQL_ID "7j4jahj2b0n0w" was executed 84610
times and had an average elapsed time of 0.091 seconds.
RATIONALE: Waiting for event "gc buffer busy" in wait class "Cluster"
accounted for 37% of the database time spent in processing the SQL
statement with SQL_ID "7j4jahj2b0n0w".
RATIONALE: Waiting for event "enq: TX - contention" in wait class
"Other" accounted for 19% of the database time spent in processing
the SQL statement with SQL_ID "7j4jahj2b0n0w".
RATIONALE: Waiting for event "enq: TX - index contention" in wait class
"Concurrency" accounted for 12% of the database time spent in
processing the SQL statement with SQL_ID "7j4jahj2b0n0w".
RATIONALE: Average time spent in Cluster wait events per execution was
0.044 seconds.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Cluster" was consuming significant database time.
(9.4% impact [25752 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
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.
Wait class "User I/O" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.
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.