SQLT is a fabulous tool. You probably already know that, or you wouldn’t be reading this! What you may not know is that in addition to all the wonderful information SQLT provides, it also has a lot of user friendly features. For example:
(1) You Can Get Rid of Those Pesky Histograms
Hidden away, deep in the bowels of SQLT there is a utility to remove the column histogram from one column on a table or partition. You can also cascade this to all partitions. At the bottom of this script is a call to
You can find this script in <SQLT Install area>/utl/xgram and it is called sqlt_delete_column_hgrm.sql. There are cases where you are experimenting with a test case where this may be useful to determine if you can ‘fix’ an execution plan.
(2) I Don’t Want It Fast! I Want It XPRESS
When collecting information, SQLT can sometimes take a long time. This is because there is a lot of information to collect. On very large systems there is so much information to collect, SQLT can take hours to run. If you really can’t wait or don’t have the patience, you may need the express version of the common methods. In version 220.127.116.11 of SQLT (March 2013) there are a couple of utilities called
These are the EXPRESS versions of the XTRACT and XECUTE routines, which execute the scripts in sqltcommon11.sql
The command to call this is
Please note in some early versions of SQLT you need to change this line in sqltcommon11.sql
EXEC ^^tool_administer_schema..sqlt$a.set_sess_param(‘ash_reports’, ‘NONE’);
EXEC ^^tool_administer_schema..sqlt$a.set_sess_param(‘ash_reports’, 0);
(3) You don’t need SQLT?
If you want to build a test case to display some behavior of your SQL your first option is to use
which you can get from the SQLT zip file. This is described in detail in Chapter 11 of my book, but the important thing to know is that you need the installation of SQLT on your test database to be able to build this test case. Then you can use all the utilities built in to SQLT to explore the execution plan. If you need to move your test case to a database which does not have SQLT installed then you are stuck. There is no way to install the TC file into this database. Luckily Carlos (at the request of many support engineers and development) provided a way to do this. There is an alternative file
The TCX file (Test Case Xpress) is for precisely this purpose. It can be used to install the test case into your disposable database, WITHOUT SQLT being installed before you start. It does not require the SQLT repository or schema objects (functions and so on) to be able to do its work. Once you unzip the TCX file, you need only have access to SYS on your disposable database and run
This does all the usual magic required. There are no other steps. The SQL is available, the schema is created and the statistics are imported. A truly useful feature.
There are many other cool features in SQLT and I hope to document at least some of them in future posts, so stop by often or sign up for posts by email.