Database Reference
In-Depth Information
The following lines summarize the number of SQL statements belonging to the current session, how many of
them were executed recursively by the database engine, and how many of them the
EXPLAIN PLAN
statement was
executed for:
5 user SQL statements in session.
13 internal SQL statements in session.
18 SQL statements in session.
2 statements EXPLAINed in this session.
The output file ends by giving overall information about the trace file. At first, you can see the trace file name,
its version, and the value of the
sort
argument used for the analysis. Then, the overall number of sessions and SQL
statements are given. In this specific case, because the argument
print=4
was specified, you can deduce that 14
(18 - 4) SQL statements are missing in the output file. Information about the table used to execute the
EXPLAIN PLAN
statement is given as well. At the end, you can see the number of lines the trace file is composed of as well as the
overall elapsed time (in seconds) for all SQL statements. I'd personally prefer to see this last piece of information at
the beginning of the output file rather than the end. That's because every time I open a TKPROF output file, I glance at
this last line before doing everything else. Knowing how much time is spent for the whole trace file is crucial; without
it, you can't judge the magnitude of the impact of one SQL statement on the total response time:
Trace file: DBM11203_ora_28030.trc
Trace file compatibility: 11.1.0.7
Sort options: prsela exeela fchela
1 session in tracefile.
5 user SQL statements in trace file.
13 internal SQL statements in trace file.
18 SQL statements in trace file.
18 unique SQL statements in trace file.
2 SQL statements EXPLAINed using schema:
CHRIS.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
46125 lines in trace file.
12 elapsed seconds in trace file.
Using TVD$XTAT
Trivadis Extended Tracefile Analysis Tool (TVD$XTAT) is a command-line tool. Like TKPROF, its main purpose is to
take a raw trace file as input and generate a formatted file as output. The output file can be an HTML or text file.
The simplest analysis is performed by merely 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.html
:
tvdxtat -i DBM11106_ora_6334.trc -o DBM11106_ora_6334.html
Why Is TKPROF Not Enough?
In late 1999, I had my first encounter with extended SQL trace, through the Oracle Support note
Interpreting Raw
SQL_TRACE and DBMS_SUPPORT.START_TRACE output
(39817.1). From the beginning, it was clear that the
