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.