Oracle : Running SQLTXPLAIN.sql

Installation:

SQLT installs under its own schemas SQLTXPLAIN and SQLTXADMIN. It does not install any objects into the application schema(s). You can install this version of SQLT in Oracle databases 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms.

Installation steps:

Uninstall a prior version (optional).
This optional step removes all obsolete SQLTXPLAIN/SQLTXADMIN schema objects and prepares the environment for a fresh install. Skip this step if you want to preserve the existing content of the SQLT repository (recommended).

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdrop.sql
Execute installation script sqlt/install/sqcreate.sql connected as SYS.
# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcreate.sql
During the installation you will be asked to enter values for these parameters:

Optional Connect Identifier (mandatory when installing in a Pluggable Database)
In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key. Entering nothing is the most common setup.
The Connect Identifier is a mandatory parameter when installing SQLT in a Pluggable Database.

SQLTXPLAIN password.
Case sensitive in most systems.

SQLTXPLAIN Default Tablespace.
Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.

SQLTXPLAIN Temporary Tablespace.
Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.

Optional Application User.
This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won't be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE.

Licensed Oracle Pack. (T, D or N)
You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.

Running SQLTXPLAIN

For brief instructions and feedback, please refer to the INSTRUCTIONS.TXT file included on same SQLT.zip downloaded file.

Start by uncompressing latest file SQLT.zip into one dedicated directory from where you can connect into SQL*Plus.

The file sql.txt, whose filename is provided as an inline parameter when SQLTXPLAIN.SQL is executed, has some restrictions and characteristics explained below.  If you were not provided with one sql.txt file, create it and place under same dedicated directory where you placed all SQLT scripts.  SQLTXPLAIN.SQL will try to open file <sql.txt> under same directory where you placed all SQLT scripts.

Restrictions and characteristics for file sql.txt:

It is a plain text file (flat file) with one and only one valid SQL Statement to be explained (SELECT, INSERT, UPDATE or DELETE),
It cannot have empty lines (blank lines),
At the very end of the file, after the very last character of the SQL Statement, one and only one 'carriage return' ('enter' or 'line feed') should be provided, with no spaces before or after it (review file sql0.txt provided as an example),
The SQL Statement should NOT have a semicolon ';' at the end,
If you get an error similar to 'Bind variable "b2" not declared', you have empty lines within the SQL Statement, or at the end (review sql0.txt provided as a correct example),
Do NOT replace bind variables with literals.  Since the SQLTXPLAIN.SQL script does not execute the SQL Statement provided on the <sql.txt> file, there is no need to replace the bind variables on it.  Actually, by replacing the bind variables with literals, the resulting Explain Plan can change substantially and may lead to confusion or false conclusions,
The filename <sql.txt> is NOT hard-coded.  Therefore, if multiple SQL Statements are being diagnosed, use filenames sql1.txt, sql2.txt, sql3.txt, etc.; or any other set of names,
File sql.txt is usually created out of the TKPROF by extracting a specific expensive SQL Statement.  File sql.txt is normally created with a simple Cut&Paste OS command into one new flat file,
File sql0.txt is provided as an example only (use it to test SQLTXPLAIN.SQL on APPS databases)
To execute the SQLTXPLAIN.SQL script, login into SQL*Plus.  If using Oracle APPS, login with APPS USER and password.  If using on a non-APPS database, connect into SQL*Plus with same USER that CAN execute the SQL Statement provided within the <sql.txt> file.  Be aware that the USER executing the SQLTXPLAIN.SQL script must have access to the objects specified in the sql.txt file, PLUS to the 'ALL_', 'DBA_' and 'V$' views (see also SQLTGRANT.SQL).

# sqlplus apps/<apps_pwd>
SQL> START SQLTXPLAIN.SQL sql.txt;
SQLTXPLAIN.SQL creates three files: SQLTXPLAIN.LOG, SQLT_<statement_id>.TXT and a Raw SQL Trace.  The first two are the log file and the actual report.  Use any text editor to review them (TextPad and WordPad work fine).  Be sure your lines are not wrapping around.  These two files are usually created under the same directory from where SQLTXPLAIN.SQL was executed.  On NT, these files may get created under $ORACLE_HOME/bin instead.

In most cases, the *.TXT report is enough to understand a specific Explain Plan.  In some cases, two additional files may be required:

Raw SQL Trace generated by SQLTXPLAIN.SQL.  Generated always under the 'udump' directory on the database server.  Filename and exact location can be found on the report or log files.  Please do not TKPROF this particular raw SQL Trace.
Export file of the table SQLT$STATTAB, which contains the CBO Stats related to the SQL Statement <sql.txt>.  See Migrating CBO Stats across Similar Instances below for details on its use.  To export this table SQLT$STATTAB, use ORACLE_HOME 8.0.6 (if using Oracle APPS), and execute command below.  Keep in mind that exported file is always BINARY and should be treated as such (if XFR as ASCII, this BINARY file gets corrupted).