          ADDM Report for Task 'TASK_481'
          -------------------------------

Analysis Period
---------------
AWR snapshot range from 355 to 356.
Time period starts at 04-AUG-08 12.04.53 PM
Time period ends at 04-AUG-08 12.06.09 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 1297 seconds.
The average number of active sessions was 17.07.

Summary of Findings
-------------------
   Description            Active Sessions      Recommendations
                          Percent of Activity
   ---------------------  -------------------  ---------------
1  CPU Usage              17.07 | 100          3
2  Top SQL by DB Time     15.79 | 92.51        3
3  Buffer Busy            9.36 | 54.84         1
4  Buffer Busy            8.8 | 51.57          2
5  Commits and Rollbacks  1.16 | 6.81          1
6  Buffer Cache Latches   .86 | 5.05           0


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


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

Finding 1: CPU Usage
Impact is 17.07 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 17.07 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 10.07 active sessions, 59.01% 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 80% 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 4.01 active sessions, 23.47% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "0fwmrgfjsmhmq" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID 0fwmrgfjsmhmq and PLAN_HASH 368089701.
         select /*+ result_cache */                     employee_id, last_name
         from                     employees where employee_id > :EMPNO order
         by employee_id
   Rationale
      SQL statement with SQL_ID "0fwmrgfjsmhmq" was executed 2400 times and
      had an average elapsed time of 0.12 seconds.
   Rationale
      Waiting for event "buffer busy waits" in wait class "Concurrency"
      accounted for 29% of the database time spent in processing the SQL
      statement with SQL_ID "0fwmrgfjsmhmq".
   Rationale
      Average CPU used per execution was 0.025 seconds.


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

   Recommendation 1: SQL Tuning
   Estimated benefit is 10.07 active sessions, 59.01% 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 80% of the database time spent in processing the SQL
      statement with SQL_ID "d9pnqk1hnx7h5".

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

   Recommendation 3: SQL Tuning
   Estimated benefit is 2.06 active sessions, 12.07% of total activity.
   --------------------------------------------------------------------
   Action
      Investigate the SQL statement with SQL_ID "9n2yvagk4drxd" for possible
      performance improvements.
      Related Object
         SQL statement with SQL_ID 9n2yvagk4drxd and PLAN_HASH 3236529094.
         select  /*+ result_cache */                           employee_id,
         last_name, salary from                           employees where
         employee_id = :EMPNO
   Rationale
      SQL statement with SQL_ID "9n2yvagk4drxd" was executed 2400 times and
      had an average elapsed time of 0.065 seconds.


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

   Recommendation 1: Schema Changes
   Estimated benefit is 8.8 active sessions, 51.57% 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.56 active sessions, 61.85% of total activity.


Finding 4: Buffer Busy
Impact is 8.8 active sessions, 51.57% 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 8.8 active sessions, 51.57% 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 8.8 active sessions, 51.57% 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.56 active sessions, 61.85% of total activity.


Finding 5: Commits and Rollbacks
Impact is 1.16 active sessions, 6.81% 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.16 active sessions, 6.81% 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 12 milliseconds.

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


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

   No recommendations are available.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Concurrency" was consuming significant database time.
      Impact is 10.56 active sessions, 61.85% 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.

