          ADDM Report for Task 'TASK_475'
          -------------------------------

Analysis Period
---------------
AWR snapshot range from 351 to 352.
Time period starts at 04-AUG-08 11.59.18 AM
Time period ends at 04-AUG-08 12.02.05 PM

Analysis Target
---------------
Database '15V' with DB ID 1635817246.
Database version 11.1.0.6.0.
ADDM performed an analysis of instance 15v, numbered 1 and hosted at stade50.

Activity During the Analysis Period
-----------------------------------
Total database time was 2274 seconds.
The average number of active sessions was 13.62.

Summary of Findings
-------------------
   Description                       Active Sessions      Recommendations
                                     Percent of Activity
   --------------------------------  -------------------  ---------------
1  CPU Usage                         13.62 | 100          2
2  Commits and Rollbacks             9.55 | 70.11         2
3  Top SQL by DB Time                3.09 | 22.72         1
4  Buffer Busy                       1.95 | 14.34         2
5  Buffer Busy                       1.95 | 14.34         1
6  Unusual "Concurrency" Wait Event  .43 | 3.16           1


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


          Findings and Recommendations
          ----------------------------

Finding 1: CPU Usage
Impact is 13.62 active sessions, 100% of total activity.
--------------------------------------------------------
Host CPU was a bottleneck and the instance was consuming 76% of the host CPU.
All wait times will be inflated by wait for CPU.
Host CPU consumption was 89%.  CPU runqueue statistics are not available from
the host's OS. This disables ADDM's ability to estimate the impact of this
finding.

   Recommendation 1: Host Configuration
   Estimated benefit is 13.62 active sessions, 100% of total activity.
   -------------------------------------------------------------------
   Action
      Consider adding more CPUs to the host or adding instances serving the
      database on other hosts.
   Action
      Also consider using Oracle Database Resource Manager to prioritize the
      workload from various consumer groups.

   Recommendation 2: SQL Tuning
   Estimated benefit is 3.09 active sessions, 22.72% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "d9pnqk1hnx7h5" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID d9pnqk1hnx7h5 and PLAN_HASH 1069195424.
         update employees set salary = :sal                         where
         employee_id = :id
   Rationale
      SQL statement with SQL_ID "d9pnqk1hnx7h5" was executed 191994 times and
      had an average elapsed time of 0.0026 seconds.
   Rationale
      Waiting for event "buffer busy waits" in wait class "Concurrency"
      accounted for 49% of the database time spent in processing the SQL
      statement with SQL_ID "d9pnqk1hnx7h5".
   Rationale
      Average CPU used per execution was 0.0005 seconds.


Finding 2: Commits and Rollbacks
Impact is 9.55 active sessions, 70.11% of total activity.
---------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is 9.55 active sessions, 70.11% 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 68985 transactions per minute with an
      average redo size of 488 bytes per transaction.

   Recommendation 2: Host Configuration
   Estimated benefit is 9.55 active sessions, 70.11% 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 2 milliseconds.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Commit" was consuming significant database time.
      Impact is 9.55 active sessions, 70.11% of total activity.


Finding 3: Top SQL by DB Time
Impact is 3.09 active sessions, 22.72% of total activity.
---------------------------------------------------------
SQL statements consuming significant database time were found.

   Recommendation 1: SQL Tuning
   Estimated benefit is 3.09 active sessions, 22.72% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "d9pnqk1hnx7h5" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID d9pnqk1hnx7h5 and PLAN_HASH 1069195424.
         update employees set salary = :sal                         where
         employee_id = :id
   Rationale
      SQL statement with SQL_ID "d9pnqk1hnx7h5" was executed 191994 times and
      had an average elapsed time of 0.0026 seconds.
   Rationale
      Waiting for event "buffer busy waits" in wait class "Concurrency"
      accounted for 49% of the database time spent in processing the SQL
      statement with SQL_ID "d9pnqk1hnx7h5".


Finding 4: Buffer Busy
Impact is 1.95 active sessions, 14.34% of total activity.
---------------------------------------------------------
A hot data block with concurrent read and write activity was found. The block
belongs to segment "HR.EMPLOYEES" and is block 48320 in file 1.

   Recommendation 1: Application Analysis
   Estimated benefit is 1.95 active sessions, 14.34% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate application logic to find the cause of high concurrent read
      and write activity to the data present in this block.
      Related Object
         Database block with object number 55675, file number 1 and block
         number 48320.
   Rationale
      The SQL statement with SQL_ID "d9pnqk1hnx7h5" spent significant time on
      "buffer busy" waits for the hot block.
      Related Object
         SQL statement with SQL_ID d9pnqk1hnx7h5.
         update employees set salary = :sal                         where
         employee_id = :id

   Recommendation 2: Schema Changes
   Estimated benefit is 1.95 active sessions, 14.34% of total activity.
   --------------------------------------------------------------------
   Action
      Consider rebuilding the TABLE "HR.EMPLOYEES" with object ID 55675 using
      a higher value for PCTFREE.
      Related Object
         Database object with ID 55675.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Concurrency" was consuming significant database time.
      Impact is 2.43 active sessions, 17.82% of total activity.


Finding 5: Buffer Busy
Impact is 1.95 active sessions, 14.34% of total activity.
---------------------------------------------------------
Read and write contention on database blocks was consuming significant
database time.

   Recommendation 1: Schema Changes
   Estimated benefit is 1.95 active sessions, 14.34% of total activity.
   --------------------------------------------------------------------
   Action
      Consider rebuilding the TABLE "HR.EMPLOYEES" with object ID 55675 using
      a higher value for PCTFREE.
      Related Object
         Database object with ID 55675.
   Rationale
      The UPDATE statement with SQL_ID "d9pnqk1hnx7h5" was significantly
      affected by "buffer busy" waits.
      Related Object
         SQL statement with SQL_ID d9pnqk1hnx7h5.
         update employees set salary = :sal                         where
         employee_id = :id

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Concurrency" was consuming significant database time.
      Impact is 2.43 active sessions, 17.82% of total activity.


Finding 6: Unusual "Concurrency" Wait Event
Impact is .43 active sessions, 3.16% of total activity.
-------------------------------------------------------
Wait event "latch: In memory undo latch" in wait class "Concurrency" was
consuming significant database time.

   Recommendation 1: Application Analysis
   Estimated benefit is .43 active sessions, 3.16% of total activity.
   ------------------------------------------------------------------
   Action
      Investigate the cause for high "latch: In memory undo latch" waits.
      Refer to Oracle's "Database Reference" for the description of this wait
      event.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Concurrency" was consuming significant database time.
      Impact is 2.43 active sessions, 17.82% of total activity.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information
          ----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
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.

