AWR Report

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     
Event                                    Waits    Time (s) % Total Ela Time
---------------------------------------------- ----------- --------
db file sequential read                568,948       4,375    66.15
CPU time                                             1,983    29.99
db file scattered read                 106,287          65      .99
log file sync                            7,053          50      .75
log buffer space                         1,717          47      .71

 

In the above example, it is clear that this system is waiting on CPU, with 29% of the processing time being spent in the CPU. As a general rule, a server is CPU-bound when the number of processes in the execution queue exceeds the number of CPUs on the server (the “r” value in rmstat).

The same phenomenon can also be observed in a system which is disk I/O bound. In the AWR sample report section below, one can see that the system is clearly constrained by disk I/O.

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                      %

Event                       Waits      Time (s)       Ela

                                                      Time

--------------------------- ---------- -----------    ----

db file sequential read           2,598   7,146         48.54

db file scattered read           25,519   3,246         22.04

library cache load lock             673   1,363          9.26

CPU time                                  1,154          7.83

log file parallel write          19,157     837          5.68

Here, reads and writes constitute the majority of the total database time. In this case, we might consider increasing the RAM size of the db_cache_size to reduce disk I/O, tune the SQL to reduce disk I/O, or invest in a faster disk I/O sub-system.

In general, the causes of these top wait events should be investigated in order to minimize database wait time as much as possible. These top wait events are also available in the next report section called Wait Events