Database Reference
In-Depth Information
or download trace files to another machine. In such situations, it can be useful. After covering TKPROF, I describe my
own profiler. The explanations are based on a trace file generated during the execution of the following PL/SQL block:
DECLARE
l_count INTEGER;
BEGIN
FOR c IN (SELECT extract(YEAR FROM d), id, pad
FROM t
ORDER BY extract(YEAR FROM d), id)
LOOP
NULL;
END LOOP;
FOR i IN 1..10
LOOP
SELECT count(n) INTO l_count
FROM t
WHERE id < i*123;
END LOOP;
END;
Using TKPROF
TKPROF is a command-line tool. Its main purpose is to take a raw trace file as input and generate a formatted text file
as output. It can also generate a SQL script to load the data in a database, although this feature is hardly ever used.
The simplest analysis is performed by just specifying an input and an output file. In the following example, the
input file is
DBM11106_ora_6334.trc
, and the output file is
DBM11106_ora_6334.txt
:
tkprof DBM11106_ora_6334.trc DBM11106_ora_6334.txt
Even if the default extension of the output file is
prf
, I personally always use
txt
. In my view, it's better to use
extensions that mean something to everybody and are usually correctly recognized by any operating system.
An analysis without specifying further arguments is helpful only when analyzing very small trace files. In most
situations, to get a better output, you must specify several arguments.
TKPROF Arguments
If you run TKPROF without arguments, you get a complete list of its arguments with a short description for each of them:
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
