DETAILED ADDM REPORT FOR TASK 'TASK_143409' WITH ID 143409
          ----------------------------------------------------------
 
              Analysis Period: 06-JUN-2011 from 11:00:18 to 13:00:35
         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 28012
                Database Time: 273552 seconds
        Average Database Load: 37.9 active sessions
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 
FINDING 1: 100% impact (273552 seconds)
---------------------------------------
Significant virtual memory paging was detected on the host operating system.
 
   RECOMMENDATION 1: Host Configuration, 100% benefit (273552 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 (273552 seconds)
      ACTION: Total PGA memory used was above the specified 
         "pga_aggregate_target" value for 18% 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 (118704 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 (106236 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 (24557 seconds)
------------------------------------
Read and write contention on database blocks was consuming significant 
database time.
 
   RECOMMENDATION 1: Schema, 1.3% benefit (3414 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 (1306 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 [24676 seconds])
         SYMPTOM: Wait class "Cluster" was consuming significant database 
                  time. (9.4% impact [25773 seconds])
      SYMPTOM: Wait class "Concurrency" was consuming significant database 
               time. (3% impact [8155 seconds])
 
FINDING 5: 3.4% impact (9263 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 (9263 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 14789 transactions per minute 
         with an average redo size of 1024 bytes per transaction.
 
   RECOMMENDATION 2: Host Configuration, 3.4% benefit (9263 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 [8488 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 [8155 seconds])
 
FINDING 7: 2% impact (5349 seconds)
-----------------------------------
SQL statements responsible for significant inter-instance messaging were found
 
   RECOMMENDATION 1: SQL Tuning, 43% benefit (118704 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 (106236 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 [25773 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.