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.