SQLTXPLAIN

Introduction

SQLTXPLAIN is an Oracle that is maintained and supported by Oracle, but there is not much publicity about this tool. It is an excellent tool for tuning SQL Statements.

Download the tool

• You can download the tool as a zip file from My Oracle Support.
• Enter the search string MOS ID 215187.1 and you see the relevant MOS Note "All About the SQLT Diagnostic Tool (Doc ID 215187.1)"
• Click on the Download Link and select your version of the database. Then download the file to your database environment.
• Rename the .txt extension to .zip and extract the zip file.

Install the tool in the database

Navigate to the install directory and run the following:

$ cd sqlt/install
$ sqlplus / as sysdba
SQL> @sqcreate.sql

-- now you have to enter a few parameters when asked. I entered the following

=> Optional Connect Identifier (ie: @PROD):           -- leave blank

=> Password for user SQLTXPLAIN: sqlt
=> Re-enter password: sqlt

=> Default tablespace [UNKNOWN]: USERS

=> Temporary tablespace [UNKNOWN]: TEMP

=> Main application user of SQLT: rob_app

=> Oracle Pack license [T]: T                         -- stands for Tuning and Diagnostics. Other options D (Diagnostics) and N (None)

-- after this the scripts will run
-- the last few lines of the script read the following:

SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully.

If we query the data dictionary we can see what was created:

SQL> col owner format a15
SQL> select owner, object_type, count(*) from dba_objects where created > sysdate -2 group by owner, object_type order by owner, object_type;

OWNER           OBJECT_TYPE               COUNT(*)
--------------- ----------------------- ----------
SQLTXADMIN      PACKAGE                         17
SQLTXADMIN      PACKAGE BODY                    17
SQLTXADMIN      SYNONYM                        247
SQLTXADMIN      TYPE                             6
SQLTXADMIN      VIEW                           105
SQLTXPLAIN      INDEX                          244
SQLTXPLAIN      LOB                             97
SQLTXPLAIN      PROCEDURE                        1
SQLTXPLAIN      SEQUENCE                        12
SQLTXPLAIN      SYNONYM                         17
SQLTXPLAIN      TABLE                          215
SQLTXPLAIN      TABLE PARTITION                 32
SYS             DIRECTORY                        7
SYS             INDEX PARTITION                 64
SYS             LOB PARTITION                    4
SYS             PROCEDURE                        1
SYS             TABLE PARTITION                 36
SYS             VIEW                            14

18 rows selected.