Database Reference
In-Depth Information
The elapsed time spent in database calls consists of CPU consumption and waiting. Ideally
e
for each database call at recursive call depth 0 would equal the CPU usage (
c
) plus the intra
db call wait time (
ela
).
e
c
ela
intra db call
=
+
dep
=
0
dep
=
0
In practice, there is usually a difference between these two values. It is unknown where
time not accounted for by
c
and
ela
was spent. The unknown contribution to response time
(U) within an entire trace file may be calculated as shown here:
e
ela
intra db call
U
=
-
c
-
dep
=
0
dep
=
0
In the example, the difference is very small, slightly more than 7 ms.
98
++
84
1525863
-
1000000
1500000
-
33297
--------------------------------------------------------------------------------------------------
=
-
0.007252 s
Of course it is much more convenient to automate the calculation of these figures with an
extended SQL trace profiler such as ESQLTRCPROF. This is the subject of the next section.
ESQLTRCPROF Reference
ESQLTRCPROF is an extended SQL trace profiler written in Perl by the author. It has the
following features:
Calculation of a resource profile for an entire SQL trace file
Calculation of a resource profile for each SQL or PL/SQL statement in a trace file
Categorization of the inter database call wait event
SQL*Net message from client
into
unavoidable latency due to network round-trips and think time
Sorting of statements by total elapsed time (the sum of parse, execute, and fetch elapsed
time plus inter database call wait time except think time)
Extraction of execution plans from
STAT
entries
Calculation of various statistics, such as physical reads, consistent gets, db block gets,
transactions per second, and buffer cache hit ratio
Apportionment of enqueue waits by individual enqueue
Breakdown of latch waits by individual latch
Inclusion of SQL statement hash values in the report for quickly locating statements in
trace files and integration with Statspack
Inclusion of SQL statement identifiers (
sqlid
) in the report for quickly locating statements
in trace files and integration with AWR (requires Oracle11
g
trace files as input)
Inclusion of module, action, and recursive call depth in the report
