What is SQLTXPLAIN?
OK, I will start with the basics…
SQLTXPLAIN, also known as SQLT for short, is a tool I developed by myself and to myself. At least that was the idea when its name was coe_xplain.sql. That was long ago… like in the past millenia or so.
Today, SQLT is a tool that Oracle uses to diagnose SQL statements performing poorly. So, it went from a single-user tool into one that is used by hundreds of engineers at Oracle. Thanks to the valuable contribution of ideas from some of the brightest minds at Oracle Support in the area of query tuning, SQLT was reborn between the years 2008 and 2012. In particular I thank Abel Macias, Mauro Pagano and Mark Jefferys for their constant flow of ideas and by reporting to me my own flaws. I also thank the more than 650 participants of my two SQL Tuning Workshops, since it is in these sessions where I have learned about most of the enhancements I have implemented over the past 3 years or so.
Anyways, back to the question “What is SQLTXPLAIN?”. This is a tool developed in PL/SQL that inputs one SQL statement and output a zip file that contains many pieces. Some are for diagnostics, and some for further use as a Test Case (TC) is created. So, when someone at Oracle asks for a SQLTXPLAIN, basically the request is to install this tool and execute one of its main methods in order for the requestor to analyze your issue.
SQLT does not come pre-installed, nor it comes with the product. To get it to work, you need to download it first from My Oracle Support (MOS). You can find it under Doc ID 215187.1. After you download it, please unzip it and read included file sqlt_instructions.html. These instructions are going to basically ask you to copy this sqlt.zip file to the database server, unzip it there again, and execute sqlt/install/sqcreate.sql connected as SYS. Provide a value to the inline parameters, and SQLT should be installed in a few minutes. If you notice the sqlplus session exists then something went wrong. Read the *.log files and look for errors. Fix them or at least try. If you get stuck, contact the tool author (me).
Once SQLT is installed then you are ready to use it. Start with method XTRACT. Refer to the instructions provided with the tool. Basically, connect into sqlplus as the application user and execute sqlt/run/sqltxtract.sql passing the SQL_ID of the SQL that you want to analyze. SQLT XTRACT will generate a zip file. Open it up and look for file sqlt_sNNNNN_main.html. Try to make sense out of it. Start with links on SQL Text, Plans Summary and Observations. Then take it from there.
What you will see in the main html report is basically the entire environment around your SQL. I mean in terms of performance. Everything that may affect your SQL performance: from CBO statistics, to parameters, to bind variables, etc. You will find details about cursor sharing, SQL Plan Management (SPM), SQL Profiles, Stored Outlines, etc. Tons of details. Not everything is used every time. What is important is that everything is there in case you need it. Even more, it is there in case someone with more expertise in SQL tuning may need it later on.
SQLT works on RAC. It works also on a distributed environment. It will soon work on data-guard (stand-by read-only) databases. If you want to see a sample of its output, download it from same MOS 215187.1. Feedback on this tool is always welcomed.
Carlos Sierra's Tools and Tips 
[…] SQLTXPLAIN includes into the sqlt/utl sub-directory a script named coe_xfr_sql_profile.sql. This script inputs a SQL_ID, then it asks to choose from a list of known Plan Hash Values (PHV), and after a few seconds it outputs another script that contains the Outline of the PHV together with the SQL Text for the given SQL_ID. […]
Custom SQL Profile and Plan Stability on 10g « Oracle SQL Tuning Notepad
April 10, 2012 at 9:58 am
[…] SQLTXPLAIN (SQLT) 11.4.4.4 was made available on April 2, 2012. Find this tool under MOS 215187.1. It contains 2 fixes and 37 enhancements: […]
SQLT 11.4.4.4 is now available « Oracle SQL Tuning Notepad
April 10, 2012 at 9:58 am
[…] SQLTXPLAIN (SQLT) is a tool widely used to analyze SQL statements in terms of performance. It has a small drawback although: It requires to create a new schema name SQLTXPLAIN, then over a hundred schema objects on it. Some Production environments have small and un-frequent maintenance windows that make it hard to plan on an expedite installation of SQLT. In such cases, this other tool SQL Health-Check (SQLHC) provides a quick workaround. […]
SQL Health-Check 101 « Oracle SQL Tuning Notepad
April 10, 2012 at 9:58 am
[…] SQLTXPLAIN, also know as SQLT, is a tool that helps to diagnose SQL statements performing poorly in an Oracle database. This tool is free to download and use. You only need to have access to My Oracle Support (MOS) and search for Document Id: 215187.1. […]
SQLTXPLAIN 101 « Oracle SQL Tuning Notepad
April 10, 2012 at 10:03 am
[…] not strictly a new feature, Carlos Sierra (@csierra_usa) made sure I mention his passion for sqltxplain, a tool that will gather and save all information it can find about a given SQL statement for […]
favourite Oracle 12c database features of OpenWorld Bloggers | portrix systems
October 7, 2012 at 10:46 pm
[…] best tool for tuning SQL is SQLTXPLAIN because it collects all the information that is required for tuning SQL. The author of this tool is […]
The golden rule of NOT tuning SQL « So Many Oracle Manuals, So Little Time
February 7, 2013 at 8:23 pm
[…] (SPM). We will discuss today some SQL Tuning tools: Event 10046, Trace Analyzer TRCANLZR, SQLTXPLAIN (SQLT), SQL Health-Check (SQLHC) and SQLT Test Case (TC). There are around 35 participants and I […]
SQL Tuning Workshop at the NoCOUG « Oracle SQL Tuning Tools and Tips
February 20, 2013 at 8:30 am
[…] SQLTXPLAIN became to be“. I wrote it as a foreword for Stelios Charalambides book on SQLTXPLAIN. If you want to learn more about SQLTXPLAIN you may want to consider reading Stelios […]
How SQLTXPLAIN became to be… | Oracle SQL Tuning Tools and Tips
April 18, 2013 at 7:49 am
Hi Carlos, your article is very interesting. I am trying to download the SQLT script, but I can’t do it because I don’t have Oracle Support ID. Could you please help me by sending the zip file to me? I very appreciate your help. Thank you…
Tommy
June 8, 2013 at 10:50 pm
done!
Carlos Sierra
June 9, 2013 at 5:42 am
Hi Carlos,
I am trying to obtain a copy of SQLTXPLAIN but am out of work and trying to increase my skills in tuning. Would it be that you would send me a copy, to orcl@me.com?
Thanks very much in advance,
Michael Milligan
Michael
August 13, 2013 at 12:32 am
sent!
Carlos Sierra
August 13, 2013 at 7:13 am
message was rejected. please verify your email address. send me message to carlos.sierra.usa@gmail.com
Carlos Sierra
August 13, 2013 at 7:15 am
Hi Carlos , is sqlt only to oracle Enterprise Edition? ,we have standard edition, is a free tool.
the docuent 215187.1 says :
Applies to .
Oracle Application Object Library – Version 11.5.10.0 and later
Oracle Database – Enterprise Edition – Version 10.2.0.1 and later
Information in this document applies to any platform.
Checked for relevance 08-MAY-2011
thanks!
jose.nadin
June 18, 2013 at 12:38 pm
SQLT also works on Standard Edition.
Carlos Sierra
June 18, 2013 at 1:56 pm
[…] colleague, Carlos Sierra, the author of famous SQLT tool, also did excellent analysis for the same issue from SQLT perspective and here is the link to […]
AWR is not Enough to Track Down IO Problem on Exadata | My Big Data World
August 23, 2013 at 5:47 pm
[…] like very much Tanel Poder snapper and Carlos Sierra SQLTXPLAIN . They represent valuable performance diagnostic tools. Unfortunately I am still waiting to find a […]
SQLTXPLAIN under Oracle 12c | Mohamed Houri’s Oracle Notes
November 18, 2013 at 3:14 am
[…] use Carlos Sierra SQLTXPLAN […]
Tuning via row source execution plan | Mohamed Houri’s Oracle Notes
December 25, 2013 at 11:26 am
Carlos,
Would this tool allow me to stop using the Diagnostics Pack with OEM or are the two complimentary
Tony M
February 7, 2014 at 8:08 am
Tony,
Good question. Instead of replying here I will post about it now.
Carlos
Carlos Sierra
February 7, 2014 at 5:08 pm
Great tool Carlos,
Any idea why it does not provide recommedation as in OEM sometimes?
Thanks,
Paul
Paul
June 4, 2014 at 3:17 pm
Paul,
SQLT does not compete with Oracle SQL Tuning Advisor. SQLT does provide health-checks although.
Carlos
Carlos Sierra
June 4, 2014 at 3:59 pm
Hi Carlos,
I have purchased the book Oracle SQL Tuning with Oracle SQLTXPLAN by Stelios Charalambides and I want to play with SQLT tool in my home pc by applying every concept of this book. However I don’t have My Oracle Support id to download it. Could you please send me the zip file?
Thanks,
Srikant
srikant
June 20, 2014 at 2:45 pm
Srikant,
You may want to contact Mauro Pagano at Oracle for this. His email is mauro.pagano@oracle.com
Carlos Sierra
June 21, 2014 at 2:47 pm
Hi Carlos,
Can I use SQLTXPLAN tool in Oracle standard edition?, am not able to find this information in Oracle metalink.
Regards,
Kiran
kiran
October 16, 2014 at 2:04 am
Kiran, Yes you can use SQLTXPLAIN on Oracle Standard Edition. You may miss some pieces on the output, but for the most part it works just fine.
Carlos Sierra
October 16, 2014 at 9:10 am
Hey Carlos, who’s responsible for maintaining SQLT – you or Oracle? Thanks!
David Litchfield
October 21, 2014 at 10:39 pm
David,
It is Oracle. MOS 215187.1. Mauro Pagano takes care of SQLT. You can contact him at his email: mauro.pagano@oracle.com
Carlos Sierra
October 23, 2014 at 12:21 pm
Thank you!
David Litchfield
October 28, 2014 at 1:36 am
Thanks a lot for the update.
kiran
October 29, 2014 at 3:58 am
Hi Carlos, I am trying to download the SQLT script, but I can’t do it because I don’t have Oracle Support ID. Could you please help me by sending the zip file to me? I very appreciate your help. Thank you…
Sridhar
January 21, 2015 at 5:21 pm
You may want to contact abel.macias@oracle.com
Carlos Sierra
January 22, 2015 at 8:25 am
[…] so I did it- I installed SQLTXPLAIN, (SQLT) on the AWR Warehouse! From previous experience with this fantastic tool, I was pretty […]
SQLTXPLAIN and the AWR Warehouse, Part I | DBA Kevlar
March 18, 2015 at 7:03 pm
Hello Carlos, I am Facundo from Argentina.
If I run SQLT (@coe_xfr_sql_profile.sql) on a RAC node, the results are for both nodes or only a local node?
Thanks.
Facundo Ezequiel Grande
March 22, 2016 at 8:21 am
All nodes
Carlos Sierra
March 22, 2016 at 9:39 am
[…] their databases and servers directly. Fortunately Oracle has utilities such as AWR and scripts like SQLTXPLAIN to help diagnose the system in your absence. If you are very fortunate, you may even get them to […]
Working with Oracle trace files using TKPROF | Jon Adams, OCP, MCTS
May 26, 2016 at 11:02 am
[…] In his own words: […]
The Oracle Database professional I admire most - Oracle Blog - Oracle - Toad World
June 30, 2016 at 10:50 am
Carlos,
Thank you for coe_xfr_sql_profile.sql
It is an invaluable tool for us. It has been most helpful for SQL that seems to confuse the optimizer!
thx John
John R
October 3, 2016 at 10:53 pm
i Carlos,
I am trying to download the SQLTXPLAN zip file, but I can’t do it because I don’t have Oracle Support ID. Could you please help me by sending the zip file to me?
I very appreciate your help.
Thank you somuch
tu
October 15, 2016 at 11:37 am
Use SQLd360 instead
Carlos Sierra
October 25, 2016 at 8:13 am
Hi Carlos,
Im from Viet Nam
I am trying to download the SQLT zip file, but I can’t do it because I don’t have Oracle Support ID.
So, could you please help me by sending the zip file to me?
I very appreciate your help.
Thank you
tu
October 16, 2016 at 12:13 am
Use SQLd360 instead
Carlos Sierra
October 25, 2016 at 8:12 am
Hi Carlos,
Thanks for the SQLT tool,very nice tool. I like to know the difference between sqlt and SQLd360 tool.
Thank you
manjeet
November 30, 2016 at 12:08 pm
In short, SQLT is the old while SQLd360 is the new. SQLT requires installation while SQLd360 does not. SQLd360 is the next generation.
Carlos Sierra
November 30, 2016 at 12:45 pm
Hi Carlos:
Im from México.
I am trying to download the SQLT zip file, but I can’t do it because I don’t have Oracle Support ID.
So, could you please help me by sending the zip file to me?
Thank you somuch
EDUARDO GARCIA
February 1, 2017 at 5:33 pm
The script (coe_xfr_sql_profile.sql), I got it, but running it sends me the following errors:
WHENEVER SQLERROR EXIT SQL.SQLCODE;
REM
VAR signature NUMBER;
REM
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
]’;
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]’,
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “SYS.XMLTYPE”, line 272
ORA-06512: at line 1
ORA-06512: at line 66
I hope you can help me.
Greetings and thanks for attention
EDUARDO GARCIA
February 1, 2017 at 5:45 pm
Can you send me the script that throws the error? carlos.sierra.usa@gmail.com
Carlos Sierra
February 1, 2017 at 9:33 pm
hey
I have the same problem
There is some workaround
blazej
May 16, 2017 at 3:24 am
Please send me the script that throws the error
Carlos Sierra
May 16, 2017 at 4:20 am
Is there any workaround?
blazej
May 16, 2017 at 3:26 am
Hi Carlos,
I had a question. If I install SQLT with “None” as my license pack, and then I buy a Tuning pack later on. So to enable SQLT with Tuning pack after its installation with “None”, can I just use “exec sqltxadmin.sqlt$a.enable_tuning_pack_access;”, Or will I have to reinstall SQLT with “T” option?
Akhil
June 6, 2017 at 11:23 am
Not sure if that has changed. Please contact abel.macias@oracle.com
Carlos Sierra
June 6, 2017 at 1:22 pm
[…] site: https://carlos-sierra.net/2012/04/03/what-is-sqltxplain/ […]
9 Ferramentas de Tuning grátis que você deve conhecer | DBTimeWizard
December 27, 2017 at 4:45 am
Hello,
I have a doubt about sqltxplain.
I loaded an AWR dump using awrload.sql in an enviroment for testing
When I try to use sqltxtract,
START sqltxtract.sql g4pkmrqrgxg3b
I received an error like “sqlid is not in memory and AWR ”
the same error using sqlhc
do you know if there is other way to check an sqlid from an AWR dump (awrextr.sql)?
Regards
efraimi
October 4, 2023 at 7:36 pm
No I don’t. SQLTXTRACT requires the SQL to be present either in memory (shared pool) or on the AWR repository.
Carlos Sierra
October 5, 2023 at 1:56 pm