Three Great Things You Didn’t Know About SQLT

On June 29, 2013, in Oracle SQL Tuning, by Stelios Charalambides
English: Labeled (SQL) database icon with shor...

English: Labeled (SQL) database icon with shortcut identification (Photo credit: Wikipedia)

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

 

exec delete_column_hgrm
(p_owner=> ‘<OWNER>’,
p_tabname=> ‘<TABLE_NAME>’,
p_colname=>'<COLUMN NAME>’,
p_cascade_parts=>'<TRUE| FALSE>’,
p_no_invalidate=>'<TRUE| FALSE>’,
p_force=>'<TRUE| FALSE>’);

 

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 11.4.5.9 of SQLT (March 2013) there are a couple of utilities called

 

XPREXT
XPREXC

 

These are the EXPRESS versions of the XTRACT and XECUTE routines, which execute the scripts in sqltcommon11.sql

 

The command to call this is

 

@sqltxprext <sql-id>

 

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’);

 

to

 

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

 

sqlt_s<SQLT_ID>_tc.zip

 

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

 

sqlt_s<SQLT_ID>_tcx.zip

 

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

 

SQL> @install

 

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.

 

 

Leave a Reply



Buy My Book!

Archives

All entries, chronologically...

Visit our friends!

A few highly recommended friends...