Oracle SQLT - Amazon Relational Database Service
PrerequisitesOption settingsAdding the optionModifying the settingsRemoving SQLT
This documentation is a draft for private preview for regions in the AWS European Sovereign Cloud. Documentation content will continue to evolve. Published: January 8, 2026.

Oracle SQLT

Amazon RDS supports Oracle SQLTXPLAIN (SQLT) through the use of the SQLT option. You can use SQLT with any edition of Oracle Database 19c and higher.

The Oracle EXPLAIN PLAN statement can determine the execution plan of a SQL statement. It can verify whether the Oracle optimizer chooses a certain execution plan, such as a nested loops join. It also helps you understand the optimizer's decisions, such as why it chose a nested loops join over a hash join. So EXPLAIN PLAN helps you understand the statement's performance.

SQLT is an Oracle utility that produces a report. The report includes object statistics, object metadata, optimizer-related initialization parameters, and other information that a database administrator can use to tune a SQL statement for optimal performance. SQLT produces an HTML report with hyperlinks to all of the sections in the report.

Unlike Automatic Workload Repository or Statspack reports, SQLT works on individual SQL statements. SQLT is a collection of SQL, PL/SQL, and SQL*Plus files that collect, store, and display performance data.

Following are the supported Oracle versions for each SQLT version.

SQLT version Oracle Database 21c Oracle Database 19c

2018-07-25.v1

Supported

Supported

2018-03-31.v1

Not supported

Not supported

2016-04-29.v1

Not supported

Not supported

To download SQLT and access instructions for using it:

Amazon RDS doesn't support the following SQLT methods:

  • XPLORE

  • XHUME

Prerequisites for SQLT

The following are prerequisites for using SQLT:

  • You must remove users and roles that are required by SQLT, if they exist.

    The SQLT option creates the following users and roles on a DB instance:

    • SQLTXPLAIN user

    • SQLTXADMIN user

    • SQLT_USER_ROLE role

    If your DB instance has any of these users or roles, log in to the DB instance using a SQL client, and drop them using the following statements:

    
DROP USER SQLTXPLAIN CASCADE;
DROP USER SQLTXADMIN CASCADE;   
DROP ROLE SQLT_USER_ROLE CASCADE;

  • You must remove tablespaces that are required by SQLT, if they exist.

    The SQLT option creates the following tablespaces on a DB instance:

    • RDS_SQLT_TS

    • RDS_TEMP_SQLT_TS

    If your DB instance has these tablespaces, log in to the DB instance using a SQL client, and drop them.

SQLT option settings

SQLT can work with licensed features that are provided by the Oracle Tuning Pack and the Oracle Diagnostics Pack. The Oracle Tuning Pack includes the SQL Tuning Advisor, and the Oracle Diagnostics Pack includes the Automatic Workload Repository. The SQLT settings enable or disable access to these features from SQLT.

Amazon RDS supports the following settings for the SQLT option.

Option setting Valid values Default value Description

LICENSE_PACK

T, D, N

N

The Oracle Management Packs that you want to access with SQLT. Enter one of the following values:

  • T indicates that you have a license for the Oracle Tuning Pack and the Oracle Diagnostics Pack, and you want to access the SQL Tuning Advisor and Automatic Workload Repository from SQLT.

  • D indicates that you have a license for the Oracle Diagnostics Pack, and you want to access the Automatic Workload Repository from SQLT.

  • N indicates that you don't have a license for the Oracle Tuning Pack and the Oracle Diagnostics Pack, or that you have a license for one or both of them, but you don't want SQLT to access them.

Note

Amazon RDS does not provide licenses for these Oracle Management Packs. If you indicate that you want to use a pack that is not included in your DB instance, you can use SQLT with the DB instance. However, SQLT can't access the pack, and the SQLT report doesn't include the data for the pack. For example, if you specify T, but the DB instance doesn't include the Oracle Tuning Pack, SQLT works on the DB instance, but the report it generates doesn't contain data related to the Oracle Tuning Pack.

VERSION

2016-04-29.v1

2018-03-31.v1

2018-07-25.v1

2016-04-29.v1

The version of SQLT that you want to install.

Note

For Oracle Database 19c and 21c, the only supported version is 2018-07-25.v1. This version is the default for these releases.

Adding the SQLT option

The following is the general process for adding the SQLT option to a DB instance:

  1. Create a new option group, or copy or modify an existing option group.

  2. Add the SQLT option to the option group.

  3. Associate the option group with the DB instance.

After you add the SQLT option, as soon as the option group is active, SQLT is active.

To add the SQLT option to a DB instance

  1. Determine the option group that you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings:

    1. For Engine, choose the Oracle edition that you want to use. The SQLT option is supported on all editions.

    2. For Major engine version, choose the version of your DB instance.

    For more information, see Creating an option group.

  2. Add the SQLT option to the option group. For more information about adding options, see Adding an option to an option group.

  3. Apply the option group to a new or existing DB instance:

  4. (Optional) Verify the SQLT installation on each DB instance with the SQLT option.

    1. Use a SQL client to connect to the DB instance as the master user.

      For information about connecting to an Oracle DB instance using a SQL client, see Connecting to your Oracle DB instance.

    2. Run the following query:

      
SELECT sqltxplain.sqlt$a.get_param('tool_version') sqlt_version FROM DUAL;

      The query returns the current version of the SQLT option on Amazon RDS. 12.1.160429 is an example of a version of SQLT that is available on Amazon RDS.

  5. Change the passwords of the users that are created by the SQLT option.

    1. Use a SQL client to connect to the DB instance as the master user.

    2. Run the following SQL statement to change the password for the SQLTXADMIN user:

      
ALTER USER SQLTXADMIN IDENTIFIED BY new_password ACCOUNT UNLOCK;
      Note

      Specify a password other than the prompt shown here as a security best practice.

    3. Run the following SQL statement to change the password for the SQLTXPLAIN user:

      
ALTER USER SQLTXPLAIN IDENTIFIED BY new_password ACCOUNT UNLOCK;
      Note

      Specify a password other than the prompt shown here as a security best practice.

Note

Upgrading SQLT requires uninstalling an older version of SQLT and then installing the new version. So, all SQLT metadata can be lost when you upgrade SQLT. A major version upgrade of a database also uninstalls and re-installs SQLT. An example of a major version upgrade is an upgrade from Oracle Database 19c to Oracle Database 21c.

Modifying SQLT settings

After you enable SQLT, you can modify the LICENSE_PACK and VERSION settings for the option.

For more information about how to modify option settings, see Modifying an option setting. For more information about each setting, see SQLT option settings.

Removing the SQLT option

You can remove SQLT from a DB instance.

To remove SQLT from a DB instance, do one of the following:

  • To remove SQLT from multiple DB instances, remove the SQLT option from the option group to which the DB instances belong. This change affects all DB instances that use the option group. For more information, see Removing an option from an option group.

  • To remove SQLT from a single DB instance, modify the DB instance and specify a different option group that doesn't include the SQLT option. You can specify the default (empty) option group or a different custom option group. For more information, see Modifying an Amazon RDS DB instance.