SQLT INSTALLATION AND DEINSTALLATION

Introduction
Performance tuning is one of the most critical responsibilities of an Oracle DBA. When an SQL statement runs slowly, consumes excessive CPU, performs unnecessary I/O, or suddenly changes execution plans after an upgrade or statistics refresh, identifying the root cause can be challenging.

Oracle provides SQLTXPLAIN (SQLT), a free diagnostic utility developed by Oracle Support, to simplify SQL performance analysis. SQLT automatically collects all relevant information about a SQL statement into a single report, allowing DBAs and Oracle Support engineers to identify and resolve performance issues more efficiently.

Why Should a DBA Install SQLT?

A DBA should install SQLT because it significantly reduces the time required to diagnose SQL performance issues.

Without SQLT, a DBA typically needs to execute numerous queries against Oracle’s data dictionary views, such as V$SQL, DBA_TAB_STATISTICS, DBA_HIST_SQLSTAT, DBA_OBJECTS, and many others, then manually correlate the information.

SQLT automates this process by collecting all relevant diagnostic information with a single command. This saves time, minimizes the risk of missing important details, and provides a standardized report that Oracle Support can use to reproduce and analyze issues.

What Problem Does SQLT Solve?

SQLT addresses several common SQL performance problems, including:

Slow-running SQL statements
High CPU utilization caused by inefficient SQL
Excessive logical or physical I/O
Optimizer choosing inefficient execution plans
SQL performance regressions after upgrades or patching
Execution plan changes due to statistics modifications
SQL statements consuming excessive resources
Difficulties reproducing performance issues

Installation

Step 1: Find SQLT Already Installed or Not

SELECT
‘SQLT version number: ‘||sqltxplain.sqlt$a.get_param(‘tool_version’)||CHR(10)||
‘SQLT version date : ‘||sqltxplain.sqlt$a.get_param(‘tool_date’)||CHR(10)||
‘Installation date : ‘||sqltxplain.sqlt$a.get_param(‘install_date’) sqlt_version
FROM DUAL;
———————————————————–

‘Installation date : ‘||sqltxplain.sqlt$a.get_param(‘install_date’) sqlt_version

ERROR at line 4:

ORA-00904: “SQLTXPLAIN”.”SQLT$A”.”GET_PARAM”: invalid identifier

It means it SQLT is not yet installed.

Step 2: Download the sqlt

KB94658 – SQLTXPLAIN (SQLT) Download and Main Reference Document

Once download and move the zip file into the server under respective directory.

mkdir -p /home/oracle/sqlt

Step 3:- Installation SQLT:-
A sqcreate.sql is the main SQLT installation script.It creates the SQLT repository schema (typically SQLTXPLAIN), installs all required packages, tables, views, procedures, and grants necessary privileges to enable SQLT for collecting and analyzing SQL performance diagnostics in the database.
[oracle@jafeen onlinelog]$ cd /home/oracle/sqlt/
[oracle@jafeen onlinelog]$ unzip sqlt_latest.zip
[oracle@jafeen onlinelog]$ cd /home/oracle/sqlt/install
[oracle@jafeen onlinelog]$ sqlplus / as sysdba

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0

SQL> @sqcreate.sql

zip error: Nothing to do! (SQLT_installation_logs_archive.zip)

PL/SQL procedure successfully completed.

Specify optional Connect Identifier (as per Oracle Net) Include “@” symbol, ie. @PROD If not applicable, enter nothing and hit the “Enter” key. You MUST provide a connect identifier when installing SQLT in a Pluggable Database in 12c This connect identifier is only used while exporting SQLT repository everytime you execute one of the main methods.

Optional Connect Identifier (ie: @PROD): <– Just hit enter

PL/SQL procedure successfully completed.

Define SQLTXPLAIN password (hidden and case sensitive).

Password for user SQLTXPLAIN: <— Enter the password Re-enter password:

PL/SQL procedure successfully completed.

… please wait

TABLESPACE FREE_SPACE_MB

————- —————–

EXAMPLE 216
USERS 32735

Specify PERMANENT tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Default tablespace [UNKNOWN]: USERS

PL/SQL procedure successfully completed.

… please wait

TABLESPACE

——————————

TEMP

Specify TEMPORARY tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Temporary tablespace [UNKNOWN]: TEMP

PL/SQL procedure successfully completed.

The main application user of SQLT is the schema owner that issued the SQL to be analyzed. For example, on an EBS application you would enter APPS. You will not be asked to enter its password. To add more SQLT users after this installation is completed simply grant them the SQLT_USER_ROLE role.

Main application user of SQLT: scott

PL/SQL procedure successfully completed.

SQLT can make extensive use of licensed features provided by the Oracle Diagnostic and the Oracle Tuning Packs, including SQL Tuning Advisor (STA), SQL Monitoring and Automatic Workload Repository (AWR). To enable or disable access to these features from the SQLT tool enter one of the following values when asked:

“T” if you have license for Diagnostic and Tuning “D” if you have license only for Oracle Diagnostic “N” if you do not have these two licenses

Oracle Pack license [T]:

PL/SQL procedure successfully completed.

TADOBJ completed.

PL/SQL procedure successfully completed. .. .. SQLT users must be granted SQLT_USER_ROLE before using this tool. <—-

SQCREATE completed. Installation completed successfully.

SQL>

Step 4 :- Verification:-

SQL> SELECT

‘SQLT version number: ‘||sqltxplain.sqlt$a.get_param(‘tool_version’)||CHR(10)||

‘SQLT version date : ‘||sqltxplain.sqlt$a.get_param(‘tool_date’)||CHR(10)||

‘Installation date : ‘||sqltxplain.sqlt$a.get_param(‘install_date’) sqlt_version

FROM DUAL; 2 3 4 5

SQLT_VERSION

——————————————————————————–

SQLT version number: 23.1.240824

SQLT version date: 2024-08-24

Installation date: 2026-07-27/11:11:42

2.Uninstallation

When Should SQLT Be Removed?

SQLT should only be removed when it is no longer required for SQL performance diagnostics or before decommissioning a database. Common scenarios include:

The SQL performance investigation has been completed.

Oracle Support has confirmed that SQLT is no longer needed.

The database is being decommissioned or rebuilt.

Organizational security or compliance policies require the removal of unused diagnostic tools.

You want to clean up the SQLTXPLAIN schema after temporary use.

Impact of Removing SQLTXPLAIN

Dropping the SQLTXPLAIN schema has the following impact:

All SQLT packages, procedures, tables, views, and reports are permanently removed.

Previously collected SQLT diagnostic data is deleted.

SQLT can no longer be used until it is reinstalled.

There is no impact on user schemas, application data, Oracle Database functionality, or database performance.

Oracle Support may request you to reinstall SQLT if future SQL performance issues need investigation.

Precautions Before Dropping the SQLTXPLAIN Schema

Before removing SQLT, ensure the following:

Verify that no active SQL performance analysis is in progress.

Back up or archive any SQLT reports that may be required for future reference.

Confirm that Oracle Support does not require the existing SQLT data for any open Service Request (SR).

Ensure no users are currently executing SQLT scripts.

Take a backup of the SQLTXPLAIN schema if you want to preserve historical diagnostic information.

Schedule the removal during a maintenance window, following your organization’s change management process.

Step 1:-
[oracle@jafeen onlinelog]$ cd /home/oracle/sqlt/install

[oracle@jafeen onlinelog]$ ls -ltr sqdrop.sql
-rw-r–r– 1 oracle oinstall 1365 Oct 30 2014 sqdrop.sql
[oracle@jafeen onlinelog]$ sqlplus / as sysdba

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0

SQL> @sqdrop.sql

PL/SQL procedure successfully completed.

… uninstalling SQLT, please wait

TADOBJ completed.

PL/SQL procedure successfully completed.

SQDOLD completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQDOBJ completed. Ignore errors from this script

PL/SQL procedure successfully completed.

SQL>

SQL> DECLARE

2 my_count INTEGER;

4 BEGIN

5 SELECT COUNT(*)

6 INTO my_count

7 FROM sys.dba_users

8 WHERE username = ‘TRCADMIN’; <—

10 IF my_count = 0 THEN

11 BEGIN

12 EXECUTE IMMEDIATE ‘DROP PROCEDURE sys.sqlt$_trca$_dir_set’;

13 EXCEPTION

14 WHEN OTHERS THEN

15 DBMS_OUTPUT.PUT_LINE(‘Cannot drop procedure sys.sqlt$_trca$_dir_set. ‘||SQLERRM);

16 END;

18 FOR i IN (SELECT directory_name

19 FROM sys.dba_directories

20 WHERE directory_name IN (‘SQLT$UDUMP’, ‘SQLT$BDUMP’, ‘SQLT$STAGE’, ‘TRCA$INPUT1’, ‘TRCA$INPUT2’, ‘TRCA$STAGE’))

21 LOOP

22 BEGIN

23 EXECUTE IMMEDIATE ‘DROP DIRECTORY ‘||i.directory_name;

24 DBMS_OUTPUT.PUT_LINE(‘Dropped directory ‘||i.directory_name||’.’);

25 EXCEPTION

26 WHEN OTHERS THEN

27 DBMS_OUTPUT.PUT_LINE(‘Cannot drop directory ‘||i.directory_name||’. ‘||SQLERRM);

28 END;

29 END LOOP;

30 END IF;

31 END;

32 /

Dropped directory TRCA$INPUT2.

Dropped directory TRCA$INPUT1.

Dropped directory SQLT$BDUMP.

Dropped directory SQLT$UDUMP.

Dropped directory TRCA$STAGE.

Dropped directory SQLT$STAGE.

PL/SQL procedure successfully completed.

SQL>

SQL> WHENEVER SQLERROR CONTINUE;

SQL>

SQL> PAU About to DROP users &&tool_repository_schema. and &&tool_administer_schema.. Press RETURN to continue.

About to DROP users SQLTXPLAIN and SQLTXADMIN. Press RETURN to continue.

SQL>

SQL> DROP USER &&tool_administer_schema. CASCADE;

old 1: DROP USER &&tool_administer_schema. CASCADE

new 1: DROP USER SQLTXADMIN CASCADE <—

User dropped.

SQL> DROP USER &&tool_repository_schema. CASCADE;

old 1: DROP USER &&tool_repository_schema. CASCADE

new 1: DROP USER SQLTXPLAIN CASCADE <—

User dropped.

SQL> DROP ROLE &&role_name.;

old 1: DROP ROLE &&role_name.

new 1: DROP ROLE SQLT_USER_ROLE <—

Role dropped.

SQL>

SQL> SET ECHO OFF;

SQDUSR completed.

SQDROP completed.

SQL>

Notes / Precautions

Always install SQLT in a non-production environment first to validate compatibility and familiarize yourself with its functionality before deploying it in production.
Verify Oracle Diagnostic Pack and Tuning Pack licensing before using SQLT, as some SQLT features may access licensed Oracle performance management components.
Keep SQLT updated by installing the latest version available from Oracle Support to benefit from bug fixes, new features, and compatibility with the latest Oracle Database releases.
Remove SQLT installations only when they are no longer required, such as after completing performance investigations or when decommissioning the database, to maintain a clean and well-managed environment.

Conclusion

Mastering the lifecycle management of SQLT ensures that your Oracle environment remains clean and secure while leveraging advanced SQL diagnostic capabilities. Proper setup requires careful attention to tablespace allocation and license configuration, ensuring you accurately select options (T, D, or N) based on your organization’s Oracle Diagnostic and Tuning Pack entitlements.

Additionally, understanding how to completely purge SQLT via sqdrop.sql is vital when upgrading the tool or decommissioning performance testing setups. Following this structured installation and deinstallation steps guarantees a seamless, conflict-free deployment of one of Oracle’s most effective performance troubleshooting utilities.

Shankar Narayanan

We take you from best practices to next practices of software implementation

Solutions that Ensure You Stay Ahead of Competition

Your Partner for Strategic Technology Solutions

Guiding Your Digital Transformation Journey

We take you from best practices to next practices of software implementation

Solutions that Ensure You Stay Ahead of Competition​

Your Partner for Strategic Technology Solutions

Guiding Your Digital Transformation Journey

SQLT INSTALLATION AND DEINSTALLATION

Solutions that Ensure You Stay Ahead of Competition