Database Reference
In-Depth Information
14:12:00 60.11 1.21 0.02 6.60 13.77 8
14:13:00 60.28 1.17 0.02 6.62 15.30 8
14:14:00 59.24 1.19 0.02 6.55 14.06 8
14:15:00 60.09 1.59 0.04 0.18 9.19 8
14:16:00 60.09 1.77 0.03 0.03 7.88 8
14:17:00 60.09 1.72 0.03 0.04 5.45 8
14:18:00 60.11 1.87 0.03 0.03 5.28 8
14:19:00 60.09 1.77 0.03 0.03 5.54 8
14:20:00 60.08 1.72 0.03 0.04 4.83 8
System Level Analysis
When you do an analysis at the system level, at first you should look at some system-wide statistics to check how
much the database instance is loaded. Such statistics are provided by the
v$system_wait_class
view. Specifically,
you should use a script (or tool) that samples the content of the
v$system_wait_class
view to get information similar
to what is shown in Figure
4-16
. In other words, the average number of active sessions and the portion of time they
spent for every wait class. Here I show you an example based on the script
system_activity.sql
. To use it, you have
to specify two parameters:
•
The first parameter specifies the sampling interval in seconds. Because the database engine
doesn't update the statistics in real-time, specifying less than 10-15 seconds is usually
pointless.
•
The second parameter specifies the number of samples to be taken.
The following example is an excerpt of the output generated by the
system_activity.sql
script when gathering
20 samples of 15 seconds each (notice that the data is equivalent to what is displayed in Figure
4-16
):
SQL> @system_activity.sql 15 20
Time AvgActSess Other% Net% Adm% Conf% Comm% Appl% Conc% SysIO% UsrIO% Sched% CPU%
-------- ---------- ------ ---- ---- ----- ----- ----- ----- ------ ------ ------ ------
19:10:11 9.7 0.0 0.0 0.0 0.0 0.4 0.0 0.0 0.9 94.8 0.0 3.8
19:10:26 10.0 0.0 0.0 0.0 0.0 0.5 0.0 0.0 1.0 94.6 0.0 3.9
19:10:41 10.0 0.0 0.0 0.0 0.0 0.4 0.0 0.0 1.0 94.8 0.0 3.8
19:10:56 9.9 0.0 0.0 0.0 0.0 0.4 0.0 0.0 1.0 94.6 0.0 4.0
19:11:11 9.8 0.0 0.0 0.0 0.2 1.0 0.0 0.0 1.2 93.7 0.0 4.0
19:11:26 9.5 0.0 0.0 0.0 0.0 0.4 0.0 0.0 0.9 94.8 0.0 3.9
19:11:41 9.6 0.0 0.0 0.0 0.0 0.4 0.0 0.0 0.9 94.8 0.0 3.8
19:11:56 9.8 0.0 0.0 0.0 0.0 0.5 0.0 0.0 1.0 94.6 0.0 3.9
19:12:11 9.7 0.0 0.0 0.0 0.0 0.3 0.0 0.0 0.8 94.8 0.0 4.1
19:12:26 9.5 0.0 0.0 0.0 0.0 0.4 0.0 0.0 1.0 94.5 0.0 4.0
19:12:42 9.9 0.0 0.0 0.0 0.0 0.4 0.0 0.0 0.9 95.1 0.0 3.6
19:12:57 9.8 0.0 0.0 0.0 0.9 0.4 0.0 0.1 0.9 93.7 0.0 3.9
19:13:12 9.4 0.0 0.0 0.0 0.0 0.4 0.0 0.0 1.0 94.7 0.0 4.0
19:13:27 9.7 0.0 0.0 0.0 0.0 0.4 0.0 0.0 0.9 94.7 0.0 4.0
19:13:42 9.8 0.0 0.0 0.0 0.0 0.4 0.0 0.0 1.1 94.6 0.0 3.9
19:13:57 10.1 0.0 0.0 0.0 0.0 0.4 0.0 0.0 1.0 94.9 0.0 3.7
19:14:12 9.9 0.0 0.0 0.0 0.0 0.4 0.0 0.0 0.9 94.9 0.0 3.7
19:14:27 9.6 0.0 0.0 0.0 0.0 0.4 0.0 0.0 1.0 94.5 0.0 4.0
19:14:42 9.6 0.0 0.0 0.0 0.7 0.4 0.0 0.0 0.9 94.0 0.0 4.0
