Database Reference
In-Depth Information
the measurement phase, you will be able to decide whether the goal might be attainable or not.
Often managers or other contacts will simply say “make it as fast as possible”. All right, at least
you've asked.
The second question to ask is whether the issue faced is truly a database performance
problem. This is done by looking at the think time figure ESQLTRCPROF derives from extended
SQL trace files. If, say 90% of the response time is think time and all the SQL statements that
were executed intermittently completed reasonably quickly, then there is no database problem.
Think time indicates that the database client did not ask the server to process any requests. The
application is either idle or busy processing instructions that do not make demands on the
DBMS instance. Since it's impossible for a DBA to reduce think time in an application, the
application developer must find out what is taking too long.
The next classification to make, is whether the slow response time is due to excessive CPU
usage or high wait time. The statistic CPU used by this session or DB CPU in Oracle10
g
indicate
CPU usage. If waiting is the main issue, the resolution depends on the kinds of wait events.
Waiting might be due to a slow I/O system (e.g.,
db file sequential read
), contention (e.g.,
enqueue
,
latch free
), or lack of CPU resources. The latter cause may be reflected in wait events if the data-
base resource manager is enabled. How to reduce wait time depending on which wait events
are most prominent (Oracle10
g
has 878 different wait events) is a subject for a separate perfor-
mance tuning book. Shee et al. ([ShDe 2004]) do a good job of addressing this topic in their book.
Resource Profiles and Performance Assessment Tools
The main goal of the assessment phase is to generate a resource profile from the data captured
in the preceding measurement phase. The concept of a resource profile has been made popular by
the work of Cary Millsap and Jeff Holt as published in the topic
Optimizing Oracle Performance
([MiHo 2003]). My extended SQL trace profiler ESQLTRCPROF is strongly influenced by this
publication. Yet, the addition of think time to resource profiles is my own invention. The research
done by Millsap and Holt has lead to the development of a commercial profiler for extended
SQL trace files, which is offered by Hotsos Enterprises, Ltd. Other tools for obtaining a resource
profile are TKPROF and ESQLTRCPROF, which is described in detail in Chapter 27.
TKPROF vs. ESQLTRCPROF
TKPROF does not really report a resource profile. Nonetheless, a TKPROF report does contain
enough information to calculate a resource profile from it. The same approach as with session
level Statspack data may be used. The response time R consists of the sum of the elapsed times
for non-recursive and recursive statements plus the wait time of the inter database call wait
events
SQL*Net message from client
and
SQL*Net message to client
. Overall totals for non-recursive
and recursive statements need to be added to get the totals for both CPU usage and wait events.
The measurement interval is reported as “elapsed seconds in trace file” in the last line of the
report by TKPROF release 10.2. Once these figures are calculated for CPU usage and all wait
events, they need to be sorted and arranged as a resource profile. Much easier to use ESQL-
TRCPROF, which does it all automatically. Another disadvantage of TKPROF is the omission
of hash values, which identify SQL statements, from the report. Hash values might be used
to correlate the SQL statements with instance level (
spreport.sql
) and SQL statement level
(
sprepsql.sql
) Statspack reports or V$ views (
V$SQL.HASH_VALUE
).
