DETAILED ADDM REPORT FOR TASK 'ADDM:4015448600_1_28011' WITH ID 143406
----------------------------------------------------------------------
Analysis Period: 06-JUN-2011 from 12:00:04 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 28010 to 28011
Database Time: 17085 seconds
Average Database Load: 27.6 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 100% impact (17085 seconds)
--------------------------------------
Significant virtual memory paging was detected on the host operating system.
RECOMMENDATION 1: Host Configuration, 100% benefit (17085 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 (17085 seconds)
ACTION: Total PGA memory used was above the specified
"pga_aggregate_target" value for 52% 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: 87% impact (14917 seconds)
-------------------------------------
SQL statements were found waiting for row lock waits.
RECOMMENDATION 1: Application Analysis, 87% benefit (14800 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: Session with ID "1787", User ID "160", Program "JDBC Thin
Client" and Module "JDBC Thin Client" was the blocking session
responsible for 19% of this recommendation's benefit.
RECOMMENDATION 2: Application Analysis, 0.68% benefit (117 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: Session with ID "1835", User ID "160", Program "JDBC Thin
Client" and Module "JDBC Thin Client" was the blocking session
responsible for 83% of this recommendation's benefit.
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class "Application" was consuming significant database
time. (87% impact [14917 seconds])
FINDING 3: 87% impact (14880 seconds)
-------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 87% benefit (14880 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 269
times and had an average elapsed time of 55 seconds.
RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
class "Application" accounted for 96% of the database time spent in
processing the SQL statement with SQL_ID "cpw5amz5aa8hy".
FINDING 4: 5.5% impact (939 seconds)
------------------------------------
Read and write contention on database blocks was consuming significant
database time.
RECOMMENDATION 1: Schema, 1% benefit (177 seconds)
ACTION: Consider hash 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
RECOMMENDATION 2: Schema, 0.88% benefit (150 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)
SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Inter-instance messaging was consuming significant database
time on this instance. (5.8% impact [988 seconds])
SYMPTOM: Wait class "Cluster" was consuming significant database
time. (6.1% impact [1048 seconds])
FINDING 5: 3.4% impact (581 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 (581 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 11870 transactions per minute
with an average redo size of 910 bytes per transaction.
RECOMMENDATION 2: Host Configuration, 3.4% benefit (581 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.2% impact [540 seconds])
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Concurrency" 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.
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.