Database Reference
In-Depth Information
files for the purpose of leveraging their content. I should note that TKPROF at that time didn't provide information
about wait events. After spending too much time manually extracting information from the raw trace files through
command-line tools like awk, I decided to write my own analysis tool: TVD$XTAT.
Currently, TKPROF provides information about wait events, but it still has five major problems that are addressed
in TVD$XTAT:
•
As soon as the
sort
argument is specified, the relationship between SQL statements is lost.
Data is provided only in aggregated form. Consequently, useful information is lost.
•
•
No information about bind variables is provided.
•
Idle wait events (for example,
SQL*Net message from client
) taking place during the
execution of a SQL statement aren't accounted for in the elapsed time shown by TKPROF. As
a result, when SQL statements are sorted according to their elapsed time, the output might be
misleading or, in extreme cases, very time consuming if not nearly impossible to interpret.
•
When a trace file doesn't contain the text of a SQL statement (specifically, the text delimited
between the
PARSING IN CURSOR
and
END OF STMT
keywords), TKPROF doesn't report the
details about the SQL statement; it just accounts for the resource utilization in the summary at
the end of the output file. Note that one case in which the text of a SQL statement isn't stored
in the trace file is when SQL trace is activated after the execution has already been started.
Installation
Here's how to install TVD$XTAT:
1.
2.
Uncompress the distribution file into an empty directory of your choice.
3.
In the shell script used to start TVD$XTAT (either
tvdxtat.cmd
or
tvdxtat.sh
, depending
on your operating system), modify the
java_home
and
tvdxtat_home
variables. The former
references the directory where a Java Runtime Environment (version 1.4.2 or later) is
installed. The latter references the directory where the distribution file was uncompressed.
4.
Optionally, change the default value of the command-line arguments. To do that, you need
to modify the
tvdxtat.properties
file, which is stored in the
config
subdirectory. By
customizing the default configuration, you can avoid specifying all arguments every time
you run TVD$XTAT.
5.
Optionally, change the logging configuration. To do that, you have to modify the
logging.properties
file, which is stored in the
config
subdirectory. Per default,
TVD$XTAT shows errors and warnings. It's not usually necessary to change these default
settings, however.
TVD$XTAT Arguments
If you run TVD$XTAT without arguments, you get a complete list of the available arguments with a short description
for each of them. Note that for every argument, there's a short representation (for example,
-c
) and a long
representation (for example,
--cleanup
):
usage: tvdxtat [-c no|yes] [-f <int>] [-l <int>] [-r 7|8|9|10|11|12]
[-s no|yes] [-t <template>] [-w no|yes]
