Introduction

In late 1999, I had my first encounter with extended SQL trace, through MetaLink note Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output (39817.1). From the beginning, it was clear that the information it provided was essential for understanding what an application is doing when it is connected to an Oracle database engine. At the same time, I was very disappointed that no tool was available for analyzing extended SQL trace files for the purpose of leveraging their content. I should note that TKPROF at that time did not provide information about wait events. After spending too much time manually extracting information from the raw trace files, I decided to write my own analysis tool: TVD$XTAT.

Over the time the tool has been enhanced and even rewritten several times, here a short history:

License and Distribution

TVD$XTAT is free and licensed under a Creative Commons License. I wrote it because I need it to quickly identify performance issues when I work for my customers.

This software is provided "as is" and any expressed or implied warranties, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose are disclaimed. In no event shall the author be liable for any direct, indirect, incidental, special, exemplary, or consequential damages (including, but not limited to, procurement of substitute goods or services; loss of use, data, or profits; or business interruption) however caused and on any theory of liability, whether in contract, strict liability, or tort (including negligence or otherwise) arising in any way out of the use of this software, even if advised of the possibility of such damage.

Requirements

Java Runtime Environment 1.4.1 or newer.

Installation

To install TVD$XTAT, you need to perform the following operations:

• Download TVD$XTAT from http://top.antognini.ch.
• Uncompress the distribution file into an empty directory of your choice.
• In the shell script used to start TVD$XTAT (either tvdxtat.cmd or tvdxtat.sh, depending on your operating system), modify the variables java_home and tvdxtat_home. The former references the directory where a Java Runtime Environment is installed. The latter references the directory where the distribution file was uncompressed.
• Optionally, change the default value of the command-line arguments. To do that, you need to modify the file tvdxtat.properties, which is stored in the subdirectory config. By customizing the default configuration, you can avoid specifying all arguments every time you run TVD$XTAT.
• Optionally, change the logging configuration. To do that, you have to modify the file logging.properties, which is stored in the directory config. Per default, TVD$XTAT shows errors and warnings. It isn’t usually necessary to change these default settings, however.

Utilization

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 is 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]
                 [-s no|yes] [-t <template>] [-w no|yes]
                 [-x severe|warning|info|fine|finer] -i <input> -o <output>
   -c,--cleanup     remove temporary XML file (no|yes)
   -f,--feedback    display progress every x lines (integer number >= 0, no
                    progress = 0)
   -h,--help        display this help information and exit
   -i,--input       input trace file name (valid extensions are TRC, GZ and
                    ZIP)
   -l,--limit       limit the size of lists (e.g. number of statements) in
                    the output file (integer number >= 0, unlimited = 0)
   -o,--output      output file name (a temporary XML file with the same
                    name but with the extension xml is also created)
   -r,--release     major release of the database engine that generated the
                    input trace file (7|8|9|10|11)
   -s,--sys         report information about SYS recursive statements
                    (no|yes)
   -t,--template    name of the XSL template used to generate the output
                    file (html.xsl|text.xsl)
   -v,--version     print product version and exit
   -w,--wait        report detailed information about wait events (no|yes)
   -x,--logging     logging level (severe|warning|info|fine|finer)

The function of each argument is as follows:

• input specifies the name of the input file. The input file must be either a trace file (extension.trc) or a compressed file (extension .gz or .zip) that contains one or several trace files. Note, however, that only a single trace file is extracted from .zip files.
• output specifies the name of the output file. During processing, a temporary XML file is created with the same name as the output file but with the extension .xml. Be careful, if another file with the same name as the output file exists, it will be overwritten.
• cleanup specifies whether the temporary XML file generated during processing is removed at the end. Generally, it should be set to yes. This argument is important only during the development phase to check intermediate results.
• feedback specifies whether progress information is displayed. It is useful during the processing of very large trace files to know the current status of the analysis. The argument specifies the interval (number of lines) at which a new message will be generated. If it is set to 0, no progress information is displayed.
• help specifies whether to display help information. It cannot be used along with other arguments.
• limit sets the maximum number of elements present in lists (for example, the lists used for SQL statements, waits, and bind variables) available in the output file. If it is set to 0, there is no limit.
• release specifies the major release number (that is, 7, 8, 9, 10, or 11) of the Oracle database engine that generated the input trace file.
• sys specifies whether information about recursive SQL statements that are executed by the user SYS are available in the output file. It is commonly set to no.
• template specifies the name of the XSL template used to generate the output file. By default, two templates are available: html.xsl and text.xsl. The former generates an HTML output file, and the latter generates a text output file. The default templates can be modified and new templates can be written as well. In this way, it is possible to fully customize the output file. The templates must be stored in the subdirectory templates.
• version specifies whether to display the version number of TVD$XTAT. It cannot be used along with other arguments.
• wait specifies whether detailed information for wait events is shown. Enabling this feature (that is, setting this argument to yes) might have a significant overhead during processing. Therefore, I suggest you set it initially to no. Afterward, if the basic wait information is not enough, you can run another analysis with it set to yes.
• logging specifies which messages are shown by the logging framework.

My book, Troubleshooting Oracle Performance, fully describes how to leverage the output generated by TVD$XTAT to identify performance problems.

Limitations and Know Problems

This is a beta version and, therefore, some functionality is still missing:

• The template text.xsl is not available yet.