SQLT Overview
SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise – ST CoE. SQLT main methods input one SQL statement and output a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly or those which generate wrong results.
For tuning SQL, SQLT requires some level of expertise to get the most from it. For most issues oracle recommends that you start by checking the query using SQL Health Check and then progress to SQLT if you are unable to resolve the issue.
Once installed, you can use SQLT to analyze a SQL statement by passing its text within a script (including bind variables), or by providing its SQL_ID. The SQL_ID can be found in AWR and ASH reports and the HASH_VALUE in SQL_TRACE output (above the SQL text and identified by the “hv=” token). You can also find these columns in the V$SQL view.
SQLT main methods connect to the database and collect execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and other elements that influence the performance of the one SQL being analyzed. These methods produce a set of data for the SQL_ID in question including a “main” report which displays information in html format. SQLT may use the Oracle Diagnostic and/or the Oracle Tuning Packs if your site has a license for them. These two provide enhanced functionality to the SQLT tool. During SQLT installation you can specify if one of these two packages is licensed by your site. If none, SQLT still provides some basic information that can be used for initial SQL diagnostics.
Installing SQLT
SQLT installs under its own schemas SQLTXPLAIN and SQLTXADMIN. It does not install any objects into the application schema(s). You can install this version of SQLT in Oracle databases 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms.
Installation steps:
1. Uninstall a prior version (optional). This optional step removes all obsolete SQLTXPLAIN/SQLTXADMIN schema objects and prepares the environment for a fresh install. Skip this step if you want to preserve the existing content of the SQLT repository (recommended).
# cd sqlt/install # sqlplus / as sysdba SQL> START sqdrop.sql
Execute installation script sqlt/install/sqcreate.sql connected as SYS.
# cd sqlt/install # sqlplus / as sysdba SQL> START sqcreate.sql
3. During the installation you will be asked to enter values for these parameters:
- Optional Connect Identifier (mandatory when installing in a Pluggable Database): In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the “Enter” key. Entering nothing is the most common setup. The Connect Identifier is a mandatory parameter when installing SQLT in a Pluggable Database.
- SQLTXPLAIN password: Case sensitive in most systems.
- SQLTXPLAIN Default Tablespace: Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.
- SQLTXPLAIN Temporary Tablespace: Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.
- Optional Application User: This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won’t be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE.
- Licensed Oracle Pack. (T, D or N): You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.
If a silent installation is desired, there are three options to pass all 6 installation parameters:
In a file
Executing first a script with pre-defined values, similar to sample script sqlt/install/sqdefparams.sql. Then use sqlt/install/sqcsilent.sql instead of sqlt/install/sqcreate.sql.
# cd sqlt/install # sqlplus / as sysdba SQL> START sqdefparams.sql SQL> START sqcsilent.sql
In-line
Executing sqlt/install/sqcsilent2.sql instead of sqlt/install/sqcreate.sql. The former inputs the same 6 installation parameters but in-line.
# cd sqlt/install # sqlplus / as sysdba SQL> START sqcsilent2.sql '' sqltxplain USERS TEMP '' T
Internal installation at Oracle
Executing sqlt/install/sqcinternal.sql instead of sqlt/install/sqcreate.sql. The former executes sqlt/install/sqdefparams.sql followed by sqlt/install/sqcsilent.sql.
# cd sqlt/install # sqlplus / as sysdba SQL> START sqcinternal.sql
Uninstalling SQLT
Uninstalling SQLT removes the SQLT repository and all SQLTXPLAIN/SQLTXADMIN schema objects. SQLTXPLAIN and SQLTXADMIN users also gets dropped. To uninstall SQLT simply execute sqlt/install/sqdrop.sql connected as SYS.
# cd sqlt/install # sqlplus / as sysdba SQL> START sqdrop.sql
Upgrading SQLT
If you have a prior version of SQLT already installed in your system, you can upgrade SQLT to its latest version while partially preserving most objects of your existing SQLT repository. The new migrated SQLT repository can then be used to restore CBO statistics or to perform a COMPARE between old and new executions of SQLT.
– To upgrade SQLT, simply do an installation without performing the optional uninstall step.
– If the upgrade fails, then it is possible the prior SQLT version was too old to be upgraded. In such case please proceed to uninstall SQLT first, followed by a clean installation.