Database Reference
In-Depth Information
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
The function of each argument is as follows:
•
explain
instructs TKPROF to generate an execution plan for each SQL statement found in
the trace file. This is done by executing the
EXPLAIN PLAN
statement (see Chapter 10 for
detailed information about this SQL statement). Obviously, to execute a SQL statement,
a connection to a database is needed. Consequently, the argument is used to specify the user,
password, and, if needed, connect string. The accepted formats are
explain=user/password@
connect_string
and
explain=user/password
. Be aware that in order to maximize your
chances of getting the right execution plans, you should specify a user with access to the same
objects and make sure all query optimizer initialization parameters are set to the same value
as the one used to generate the trace file. You should also be wary of initialization parameters
changed at runtime by the application or with logon triggers. It goes without saying that if
you can use the same user, it's even better. In any case, even if all the previous conditions are
met, because the execution plans generated by the
EXPLAIN PLAN
statement don't necessarily
match the real ones (the reasons are explained in Chapter 10), I don't recommend specifying
the
explain
argument. If an invalid user, password, or connect string is specified, the trace file
is processed without any interactive error message. Instead, an error like the following will be
found in the output file just after the header:
error connecting to database using: scott/lion
ORA-01017: invalid username/password; logon denied
EXPLAIN PLAN option disabled.
•
table
is used only together with the
explain
argument. Its purpose is, in fact, to specify which
table is used by the
EXPLAIN PLAN
statement to generate the execution plans. Usually you
can avoid specifying it because TKPROF automatically creates and drops a plan table named
prof$plan_table
in the schema used for the analysis. In any case, if the user can't create
tables (for example, because the
CREATE TABLE
privilege is lacking), then the
table
argument
must be specified. For example, to specify that the
plan_table
table owned by the
system
user
must be used, the argument must be set to
table=system.plan_table
. The user performing
the analysis must have
SELECT
,
INSERT
, and
DELETE
privileges on the specified table. Also, in
