Most of the overall database performance issues (not caused by SQL tuning) are pretty complex to setup because they require a workload and it takes quite some work to reproduce concurrency in a test environment where at best you got a couple folks testing some new code. Nowadays there are ways to reproduce a realistic workload (ie. Real Application Testing) but still it’s not a 2 minutes deal.

One of the reasons why I like SQL Tuning so much is the ability to easily (dis)prove my hypotheses and so being able to gain a little more confidence on the root cause and how to fix the problem, the famous “I tested it!”.
First, how can you test (/reproduce) sql tuning problems? Building a testcase
Second, can SQLT make it easier for me? Of course, otherwise I would not be writing this post 😀

Regardless where your SQL runs, the optimizer will work the same.
I think of the optimizer as a deterministic mathematical model where given the same inputs (SQL, binds, stats, etc etc) I will receive the same output (execution plan), so to get the same output we just need to provide the same input!!
In summary to build a testcase we “just” need to collect all those info the optimizer uses when trying to find an optimal plan for our SQL, problem is putting all the pieces together and packing them in some sort of portable way may require a lot of time if performed manually.

For every run of SQLT (no matter the method used) a testcase is packed together into a zip file and added to the main output file. The archive name is sqlt_sNNNNN_tc.zip and it contains everything we need

The list of files is long but several scripts are just utilities that come handy when playing with a testcase, the most important files from the list above are:

1. sqlt_sNNNNN_metadata.sql -> the DDL for all the objects involved in the SQL including dependencies
2. sqlt_sNNNNN_exp.dmp -> a dmp of the SQLT repository from the source system, it includes stats plus some info we might need when remapping columns
3. sqlt_sNNNNN_system_stats.sql -> the system statistics, so the CBO thinks the hw on the target system matches the source
4. sqlt_sNNNNN_set_cbo_env.sql -> the CBO environment from the source system
5. q.sql -> the SQL including binds

So how do we install the testcase? Still sounds a lot of work looking at all those files!
Inside the main zip file there is a html readme with all the instructions, follow “Implement SQLT Test Case (TC)”

Good news is SQLT provides a “hat” script (xpress.sql) that takes care of starting all the other scripts in the right order so from our point of view all it takes to install a testcase is unzip the file and start xpress.sql.

There is also a “custom” mode where you can manually start each script so that you have the flexibility to perform any other operation in between each step (ie. load some data)

It takes probably less than a couple minutes to install most of the testcases and once you have it in place it’s just a matter of proving your hypothesis right (or wrong) without having to touch the source system in any way.