Database Reference
In-Depth Information
determine whether the system is doing more or less work than expected, and more important, you can compare these
values with a baseline. Nevertheless, two of the metrics can be used to judge the load of the database instance directly
(unfortunately, both values aren't available in version 10.2.):
The
•
DB time Per Second
(7.1) is equivalent to the average number of active sessions. Based
on the rule of thumb described in Chapter 4, with an average number of active sessions that is
equivalent to the number of CPU cores (8), the system can be considered fairly busy.
The
DB CPU per second
(1.9) tells you whether the instance is CPU bound. In fact, by
comparing it with the number of CPU cores (8), you can determine the average CPU
utilization. In this case, the database instance consumed only about 24% (1.9/8*100) of
the total CPU capacity. Hence, if the server wasn't shared with other database instances or
applications, the CPU capacity was adequate for the load.
The report continues with a set of ratios that are of very limited usage. The only sensible thing you can do with
them is to compare them with a baseline to determine whether something has changed:
•
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.98
Buffer Hit %: 99.86 Optimal W/A Exec %: 100.00
Library Hit %: 100.01 Soft Parse %: 98.99
Execute to Parse %: 99.96 Latch Hit %: 99.96
Parse CPU to Parse Elapsd %: 103.03 % Non-Parse CPU: 98.84
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 66.47 66.55
% SQL with executions>1: 71.80 71.85
% Memory for SQL w/exec>1: 72.70 72.88
The next part shows a resource usage profile of the top 5 events (including the CPU utilization). Simply put, in
this table, the DB time is broken up to show how it was spent. For example, according to the following excerpt, 71.6%
of the time was spent doing single-block reads:
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 520,240 4,567 9
71.6
CPU time 1,620 25.4
log file sync 182,275 94 1 1.5
log file parallel write 178,406 39 0 .6
read by other session 2,693 27 10 .4
Contrary to the AWR report, the list of top 5 events doesn't show the wait classes (for example,
User I/O
,
System I/O
,
Commit
, or
Concurrency
). If you see an event for which you ignore the wait class it belongs to, you can
execute a query like the following to find it:
SQL> SELECT wait_class
2 FROM v$event_name
3 WHERE name = 'db file parallel read';
