I have recently used SQLTXPLAIN to solve a production performance issue. This post is about my experiences with this tool.
- Oracle SQL Tuning with Oracle SQLTXPLAIN
- SQLT Diagnostic Tool (Doc ID 215187.1)
- FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions (Doc ID 1454160.1)
SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise – ST CoE. SQLT inputs one SQL statement and outputs a set of diagnostics files. These files are commonly used to diagnose SQL statements performing poorly. SQLT connects to the database and collects execution plans, Cost-based Optimizer CBO statistics, schema objects metadata, performance statistics, configuration parameters, and similar elements that influence the performance of the SQL being analyzed.
This tool is free from My Oracle Support, but the full power of this tool requires licences for Diagnostics and Tuning packs which, in turn, require an Oracle Enterprise Edition licence.
Several of the DBAs at work have used this tool to provide information to Oracle Support through the EXTRACT process. A few brave souls have tried to make sense of the wealth of reports that are produced.
A very useful guide for how to read these reports can be found in Oracle SQL Tuning with Oracle SQLTXPLAIN. We have a copy floating around the office.
My Recent Experience
The installation of this tool has issues for a production environment:
- It has a large footprint of procedures, a separate user-name, new roles, and access to production user-names.
- For OEM products, such as SAP, a new table-space needs to be created to store the tool. This requires negotiation with the application support people to make sure that they understand the appearance of a new table-space in their database. I chose the unimaginative name of USERS.
- This also applies to the new user-name of SQLTXPLAIN, and associated role.
- What is more difficult to explain is the necessity of using proxy access to production user-names (see Technical Note on Using #SQLTXPLAIN Through a Proxy User). I suspect that the application support team just reason that the DBAs have access to everything already, this level of access is not more of a security risk than normal.
The problem for which SQLTXPLAIN was very useful concerned a SQL statement that performed much better in one (1) database than in another. The application support team was adamant that there were no differences between the two (2) databases.
What I did was to:
- Install SQLTXPLAIN into both databases after getting the appropriate change approval.
- Run an EXTRACT against the SQL in both databases.
- Created a third database and installed SQLTXPLAIN there. Into this database, I imported the dump files from these two (2) EXTRACT runs.
- Run a COMPARE report in this third database to see what the differences were.
- Created a fourth database and installed SQLTXPLAIN as well. I imported the test case dump file from the EXTRACT run done in the poorly reporting database.
- Run the test case without modification to confirm that the same plan as occurred in the poorly performing database.
- Change the settings in the fourth database to see if I could reproduce the good plan.
The Compare report had highlighted several differences between the good and badly performing databases:
- Different database parameters
- Different sizes of the underlying objects
- Different sets of indices
These differences allowed me to plan my testing. In the end, I only had to change the database parameters to match that of the good-performing database in order to reproduce the good plan.
Now, I have to find the reasons for the differences in the database parameters.
SQLTXPLAIN is a very powerful tool. User expectations need to be carefully managed prior to installing it in a production database. This tool is for DBAs only as it requires SYSDBA access to operate.
The purchase of Oracle SQL Tuning with Oracle SQLTXPLAIN is highly recommended if one to use the tool successfully. However, My Oracle Support has several articles about how to use this tool. The FAQ can be found at FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions (Doc ID 1454160.1).