Configured and use of SQLT Reports

SQLT is a tool used to check the SQL statement and generate set of diagnostics files in HTML format. SQLT used to analyze a SQL Statement with help of SQL_ID and SQL_TEXT.
Setup of SQLT create two users: SQLTXPLAIN(unlock) and SQLTXADMIN(already locked).

Installing SQLT tool in Oracle

1. Download the SQLT software from oracle support.

2. Go to the directory SQLT\install folder.
cd SQLT\install
3. Connect with the database as sysdba
sqlplus / as sysdba
4. For setup SQLT run the following command:
SQL> START sqcreate.sql;
5. Verify the DBA_USERS view for user creation
SQL> select username,account_status from dba_users where username like 'SQLTX%';

USERNAME        ACCOUNT_STATUS
--------------  -----------------
SQLTXPLAIN      OPEN
SQLTXADMIN      EXPIRED & LOCKED

Uninstall steps for SQLT

1. Go to the directory SQL\install folder
cd SQLT\install
2. Connect with the database as sysdba
sqlplus / as sysdba
3. For uninstall the SQLT run the following command:
SQL> START sqdrop.sql
4. For check the DBA_USERS for SQLT user existence:
SQL> select username,account_status from dba_users where username like 'SQLTX%';

no row found

SQLT has 7 Methods to generate diagnostics report:

1.XTRACT Method is used if you have the SQL_ID or HASH_VALUE of the SQL statement, Mostly used method.
a) Go to directory SQLT\run
cd SQLT\run
b) Connect with database
sqlplus / as sysdba
c) Start the method
SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
Example
SQL> START sqltxtract.sql 0k5uut65g6vb sqltxplain_password

2. XECUTE Method it’s executes the SQL statement which need to analyzed then it produce output report.If SQL query take long time to execute then chose XTRACT Method. For running need to create file for SQL query which used by method.
Note: For using this method you need to create a file which have SQL query . if SQL query is using bind variable then you need to declare it in file.
Note: For Insert/update /delete operation you need to create savepoint for transaction and rollbackup upto that savepoint
( So, if not having knowledge then please do not use this method)
a. Got to directory SQLT\run
cd SQLT\run
b. Connect with database
sqlplus / as sysdba
c. Start the method
SQL>START sqltxecute.sql [path]\scriptname.sql [sqltxplain_password]
Example
SQL>START sqltxecute.sql D:\script.sql sqltxplain_password

3. XTRXEC Method: used both method XTRACT and XECUTE.
XTRACT generated a SQL script with bind variable for XECUTE Method.
XTRACT take bind variable as peak value of execution plan.
a. Go to directory SQLT\run
cd SQLT\run
b. Connect with database
sqlplus / as sysdba>
c. Start the method
SQL> START sqltxtrxec.sql [sql_id]|[HASH_VALUE] [ssqltxplain_password]
Example:
SQL> START sqltxtrxec.sql 0k5uut65g6vb sqltxplain_password

4. XTRSBY Method is used to analyze SQL on DATA Guard or Stand by databases with SQL_ID or HASH VALUE. Create a database link of standby database from primary database. It’s used db_link in command
a. Go to directory sqlt\run
cd sqlt\run
b. Connect with primary database
sqlplus / as sysdba
c. Start the method:
SQL> START sqltxtrsby.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password] [DB_LINK]
Example:
SQL> START sqltxtrsby.sql 0k5uut65g6vb sqltxplain_password db_link_name

5. XPLAIN Method is based on the EXPLAIN PLAN FOR command,
therefore it is blind to bind variables referenced by your SQL statement.For this method you need to create SQL text file.
Note: Use this method only if XTRACT or XECUTE are not possible.
a. Go to directory sqlt\run
cd sqlt\run
b. Connect with primary database
sqlplus / as sysdba
c. Start the method:
SQL>START sqltxplain.sql [path]filename [sqltxplain_password]
Example:
SQL> START sqltxplain.sql D:\sql1.sql sqltxplain_password

6. XPREXT Method used if you already used XTRACT method for faster execution of SQLT while disabling some features.
Check features disable with sqlt/run/sqltcommon11.sql
a. Go to directory sqlt\run
cd sqlt\run
b. Connect with database
sqlplus / as sysdba
c. Start the method:
SQL> START sqltxprext.sql [SQL_ID]|[HASH_VALUE] [sqltxplain_password]
Example:
SQL> START sqltxprext.sql 0k5uut65g6vb sqltxplain_password

7. XPREXC Method used if you already used XECUTE method for faster execution of SQLT while disabling some features.
Check features disable with sqlt\run\sqltcommon11.sql
a. Go to directory sqlt\run
cd sqlt\run
b. Connect with database
sqlplus / as sysdba
c. Start the method:
SQL> START [path]sqltxprexc.sql [path]scriptname [sqltxplain_password]
Example:
SQL>START sqltxprexc.sql D:\script1.sql sqltxplain_password