AWR Report

Top 5 Timed Events Section

The AWR report provides a super-detailed view of all elapsed-time metrics, and the most important of these metrics is the Top-5 timed events as shown in the output below. This report is critical because it shows those database events that might constitute the bottleneck for the system.

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.

SEE CODE DEPOT FOR FULL SCRIPTS

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm