Database Reference
In-Depth Information
Running the query confirms the result of the manual calculation for the interval between
snapshots 90 and 91.
Start End CPU
SnapID SnapID Start Time End Time Interval (s) Utilization (%)
------ ------ ------------------- ------------------- ------------ ---------------
90 91 15.08.2007 09:35:21 15.08.2007 09:57:24 1323 15.56
88 89 15.08.2007 06:37:42 15.08.2007 07:27:28 2986 7.14
87 88 15.08.2007 06:06:04 15.08.2007 06:37:42 1898 5.28
This query quickly identifies periods of high load that may merit drilling down by gener-
ating Statspack reports for the beginning and ending snapshots with the highest CPU usage.
High DB Time
What if a performance problem is due to waiting and does not manifest itself through high CPU
usage? Then the approach shown in the previous section fails. Waiting might be due to conten-
tion for latches, locks, or other resources such as slow disks. Oracle10
g
offers so-called time
model statistics that consider CPU time consumption and waiting. These are available at instance
(
V$SYS_TIME_MODEL
) and session level (
V$SESS_TIME_MODEL
). In essence, time spent within the
database instance is accounted for. According to the
Oracle10g Database Reference
:
DB Time is the amount of elapsed time (in microseconds) spent performing database
user-level calls. This does not include the time spent on instance background processes
such as PMON.
The manual further states that the metric “DB time” includes the following:
DB CPU
connection management call elapsed time
sequence load elapsed time
sql execute elapsed time
parse time elapsed
PL/SQL execution elapsed time
inbound PL/SQL rpc elapsed time
PL/SQL compilation elapsed time
Java execution elapsed time
Not a single word about wait time here. However, considering that wait time is rolled up
into the elapsed time of database calls in SQL trace files, it would be surprising if time model
statistics followed a different approach. A quick proof may be built by leveraging the undocu-
mented PL/SQL package
DBMS_SYSTEM
and generating some artificial wait time. At the beginning of
a new database session, all values in
V$SESS_TIME_MODEL
are nearly zero.
