SQL T - Trace File Analysis Tool

SQLT (SQLTXPLAIN) is a tool that inputs one SQL statement and outputs a set of comprehensive diagnostic files for SQL performance analysis and tuning.

***Note*** These notes are from the SQLT user documentation

Comprehensive HTML report that includes an enhanced explain plan, details about the tables accessed by the SQL, their indexes, their columns, CBO statistics including Histograms, partitions and subpartitions if applicable, initialization parameters in effect, stored outlines associated to the SQL, SQL profiles if any, child plans, metrics of SQL execution performance, observations, etc.

SQL T is available via Metalink note 215187.1. Download SQLT.zip and follow the directions to install it.

Since the tool is installed into its own schema and makes use of temporary objects for most of its data structures, it is light and moderate intrusive. The schema owner SQLTXPLAIN only gets granted a small subset of roles and attributes in order to generate SQL analysis results. This SQLTXPLAIN schema owner does not read application data itself.

· This SQLT tool installs into its own schema SQLTXPLAIN
· It does not install any objects into application schemas
· Its footprint is very small
· The RDBMS version can be 9i, 10g or 11g
· The OS can be UNIX, LINUX or WINDOWS
· Source is not wrapped (SQL and PL/SQL)
· It can be installed in RAC systems
· It installs by executing just one script sqcreate.sql

**Note** More information is available by running it as a SYSDBA.

SQLT inputs one SQL DML statement provided as one of these methods:
1. XPLAIN: As a stand-alone SQL in a flat text file.
2. XTRACT: As a memory-resident SQL, identified by its hash_value or sql_id.
3. XECUTE: As a stand-alone script that contains one SQL, together with the declaration and values of its bind variables.
4. XTRXEC: As a combination of XTRACT and XECUTE, this method gets a SQL from memory and executes both XTRACT and XECUTE.

The recommended methods are XECUTE and XTRACT. Try avoiding the XPLAIN method since the Explain Plan generated by it may not be accurate if your SQL contains bind variables.

SQLT runs various traces and collects information from a variety of sources including AWR.

The main.html doc…everything is hyperlinked and all information about this SQL statement is included. This document includes all stats, tablespace info, CBO explain plan info from the 10053 trace, and more!

This tool literally exposes everything you would want to know about a particular SQL statement and how it interacted with the Oracle RDBMS.