Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Oracle SQLT

Amazon RDS supports Oracle SQLTXPLAIN (SQLT) through the use of the SQLT option.

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.

To download SQLT and access instructions for using it, log in to your My Oracle Support account, and open the following documents:

You can use SQLT with any edition of the following Oracle Database versions:

  • Oracle 12c, 12.1.0.2

  • Oracle 11g, 11.2.0.4

Amazon RDS does not 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:

    Copy
    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

T

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.

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 11.2 or 12.1.

    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 a DB Instance Running the Oracle Database Engine.

    2. Run the following query:

      Copy
      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:

      Copy
      ALTER USER SQLTXADMIN IDENTIFIED BY new_password ACCOUNT UNLOCK;
    3. Run the following SQL statement to change the password for the SQLTXPLAIN user:

      Copy
      ALTER USER SQLTXPLAIN IDENTIFIED BY new_password ACCOUNT UNLOCK;

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 11g to Oracle 12c.

Using SQLT

SQLT works with the Oracle SQL*Plus utility.

To use SQLT

  1. Download the SQLT .zip file from Document 215187.1 on the My Oracle Support site.

  2. Unzip the SQLT .zip file.

  3. From a command prompt, change to the sqlt/run directory on your file system.

  4. From the command prompt, open SQL*Plus, and connect to the DB instance as the master user.

    For information about connecting to a DB instance using SQL*Plus, see Connecting to a DB Instance Running the Oracle Database Engine.

  5. Get the SQL ID of a SQL statement:

    Copy
    SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT='sql_statement';

    Your output is similar to the following:

    
    SQL_ID
    -------------
    chvsmttqjzjkn                
                
  6. Analyze a SQL statement with SQLT:

    Copy
    START sqltxtract.sql sql_id sqltxplain_user_password

    For example, for the SQL ID chvsmttqjzjkn, enter the following:

    Copy
    START sqltxtract.sql chvsmttqjzjkn sqltxplain_user_password

    SQLT generates the HTML report and related resources as a .zip file in the directory from which the SQLT command was run.

  7. (Optional) To enable application users to diagnose SQL statements with SQLT, grant SQLT_USER_ROLE to each application user with the following statement:

    Copy
    GRANT ROLE SQLT_USER_ROLE TO application_user_name;

    Note

    Oracle does not recommend running SQLT with the SYS user or with users that have the DBA role. It is a best practice to run SQLT diagnostics using the application user's account, by granting SQLT_USER_ROLE to the application user.

Modifying SQLT Settings

After you enable SQLT, you can modify the LICENSE_PACK setting 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 a DB Instance Running the Oracle Database Engine.

Related Topics