Looking for an elegant companion for SQL Tuning ? — The answer is: SQLT.
SQLT is a tool originally developed and still regularly updated, maintained by Carlos Sierra. You can visit his website for loads of information on this wonderfully crafted tool, which is one of the most essential tool a DBA must have so as to easily debug lots and lots of background and supportive information on a badly performing SQL query on any version of Oracle database the DBA’s responsible for. Idea behind this article is to provide you all with a basic knowledge of this tool and how to install this tool on our database(s). Following posts would include more details on its usage, sample SQLT reports for you to learn more, some of the most important scripts which are part of this tool, the most important sections in the SQLT output and how to read/interpret them, and lots more.
I would also request all of you who’s reading this article now to keep an eagle’s eye on Carlos’s website for (almost) daily updates on this tool, its usage, and various other aspects of it, which would help you intensely in resolving SQL Query tuning issues in your day to day job.
No — It’s not a tool which would list a number of steps to solve the problem !!!
In today’s rapidly changing world of Technology, Oracle databases provide many features which are often missed out by many DBA’s. It seems more of us are not looking for a great assistant to work with for resolving critical issues on our Production databases, but rather, we are seeking out for some intelligent software systems which can hover through our 10053 trace files and let us know there’s the bottleneck, and most importantly, what we can “run” on the SQL*Plus prompt to get rid of it. May this not be the intention of the readers of this article!
SQLT would provide you with utmost quality information about all aspects of a poorly running SQL – Statistics, histograms, tables, execution plans, system parameters, etc. It would not have a “Solution” section wherein commands would be listed out for you to run and resolve the issue. It would provide us, however, with enough information so that we can find and fix the root cause of the problem.
It is quite a straight-forward tool, and can be downloaded from the Oracle Metalink. Those who have a valid CSI (Customer Support Identifier) can login to Oracle Support and search for SQLT in the Knowledge area, or you can also search with the Metalink ID: 215187.1. You can now scroll to the bottom of the note and choose the version of SQLT suitable for your environment. There are currently versions suitable from 9i to 11 g.
Download the version, specific to your database, and unzip it. When I installed it on Linux, I did it on $HOME of my ORACLE user which created a folder called “sqlt” (note the lower case!) inside which I could find the installation/un-installation and other scripts.
To install the tool simply run the “sqcreate.sql” script which would install this tool. During installation you would be required for some inputs. Below are the steps which are involved typically in the installation:
- Connect as sys, e.g., sqlplus / as sysdba
- Make sure your database is running
- Run the sqcreate.sql script.
- Select the default for the first option.
- Enter and confirm the password for SQLTXPLAIN (the owner of the SQLT packages).
- Select the tablespace where the SQLTXPLAIN will keep its packages and data.
- Select the temporary tablespace for the SQLTXPLAIN user (in my case, TEMP).
- Then enter the username of the user in the database who will use SQLT packages to fix tuning problems. Typically this is the schema that runs the problematic SQL.
- Then enter “T”, “D” or “N.” This reflects your license level for the tuning and diagnostics packs. Most sites have both so you would enter “T”, (this is also the default).
- If you have the diagnostics pack, only enter “D”;
- and if you do not have these licenses, enter “N”.
The last message you see is “SQCREATE completed. Installation completed successfully.”
Well — it’s that simple! Once the installation is complete, we can, just to make sure the required and important scripts would run properly, manually login to the database with the SQLTXPLAIN user with the password (provided above in the steps of installation). If there are any issues with login, it should be resolved at this time!