What is SQLTXPLAIN (SQLT)?
And its Use cases:
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.
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.
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.
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.
Free add-on tool that helps to diagnose SQL statements performing poorly
Inputs one SQL statement and outputs a set of diagnostics reports and files including a
Test Case.
Frequently used by Support and Development
Newest SQLT code available for 10.2 and 11g
Older versions for 8i, 9i and 10.1 are available
Same SQLT code for UNIX, Linux and Windows
Self-contained in its own schema: SQLTXPLAIN
Un-wrapped readable PL/SQL
Comprehensive content
An Execution Plan has many dependencies
Consistent view
Captures the environment around the SQL statement
Current and Historical
Plans, statistics, parameters, metrics, active sessions, etc.
Improves remote diagnostics
Includes everything that may affect execution of one SQL
Reduces round-trips
Thus expedites diagnostics
When to use SQLT
• After an upgrade
• After gathering statistics
• Unstable plans over time or RAC nodes
• After known changes in the environment
• Different performance in two similar systems
• When “nothing” has changed
• To understand current and past plans
• When requested by Support or Development
How to use SQLT
• Download latest version from MOS Doc ID 215187.1
• Install in database system
• Select appropriate method
• XTRACT when SQL_ID is available
• XECUTE when detailed execution metrics are desired
• XTRXEC to get everything from XTRACT and XECUTE
• XPLAIN when XTRACT and XECUTE are not feasible
• XTRSBY when SQL executed on a read-only database
• Review diagnostics reports
• Focus on MAIN
Using SQLT Main Methods
• XTRACT
• XECUTE
• XTRXEC
• XPLAIN
• XTRSBY
Which method to use?
• If SQL was executed on a standby read-only database use XTRSBY on Primary
• If SQL takes less than 1hr to execute use XTRXEC
• If SQL takes more than 1hr to execute use XTRACT
• If you have to have A-Rows use either
1. XECUTE
2. Parse SQL with STATISTICS_LEVEL = ALL or with Hint /*+ gather_plan_statistics */ and
use XTRACT
• If SQL has no binds or bind peeking is disabled
XPLAIN is OK
How to run SQLT to find out the best plan:
conn as application user/sysdba (Preferred method is connecting as app user)
@/export/home/oracle/sqlt/run/sqltxtract.sql <SQLID>
It produces the output like sqlt_s<STATEMENT_ID>_xtract_<SQL_ID>.zip
In that zip file, open the sqlt_s<STATEMENT_ID>__main.html
Plan Summary
OEM Tuning Advisor
Report
Select “Summary” option to check available plans information also its show which is best plan and
worst plan.
Creating custom profile with best plan
conn as sysdba
@/export/home/oracle/sqlt/utl/coe_xfr_sql_profile.sql <SQLID> <HASVALUE>
Its creates another Sql file, which should be run to create manual Sql profile for the sql.
And its look likes coe_xfr_sql_profile_<sql_id>_<plan hash value>.sql
Execute the above Sql script as sysdba to create custom profile for a specific sql_id.
How to compare the SQL statements in two different environments:
Usually user’s complaints Query is running fine in lower environment and it’s not running in
higher environment or vice versa. In this scenario we can use SQLT Compare option to compare the
both environments to find the gaps.
Using SQLT COMPARE:
We need to have a set of SQLT files (sqlt_sNNNNN_method.zip) from two executions of the SQLT tool.
They can be from any method (XTRACT, XECUTE or XPLAIN) and they can be from the same or different
systems. They do not have to be from same release or platform. For example, a SQLT from 10g on
Linux and a SQLT from 11g on Unix can be compared.
To use the COMPARE method you need 3 systems: SOURCE1, SOURCE2 and COMPARE. The 3 could all be
different, or all the same. For example, SOURCE1 could be PROD, SOURCE2 DEV and COMPARE DEV. In
other words, you could do the COMPARE in one of the sources. Or the COMPARE could be done on a 3rd
and remote system.
Basically you need to restore the SQLT repository from both SOURCES into the COMPARE system. In
most cases it means "restoring" the SQLT repository from at least one SOURCE into the COMPARE.
Once you have both SQLT repositories into the COMPARE system, then you can execute this method.
Steps:
Unzip sqlt_s86881_tc.zip from this SOURCE in order to get sqlt_s86881_expdp.dmp.
Copy sqlt_s86881_exp.dmp to the server (BINARY).
Execute import on server:
imp sqltxplain FILE=sqlt_s86881_exp.dmp TABLES=sqlt% IGNORE=Y
Perform the equivalent steps for the 2nd SOURCE if needed. You may want to follow its readme file.
Execute the COMPARE method connecting into SQL*Plus as SYS. You will be asked to enter which 2
statements you want to compare.
START sqlt/run/sqltcompare.sql
# cd sqlt
# sqlplus sqltxplain
SQL> START [path]sqltcompare.sql [STATEMENT_ID 1] [STATEMENT_ID 2]
SQL> START run/sqltcompare.sql 36748 88079
SQL> START run/sqltcompare.sql
Sample output:
Copying custom profiles from once source to another source:
When we need to create outlines in source system to apply on target system, SQLT is the one
of the easiest method to do this.
Generate SQT output and find the best plan is source system and create custom profile like below.
Provide input with the sql_id and hash value from source system to generate custom profile and run
in source system.
@/export/home/oracle/sqlt/utl/coe_xfr_sql_profile.sql <SQLID> <HASVALUE>
And it produces the sql file like “coe_xfr_sql_profile_<sql_id>_<plan hash value>.sql”
Copy this sql file and run in target system.
Note: A SQL Profile contains additional statistics of this SQL statement for the query optimizer
to generate a better execution plan. An outline contains hints for this SQL statement for the
query optimizer to generate a better execution plan.
SQL Tuning Health-Check Script (SQLHC) [ID 1366133.1]
If we don’t have SQLT in our system for emergency we can use the below attached script to
get diagnostic report for a particular sql_id.
$SQL>@sqlhc.sql T <SQL_ID>
