Database Reference
In-Depth Information
by a database client. Occasionally the DBMS is not to blame for performance problems that
end users or developers perceive as database performance problems. If you encounter a resource
profile with very prominent think time, say 50% or more, the average duration of think time is
more than several hundred milliseconds and there are no expensive SQL statements in the
statement level ESQLTRCPROF report, this is probably such a case. If you do encounter such a
case, average think time will usually be in the range of several seconds. You should check the
average duration of think time to safeguard against erroneous classification of
SQL*Net message
from client
as think time due to a threshold that is too low. Of course it is also important to pay
attention to the measurement interval. It would be inappropriate to choose a period of inactivity
by a database client as the measurement interval.
ESQLTRCPROF tries to address some shortcomings of TKPROF. Given that Millsap and
Holt's book [MiHo 2003] has been available since 2003, it is somewhat astonishing that the
TKPROF release shipped with Oracle11
g
still does not contain a resource profile, nor does it
sort wait events by contribution to response time. TKPROF also fails to report average dura-
tions of database calls and wait events.
ESQLTRCPROF takes into account that inter database call as well as intra database call
wait events affect a statement's contribution to response time. A statement that is executed
many times at recursive call depth 0 incurs a network round-trip for each execution. Thus, a
statement that executes in a fraction of a second, but is executed many times, may contribute
more to response time than a slower statement, which is executed merely a few times. For the
purpose of sorting SQL or PL/SQL statements in a trace file by their actual contribution to
response time, inter database call wait events must be considered too. This is why ESQLTRCPROF
defines total elapsed time for a statement as the sum of all
e
values plus the sum of all
ela
values for
inter database call wait events that are associated with a statement. Total elapsed time is used
to sort statements in the ESQLTRCPROF statement level report section. Due to this novel
approach, ESQLTRCPROF does not need sort options in the way that TKPROF does.
Source Code Depot
Table 27-3 lists this chapter's source files and their functionality.
Table 27-3.
ESQLTRCPROF Source Code Depot
File Name
Functionality
esqltrcprof.pl
This Perl program creates a session level resource profile as well as
statement level resource profiles from an extended SQL trace file. It
classifies the wait event
SQL*Net message from client
into think time
and genuine network latency. It also computes transactions per second
and other metrics and breaks the wait events
latch free
and
enqueue
down to individual latches and enqueues.
insert_customer.pl
This Perl program inserts a row into a table with
INSERT
RETURNING
.
DDL for creating database objects referenced by the program is
included in the Perl source code.
insert_customer.trc
Extended SQL trace file from a run of
insert_customer.pl
.
