SQLT Tips

Many readers are familiar with Oracle's Explain Plan functionality to show how the Oracle database engine plans on executing a given SQL statement. Oracle also provides a utility named SQLTXPLAIN, affectionately known as SQLT. SQLT was created to fill in many gaps missing from the traditional Explain Plan. In addition to showing how the SQL statement will be executed, SQLT provides object statistics, object metadata, optimizer-related initialization parameters, and just about every bit of information the database administrator would want to know when tuning a SQL statement for optimal performance. SQLT produces an html file with many hyperlinks to all of the sections in that report.

SQLT can be downloaded from Note 215187.1 on My Oracle Support for those that have a valid Oracle Support contract. The utility comes as a simple zip file. Before SQLT can be used, it must be installed in the database. Installation will create two schemas, sqltxplain and sqltxadmin. The utility was unzipped to the directory /home/oracle/sqlt and the installation is started below. The installation should be performed as a sysdba user.

SQL> connect / as sysdba

SQL> @/home/oracle/sqlt/install/sqcreate.sql

PL/SQL procedure successfully completed.

The installation can be performed in the local database by entering nothing for the Connect Identifier, or in a remote database by specifying a TNS alias.

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

The password for the schema owner is provided.

Define SQLTXPLAIN password (hidden and case sensitive).

Password for user SQLTXPLAIN:

Re-enter password:

PL/SQL procedure successfully completed.

... please wait

The schema owner needs a permanent and a temporary tablespace. Note that the tablespace name is case sensitive.

TABLESPACE                     FREE_SPACE_MB

------------------------------ -------------

USERS                                  22064

SOE                                 33552498

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.

Initially, one user is given the appropriate role to run SQLT in the database. If desired, additional users can run SQLT by granting them the sqlt_user_role role.

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 use the optional Diagnostic and Tuning packs provided they are licensed. If none of these are licensed, answer N to keep in compliance with current license agreements. In the example below, both packs will be used.

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]: T

Specifying the licensing is the final question asked by the installation script. The script will now create all of the database objects, which may take some time. The installation is complete when the following messages appear.

SQLT users must be granted SQLT_USER_ROLE before using this tool.

SQCREATE completed. Installation completed successfully.

If the SQL statement to be tuned is in the Shared Pool and the sql_id value is known, the sqlxtract.sql script can be used to generate the report. The password for the sqltxplain user must be provided. Since the password was not provided as a parameter to the script, the script will prompt for it.

SQL> @/home/oracle/sqlt/run/sqltxtract.sql b5sgm4db0pjhj

PL/SQL procedure successfully completed.

Parameter 1:

SQL_ID or HASH_VALUE of the SQL to be extracted (required)

Paremeter 2:

SQLTXPLAIN password (required)

Enter value for 2: password

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQLT_VERSION

----------------------------------------

SQLT version number: 12.1.09

SQLT version date  : 2015-06-13

Installation date  : 2015-07-24/11:24:45

... please wait ...

  adding: alert_orcl.log (deflated 91%)

NOTE:

You used the XTRACT method connected as PEASLAND.

In case of a session disconnect please verify the following:

1. There are no errors in sqltxtract.log.

2. Your SQL b5sgm4db0pjhj exists in memory or in AWR.

3. You connected as the application user that issued original SQL.

4. User PEASLAND has been granted SQLT_USER_ROLE.

In case of errors ORA-03113, ORA-03114 or ORA-07445 please just

re-try this SQLT method. This tool handles some of the errors behind

a disconnect when executed a second time.

To actually diagnose the problem behind the disconnect, read ALERT

log and provide referenced traces to Support. After the root cause

of the disconnect is fixed then reset SQLT corresponding parameter.

To monitor progress, login into another session and execute:

SQL> SELECT * FROM SQLTXADMIN.sqlt$_log_v;

... collecting diagnostics details, please wait ...

In case of a disconnect review log file in current directory

If running as SYS in 12c make sure to review sqlt_instructions.html first

At this point, SQLT will begin its data collection and will create a large zip file. The work is complete when messages similar to the following are shown.

  adding: sqlt_s34043_sqldx.zip (stored 0%)

SQLTXTRACT completed.

The zip file will have the SQL id number in the file name along with the type of SQLT utility that was run, xtract in this case.

[oracle@host01 ~]$ ls -l sqlt*.zip

-rw-r--r-- 1 oracle oinstall 1681777 Sep 15 11:28 sqlt_s34043_xtract_b5sgm4db0pjhj.zip

When the zip file is expanded, there will be a number of files but the one that starts the SQLT report is titled sqlt_id_main.html where id is the identifier number of the SQLT run. In the example above, the id is 's34043? so the file that starts the report is sqlt_s34043_main.html. The file can be opened in any web browser. The top of the file is the main area and contains a table of contents for the entire report.

Each section of the report can be reached by clicking on one of the hyperlinks that point to the relevant location in the document. Some items will not have hyperlinks if SQLT has chosen not to generate that section of the report. For instance, this query does not use bind variables so the Peeked Binds and Captured Binds sections were not generated. Every section in the report has a link at the bottom of that section that brings the user back to the top, or back to the section shown in the figure above.

The section after the main table of contents is often the most illuminating as to the performance of the query. The Observations section provides analysis SQLT has offered as the knowledge expert looking at all of the data collected for this query. An example of the Observations section can be seen below.

The user can click on the plus symbol in the Details column to the right of each observation to obtain more information.

When tuning SQL statements, the database administrator always wants to see the Explain Plan. It is no surprise that SQLT includes the Explain Plan in its report just for this information in the Execution Plan section. A sample Execution Plan section can be seen below.

The SQLT report sample seen so far was generated with the sqltxtract.sql script. This script can only be used when the SQL statement is in the Shared Pool and the sql_id value can be determined. One of the benefits of the sqltxtract.sql script is that the SQL statement is not executed, which can be very useful when trying to analyze long running SQL statements. This extract method works great, but nothing gathers more information for SQLT than letting SQLT execute the statement and watching it in action. SQLT provides the sqltxecute.sql script to analyze a SQL statement that SQLT will execute. The SQL statement must be in a text file and contain a specific comment, seen below, in the text that SQLT will use to help identify it in the Shared Pool. The file my_query.sql below contains the query to be analyzed. Notice the comment in the SELECT clause. When running the sqltxecute.sql script, use this exact comment.

[oracle@host01 ~]$ cat my_query.sql

SELECT /* ^^unique_id */

 o.order_date, d.detail_id

FROM scott.orders o

JOIN scott.order_details d

ON o.order_id=d.order_id;

For those SQL statements that modify database, SQLT will create a savepoint and rollback the transaction to that savepoint upon completion. The sqltxecute.sql script is run very similarly to the sqltxtract.sql script, but instead of the sql_id value, the SQL script name is provided.

SQL> @/home/oracle/sqlt/run/sqltxecute.sql my_query.sql

The sqltxtract.sql and sqltxecute.sql scripts are the two most common methods to invoke SQLT. This section has provided what is only an introduction to SQLT. Since there is very little SQLT functionality just for Oracle RAC systems, this book will skip diving into SQLT in more detail. This section was provided because SQLT is part of the RAC Support Tools bundle from Oracle Support. The SQLT utility has lots of functionality in its arsenal and the reader is encouraged to further research this tool.