Database Reference
In-Depth Information
Calculating Statistics
Database call statistics at recursive call depths other than zero are rolled up into the statistics
at recursive call depth 0. To calculate the total number of db block gets in the trace file, we must
consider only
cu
parameter values of
PARSE
,
EXEC
, and
FETCH
entries with
dep=0
. The database
call parameter
cu
(for current read) corresponds to the statistic db block gets. The only
cu
value
at recursive call depth 0 that is greater than zero is in line 38 (
cu=8
). This is off by one from the
db block gets value retrieved from
V$SESSTAT
. Note that three db block gets have occurred at
recursive call depth 1 and below (line 35). Two db block gets were recorded at recursive call
depth 2 (line 32). The fact that the total number of db block gets as determined by querying
V$SESSTAT
was nine confirms that database call statistics at lower levels are rolled up into statistics
at recursive call depth 0. The sum of
cu
values at any recursive call depth is 13. If the
cu
values
at recursive call depth
n
did not include the
cu
values at recursive call depth
n
+1, we would see
at least 13 db block gets in
V$SESSTAT
.
Calculating Response Time
The response time R is defined as the sum of all
e
values at
dep=0
plus the sum of all
ela
values
from inter database call wait events ([MiHo 2003], page 94).
e
ela
inter db call
R
=
+
dep
=
0
The sum of all
e
values at
dep=0
is derived from lines 12, 20, and 38.
e
=
98
++
84
1525863
=
1526045
dep
=
0
SQL*Net message from client
and
SQL*Net message to client
are the inter database call wait
events present in the trace file. The sum of all
ela
values of inter database call wait events is
derived from lines 13, 14, 37, 39, 42, and 44.
5
ela
inter db call
=
+++++
569
5
232
2
9864075
=
9864888
Since
e
and
ela
values are in microseconds, R expressed in seconds is:
1526045
+
9864888
R
=
-----------------------------------------------------
=
11.390 s
1000000
To check that the code path measured is sufficiently instrumented, it is always a good idea
to calculate the elapsed time covered by the trace file from the first and last
tim
values. The
lowest timestamp at the beginning of the trace file is 789991633616. The highest timestamp in
line 44 is 790003031019. Thus, the interval in seconds covered by the trace file is:
790003031019
-
1000000
789991633616
------------------------------------------------------------------------------
=
11.397 s
If there is a large discrepancy between R and the elapsed time covered by the trace file,
then the ORACLE kernel does not have instrumentation for part of the code path in place.
