Database Reference
In-Depth Information
Interpreting TKPROF Output
The analysis was done by specifying the following arguments:
tkprof DBM11203_ora_28030.trc DBM11203_ora_28030.txt
sort=prsela,exeela,fchela print=4 explain=chris/ian aggregate=no
Note that this isn't the way you were just advised to do it. This is only to show you a specific output. Both the trace
file and the output file are available along with the other files for this chapter.
The output file begins with a header. Most of its information is static. Nevertheless, there's useful information in
it: the name of the trace file, the value of the
sort
argument used for the generation of the output file, and a line that
identifies the traced session. This last bit of information is available only because the
aggregate=no
argument was
specified. Note that when a trace file contains multiple sessions and the
aggregate=no
argument is specified, this
header is repeated and used as a separator between the SQL statements belonging to different sessions:
TKPROF: Release 11.2.0.3.0 - Development on Fri Nov 30 23:45:57 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: DBM11203_ora_28030.trc
Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
--------------------------------------------------------------------------------
*** SESSION ID: (156.29) 2012-11-30 23:21:45.691
Any error that occurred while connecting to the database or generating the execution plans is added just after
this header.
After the header, the following information is given for every SQL statement: the text of the SQL statement, the
execution statistics, information about parsing, the execution plan, and the wait events. The execution plan and wait
events are reported only if they're stored in the trace file. Remember, in version 10.2, an execution plan is written
to the trace file only when the cursor it's associated with is closed. This means that if an application reuses cursors
without closing them, no execution plan will be written in the trace file for the reused cursors.
The text of the SQL statement in some situations is formatted. Unfortunately, the code responsible for this
operation doesn't provide correct formatting in all situations. For instance, in this case the
FROM
keyword of the
extract
function is confused with the
FROM
clause of the
SELECT
statement. Note that the identifier of the SQL
statement is available only as of version 11.1.0.6
,
and the execution plan hash value only as of version 11.1.0.7:
SQL ID: 7wd0gdwwgph1r Plan Hash: 961378228
SELECT EXTRACT(YEAR
FROM
D), ID, PAD FROM T ORDER BY EXTRACT(YEAR FROM D), ID
