TKPROF is an acronym for the Transient
Kernel PROFile, set
via the sql_trace parameter, used to format a trace file into readable
form.
In addition to TKPROF, see here for
using trace analyzer to trace SQL
and complete directions for using TKPROF in the book "Advanced Oracle Utilities":
Analyzing TKPROF Results
This is from the book
Oracle
Utilities: Using Hidden Programs,
Import/Export, SQL Loader, oradebug,
Dbverify, Tkprof and More Order now and
receive immediate access to the Online Code
Depot!
So what should DBAs be looking for? Here's a small checklist
of items to watch for in
TKPROF
formatted files:
- Compare the number of parses to number of executions. A
well-tuned system will have one parse per n executions of a statement and
will eliminate the re-parsing of the same statement.
- Search for SQL statements that do not use bind variables (:variable).
These statements should be modified to use bind variables.
- Identify those statements that perform full table scans, multiple disk
reads, and high CPU consumption. These performance benchmarks are defined by
the DBA and need to be tailored to each database. What may be considered a
high number of disk reads for an OLTP application may not even be minimal
for a data warehouse implementation.
Uses for the
TKPROF
utility
The TKPROF utility can be used for the following:
- Compare the number
of parses to number of
executions.
- Search for SQL
statements that do not
use bind variables
(:variable). These
statements should be
modified to use bind
variables.
- Identify those
statements that perform
full table scans,
multiple disk reads, and
high CPU consumption.
The Oracle
TKPROF
utility will be explained below:
Step 1: Check the
Environment
Before tracing
using TKPROF can be
enabled, the environment
must first be configured
by performing the
following steps:
- Enable Timed
Statistics - This
parameter enables the
collection of certain
vital statistics such as
CPU execution time, wait
events, and elapsed
times. The resulting
trace output is more
meaningful with these
statistics. The command
to enable timed
statistics is:
ALTER SYSTEM SET
TIMED_STATISTICS = TRUE;
- Check the User Dump
Destination Directory - The trace files
generated by Oracle can
be numerous and large.
These files are placed
by Oracle in the user_dump_dest directory
as specified in the
init.ora. The user dump
destination can also be
specified for a single
session using the alter
session command. Make
sure that enough space
exists on the device to
support the number of
trace files that you
expect to generate.
Step 2: Turn Tracing
On
The next step in the
process is to enable
tracing. By default,
tracing is disabled due
to the burden (5-10%) it
places on the database.
Tracing can be defined
at the session level:
ALTER SESSION SET
SQL_TRACE = TRUE;
DBMS_SESSION.SET_SQL_TRACE(TRUE);
A DBA may enable tracing
for another user's
session by using the
following statement:
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
The sid (Session ID) and
serial# can be obtained
from the v$session view.
Once tracing with Oracle
TKPROF
is enabled,
Oracle generates and
stores the statistics in
the trace file. The
TKPROF trace file name is
version specific.
- When tracing a
session with TKPROF, remember that
nothing in v$session
indicates that a session
is being traced.
Therefore, trace with
caution and remember to
disable tracing after an
adequate amount of trace
data has been generated.
-
TKPROF does
not control the contents
of a trace file, it
simply formats them.
Oracle provides multiple
ways to actually
generate the trace file.
TKPROF is
valuable for detailed
trace file analysis. For
those DBAs that prefer a
simpler tracing
mechanism with instant
feedback, the autotrace utility
should be used.
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
