Database Reference
In-Depth Information
Another useful system-wide perspective on the load of a system is given by the time model statistics, specifically
from the data provided by the
v$sys_time_model
view. Its content tells you which engine is doing most of the
processing and, in case the SQL engine is responsible for it, it also informs you whether operations like parses might
be impacting the performance. Also in this case, I advise you to use a script (or tool) that samples the content of the
dynamic performance view. For example, the
time_model.sql
script shows, for a given period of time, the deltas of all
time model statistics. 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 time model 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 shows the output generated by the
time_model.sql
script when gathering two samples of
15 seconds each (notice that the script displays only statistics that change during the sampling period):
SQL> @time_model.sql 15 2
Time Statistic AvgActSess Activity%
-------- -------------------------------------------------- ---------- ---------
19:14:49 DB time 9.8 98.6
.DB CPU 0.3 3.4
.sql execute elapsed time 9.7 97.3
.PL/SQL execution elapsed time 0.1 1.2
background elapsed time 0.1 1.4
.background cpu time 0.0 0.4
Time Statistic AvgActSess Activity%
-------- -------------------------------------------------- ---------- ---------
19:15:04 DB time 9.8 98.8
.DB CPU 0.3 3.5
.sql execute elapsed time 9.7 97.8
.parse time elapsed 0.0 0.3
..hard parse elapsed time 0.0 0.3
.PL/SQL execution elapsed time 0.1 1.2
background elapsed time 0.1 1.2
.background cpu time 0.0 0.3
You can also use time model statistics to check whether specific sessions are actually responsible for most of the
observed activity. For that purpose, you need the session level statistics provided by the
v$sess_time_model
view.
Also in this case, you should use a script (or tool) that samples the content of the dynamic performance view. Here
I show you an example based on the
active_sessions.sql
script. Its purpose is to show, for a given period of time,
how much DB time is spent by the top sessions. To use it, you have to specify three parameters:
•
The first parameter specifies the sampling interval in seconds. Because the database engine
doesn't update time model 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 third parameter specifies how many sessions are listed in the output. It's usually pointless
to specify more than 10-20 sessions.
