Database Reference
In-Depth Information
From the perspective of the database server, think time is elapsed time that a database
client accumulates without making any demands on the DBMS. In other words, the client is
not sending any requests to the DBMS server. By default, ESQLTRCPROF classifies any waits of
more than 5 ms for
SQL*Net message from client
as the pseudo wait event
think time
. For such
waits, think time will be displayed as a contributor to response time in resource profiles.
Command Line Options
ESQLTRCPROF accepts three options that modify its processing. When called without any
arguments, it prints information on its usage, supported options, and their meanings.
$ esqltrcprof.pl
Usage: esqltrcprof.pl -v -r <ORACLE major release>.<version> -t <think time in
milliseconds> <extended sql trace file>
-v verbose output; includes instances of think time
-r value must be in range 8.0 to 10.2
The threshold beyond which
SQL*Net message from client
is classified as think time is
configured in milliseconds with the option
-t
(for threshold). The default threshold (5 ms) is
usually appropriate for local area networks (LANs), but needs to be increased for trace files
from database clients that connect over a wide area network (WAN). To suppress the categori-
zation of
SQL*Net message from client
, perhaps for the sake of comparing figures with a TKPROF
report or other tools, set the think time threshold to an arbitrarily large value such as ten hours
(36000000 ms). This will make the pseudo wait event think time disappear from resource profiles.
The option
-v
(for verbose) is for printing instances of think time. When
-v
is used, each
time ESQLTRCPROF encounters an instance of think time in a trace file, it prints a message
with the length of the think time and the position in the trace file. Module and action are also
included in the message to point out where in the code path think time was detected.
Found 9.864 s think time (Line 64, Module 'insert_customer.pl' Action 'undefined')
Since ESQLTRCPROF also supports Oracle8, which merely had centisecond resolution of
e
,
c
, and
ela
values, it determines the unit of timing data from the trace file header. The mapping
of latch numbers to latch names is also release dependent. Thus, ESQLTRCPROF refuses to
process trace files that lack release information in the header. The TRCSESS utility shipped
with Oracle10
g
creates trace files that lack a header. When ESQLTRCPROF is passed such a file
as input, it exits with the following error message:
$ esqltrcprof.pl insert_cust.trcsess
No trace file header found reading trace file up to line 9. ORACLE release unknown.
Please use switch -r to specify release
Usage: esqltrc_profiler.pl -v -r <ORACLE major release>.<version> -t <think time in
milliseconds> <extended sql trace file>
-v verbose output; includes instances of think time
-r value must be in range 8.0 to 10.2
The option
-r
(for release) is provided to let ESQLTRCPROF know the release of the ORACLE
DBMS that created the trace file.
