Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

What is SQLTXPLAIN?

with 47 comments

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.

Advertisements

Written by Carlos Sierra

April 3, 2012 at 4:24 pm

Posted in SQLTXPLAIN (SQLT)

Tagged with ,

47 Responses

Subscribe to comments with RSS.

  1. […] 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. […]

  2. […] 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: […]

  3. […] 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. […]

  4. […] 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. […]

  5. […] 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 […]

  6. […] 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 […]

  7. […] (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 […]

  8. […] 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 […]

  9. 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

  10. 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

  11. […] 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 […]

  12. […] like very much Tanel Poder snapper and Carlos Sierra SQLTXPLAIN . They represent valuable performance diagnostic tools. Unfortunately I am still waiting to find a […]

  13. […] use Carlos Sierra SQLTXPLAN […]

  14. 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

  15. 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

  16. 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

  17. Hey Carlos, who’s responsible for maintaining SQLT – you or Oracle? Thanks!

    David Litchfield

    October 21, 2014 at 10:39 pm

  18. 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

  19. […] so I did it-  I installed SQLTXPLAIN, (SQLT) on the AWR Warehouse!  From previous experience with this fantastic tool, I was pretty […]

  20. 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

  21. […] 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 […]

  22. 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

  23. 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

  24. 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

  25. 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

  26. 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

  27. 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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: