“He couldn't move a mountain nor pull down a big oak tree But my Daddy became a mighty big man with a simple philosophy Do what you do, do well, boy Do what you do, do well Give your love and all of your heart And do what you do, do well” —Ned Miller
Carlos Sierra spent 17 years in the SQL performance group of Oracle Support. He couldn’t move a mountain nor pull down a big oak tree but became a mighty big man by putting all the knowledge he gained during that time into a wonderful tool called “SQLT.” Unlike Jack’s beanstalk, SQLT did not grow overnight; it took many years to evolve into the wonderful thing that it is today. The goal of SQLT is to collect all the information that may be pertinent to solving a SQL performance problem.
In his own words:
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.
Do you belong to the old school that believes that a SQL performance problem can be diagnosed using nothing more than EXPLAIN PLAN and perhaps a 10053 trace? You could not be more wrong. Here is a screen shot of the navigation section of a SQLT report. Observe how many different kinds of information are relevant to an investigation of SQL performance.
If you want to get serious about SQL tuning, then learn how to use SQLT. There’s a Support note on it. There’s even a whole book on it written by one of Carlos’s partners in crime.
If you’re not serious about SQL tuning, then please do stick with EXPLAIN PLAN and 10053 traces.