Database Reference
In-Depth Information
19:19 1.2 6.8 93.2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:20 1.5 8.9 91.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:21 1.2 2.7 97.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:22 1.2 8.2 91.8 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:23 0.8 10.2 89.8 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:24 1.0 15.3 84.7 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:25 0.8 11.1 88.9 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:26 1.0 6.6 93.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:27 0.9 9.3 90.7 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
19:28 0.8 10.0 90.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Analysis Without Diagnostics Pack
The main operations you have to carry out for the analysis of performance problems without the Diagnostics Pack
option are basically the same as those described in the preceding section. However, it goes without saying that the
goal is to carry out the analysis without taking advantage of dynamic performance views that are licensed through the
Diagnostics Pack option. There are two challenges here: first, you can't use Enterprise Manager, and second, most of
the dynamic performance views you can use provide only cumulated statistics. Specifically, a replacement for active
session history doesn't exist. As a result, you can't look at what's happened over the last few minutes, nor can you
directly query a history of the operations carried out by a session. The only dynamic performance views that don't
provide cumulated statistics are those providing metrics. But because metrics are focused on ratios and counters,
they're of limited use for analyzing performance problems. Therefore, in general, the analysis must be based on
dynamic performance views that provide cumulative statistics.
To effectively analyze performance problems based on dynamic performance views that provide cumulative
statistics, you need utilities that sample the information they provide. Such utilities might be simple scripts or
complex tools like Enterprise Manager. Even though several third-party tools provide features similar to those
available in Enterprise Manager, this section focuses on a set of scripts that are freely available, so they can be used
on any system. Because most of these scripts work on cumulated statistics, their aim is to find out the rate at which
a specific statistic changes over a short period of time. For that purpose, they repeatedly select the same dynamic
performance view and compute deltas between each selection.
Database Server Load
To assess how much the database server is loaded, you can't use the metric's history (to use it, you need the
Diagnostics Pack option). Instead, you can look at the current metric through the
v$metric
view. To do that, I use the
host_load.sql
script. It takes as input a single parameter that specifies in minutes for how long the database server
load is shown. The following example is an excerpt of the output it generates (notice that the data is equivalent to that
displayed in Figure
4-15
):
SQL> @host_load.sql 16
BEGIN_TIME DURATION DB_FG_CPU DB_BG_CPU NON_DB_CPU OS_LOAD NUM_CPU
---------- -------- --------- --------- ---------- ------- -------
14:05:00 60.10 1.71 0.03 0.03 4.09 8
14:06:00 60.08 1.62 0.03 0.04 4.13 8
14:07:00 59.10 1.89 0.03 0.04 4.96 8
14:08:00 60.11 1.93 0.03 0.03 5.29 8
14:09:00 60.09 1.73 0.03 0.59 4.60 8
14:10:00 60.10 1.57 0.02 3.64 7.50 8
