Databases Reference
In-Depth Information
processed output file. If tracing had been enabled before module and action were set, you
would need to look for the
SESSION
ID
entry preceding the
ACTION
NAME
or
MODULE
NAME
entry.
Instead of passing module and action to TRCSESS, we now pass the
SID
and
SERIAL#
that we
identified in the trace file.
$ trcsess output=session_133_39.trc session=133.39 ten1_s000_8558.trc
$ grep "not instrumented" session_133_29.trc
This time the string literal used to mark the first session is not found in the output file. This
proves that TRCSESS is able to process the shared server process' input trace file correctly, as
long as the option
session
is used.
Instrumentation and the Program Call Stack
The examples we have looked at so far are highly simplified. A real application has many routines,
which call each other. The called routine does not normally know which routine it was called
by and it may not always be called by the same routine. To keep track of the program call stack,
each routine needs to push the current instrumentation settings on to a stack before overwriting
them with its own settings. On exit from the routine, the previous settings may be fetched from
the stack (pop) and restored. Care must be taken that the pop operation is also performed
upon abnormal exit from a routine due to an exception or error. As far as I know, such a stack
was first implemented in PL/SQL by the Hotsos ORACLE instrumentation library (ILO).
7
Figure 23-1 illustrates how routines in an application call each other. Execution begins in
routine A at t
1
Instrumentation settings of routine A are in effect at this point. At time t
2
, routine
A calls routine B. To preserve the instrumentation settings of routine A, routine B pushes them
onto a stack and then puts its own settings into effect. At t
3
, routine B calls routine C, which in
turn saves instrumentation settings from routine B on the stack before overwriting them. At t
4
,
the application exits routine C, which restores the instrumentation settings of routine B by
popping them off the stack, just prior to exiting. The process goes on until the program exits
from routine A at some point after t
7
. The axis on the right indicates which routine is executing
at a certain point in time. If this approach is adhered to throughout the code path of an appli-
cation, it is possible to find out how much time is spent in each module by using
DBMS_MONITOR
and
V$SERV_MOD_ACT_STATS
. Statistics provided by the view
V$SERV_MOD_ACT_STATS
include “DB
time”, “DB CPU”, “physical reads”, “physical writes”, “cluster wait time”, “concurrency wait
time”, “application wait time”, and “user I/O wait time”.
The Hotsos ILO library is free software and may be downloaded from
http://sourceforge.net/
projects/hotsos-ilo
.
7.
