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.
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
% Total
Event
Waits Time (s) 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.
SEE CODE DEPOT FOR FULL SCRIPTS
and include the URL for the page.
