SQLTXPLAIN

Oracle SQLTXPLAIN:
-----------------------------------
SQLTXPLAIN is a tool that generates a comprehensive report about a SQL statement which has been provided as input to the tool. The report includes the explain plan for a given SQL statement, cost-based optimizer statistics for the objects accessed by the statement, and details of related objects. The output HTML report produced can be used to analyze and diagnose SQL performance issues (SQL tuning).

Unlike SQL statements "Explain Plan" and "Autotrace", SQLTXPLAIN offers complete environment and configuration reports relating the SQL we exmaines. The report is categorized as followings.

Environment
SQL Identification
SQL Statement
Explain Plan
Observations
Object Dependency
Objects
Tables
Indexes
Table Columns
Index Columns
Constraints
Column Histograms
Metadata
Tablespaces
Initialization Parameters
Optimizer Trace (EVENT 10053)
Tool Configuration Parameters

During the installation of SQLTXPLAIN, the following parameters will be asked for value.
Specify SQLTXPLAIN password:

Enter value for host_string: (For instance, it your db name is prod, then input @prod)
Enter value for application_schema: (the schema under which you examine sql statement)
Enter value for default_tablespace:
Enter value for temporary_tablespace:

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.

SQLT provides the following benefits:
1. Consistent snapshot of most commonly used pieces of information associated to a SQL analysis and tuning effort, like CBO statistics and performance dynamic views. For most of these objects, inconsistent snapshots of different objects would render the analysis inaccurate or inconclusive.
2. Reduce the number of trips (thus overall time) between people involved in the SQL analysis and tuning. This is specially relevant in systems where the person doing the analysis is external to the organization or does not have full access to the system experiencing poor performance.
3. Preserves CBO statistics and initialization parameters in effect at the time of the analysis, making it easier to reverse the environment to a known state. Further more, the associated CBO statistics can easily be migrated to a similar system in order to try to replicate the same plan good or bad, thus the query performance.
4. Since the tool is installed into its own schema and makes use of temporary objects for most of its data structures, it is lite 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.

The below metalink note will gove you the full details regarding SQLTXPLAIN tool and its usage. The software is also available in the same note.

215187.1: SQLT (SQLTXPLAIN) - Enhanced Explain Plan and related diagnostic information for one SQL

For 9i, 10g and 11g instances, download tool from sqlt.zip. Read intructions.txt included in zip file.
For output samples (9i, 10g and 11g), download sqlt_sample.zip
For 8i instances, download and use old tool from sqlt_old.zip