          ADDM Report for Task 'TASK_478'
          -------------------------------

Analysis Period
---------------
AWR snapshot range from 353 to 354.
Time period starts at 04-AUG-08 12.03.14 PM
Time period ends at 04-AUG-08 12.04.31 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 1293 seconds.
The average number of active sessions was 16.8.

Summary of Findings
-------------------
   Description                       Active Sessions      Recommendations
                                     Percent of Activity
   --------------------------------  -------------------  ---------------
1  CPU Usage                         16.8 | 100           3
2  Top SQL by DB Time                15.6 | 92.89         3
3  Buffer Busy                       9.02 | 53.7          1
4  Buffer Busy                       7.75 | 46.16         2
5  Commits and Rollbacks             1.07 | 6.37          1
6  Buffer Cache Latches              .99 | 5.88           1
7  Unusual "Concurrency" Wait Event  .4 | 2.39            1


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


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

Finding 1: CPU Usage
Impact is 16.8 active sessions, 100% of total activity.
-------------------------------------------------------
Host CPU was a bottleneck and the instance was consuming 89% of the host CPU.
All wait times will be inflated by wait for CPU.
Host CPU consumption was 93%.  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 16.8 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 9.58 active sessions, 57% 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 2400 times and
      had an average elapsed time of 0.3 seconds.
   Rationale
      Waiting for event "buffer busy waits" in wait class "Concurrency"
      accounted for 69% of the database time spent in processing the SQL
      statement with SQL_ID "d9pnqk1hnx7h5".
   Rationale
      Waiting for event "latch: cache buffers chains" in wait class
      "Concurrency" accounted for 8% of the database time spent in processing
      the SQL statement with SQL_ID "d9pnqk1hnx7h5".
   Rationale
      Average CPU used per execution was 0.016 seconds.

   Recommendation 3: SQL Tuning
   Estimated benefit is 5.05 active sessions, 30.08% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "cyuknq4uv9gn6" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID cyuknq4uv9gn6 and PLAN_HASH 368089701.
         select employee_id, last_name from                     employees
         where employee_id > :EMPNO order by employee_id
   Rationale
      SQL statement with SQL_ID "cyuknq4uv9gn6" was executed 2400 times and
      had an average elapsed time of 0.13 seconds.
   Rationale
      Waiting for event "buffer busy waits" in wait class "Concurrency"
      accounted for 35% of the database time spent in processing the SQL
      statement with SQL_ID "cyuknq4uv9gn6".
   Rationale
      Average CPU used per execution was 0.026 seconds.


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

   Recommendation 1: SQL Tuning
   Estimated benefit is 9.58 active sessions, 57% 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 2400 times and
      had an average elapsed time of 0.3 seconds.
   Rationale
      Waiting for event "buffer busy waits" in wait class "Concurrency"
      accounted for 69% of the database time spent in processing the SQL
      statement with SQL_ID "d9pnqk1hnx7h5".
   Rationale
      Waiting for event "latch: cache buffers chains" in wait class
      "Concurrency" accounted for 8% of the database time spent in processing
      the SQL statement with SQL_ID "d9pnqk1hnx7h5".

   Recommendation 2: SQL Tuning
   Estimated benefit is 5.05 active sessions, 30.08% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "cyuknq4uv9gn6" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID cyuknq4uv9gn6 and PLAN_HASH 368089701.
         select employee_id, last_name from                     employees
         where employee_id > :EMPNO order by employee_id
   Rationale
      SQL statement with SQL_ID "cyuknq4uv9gn6" was executed 2400 times and
      had an average elapsed time of 0.13 seconds.
   Rationale
      Waiting for event "buffer busy waits" in wait class "Concurrency"
      accounted for 35% of the database time spent in processing the SQL
      statement with SQL_ID "cyuknq4uv9gn6".

   Recommendation 3: SQL Tuning
   Estimated benefit is 1.96 active sessions, 11.64% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "au7v19t361bn7" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID au7v19t361bn7 and PLAN_HASH 3236529094.
         select employee_id, last_name, salary from
         employees where employee_id = :EMPNO
   Rationale
      SQL statement with SQL_ID "au7v19t361bn7" was executed 2400 times and
      had an average elapsed time of 0.06 seconds.
   Rationale
      Waiting for event "buffer busy waits" in wait class "Concurrency"
      accounted for 41% of the database time spent in processing the SQL
      statement with SQL_ID "au7v19t361bn7".


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

   Recommendation 1: Schema Changes
   Estimated benefit is 7.75 active sessions, 46.16% 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 10.41 active sessions, 61.97% of total activity.


Finding 4: Buffer Busy
Impact is 7.75 active sessions, 46.16% 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 7.75 active sessions, 46.16% 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
   Rationale
      The SQL statement with SQL_ID "cyuknq4uv9gn6" spent significant time on
      "buffer busy" waits for the hot block.
      Related Object
         SQL statement with SQL_ID cyuknq4uv9gn6.
         select employee_id, last_name from                     employees
         where employee_id > :EMPNO order by employee_id

   Recommendation 2: Schema Changes
   Estimated benefit is 7.75 active sessions, 46.16% 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 10.41 active sessions, 61.97% of total activity.


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

   Recommendation 1: Host Configuration
   Estimated benefit is 1.07 active sessions, 6.37% 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 28 K and the
      average time per write was 13 milliseconds.

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


Finding 6: Buffer Cache Latches
Impact is .99 active sessions, 5.88% of total activity.
-------------------------------------------------------
Contention on buffer cache latches was consuming significant database time.

   Recommendation 1: SQL Tuning
   Estimated benefit is 8.31 active sessions, 49.48% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "d9pnqk1hnx7h5" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID d9pnqk1hnx7h5.
         update employees set salary = :sal                         where
         employee_id = :id
   Rationale
      Waiting for event "buffer busy waits" in wait class "Concurrency"
      accounted for 80% of the database time spent in processing the SQL
      statement with SQL_ID "d9pnqk1hnx7h5".
   Rationale
      Waiting for event "latch: cache buffers chains" in wait class
      "Concurrency" accounted for 9% of the database time spent in processing
      the SQL statement with SQL_ID "d9pnqk1hnx7h5".

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


Finding 7: Unusual "Concurrency" Wait Event
Impact is .4 active sessions, 2.39% 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 .4 active sessions, 2.39% 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 10.41 active sessions, 61.97% 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.

