Oracle SQLTXPLAIN SQLT Tutorial Example Part 1

There are many SQL Performance Tuning tools for Oracle Database spread out in the market. SQLTXPLAIN, shortly referred by SQLT is one that is becoming increasingly popular these days. This is a native performance tuning tool of Oracle, and can help to provide adequate diagnostic information for a SQL Query and help to increase the Query Performance by refining it. In this tutorial, we will try to harness the benefits and explain the usage of this tool. You will have to download the latest available version of the package provided by Oracle. We will be using version 11.4.2.7 of this Oracle tuning utility. This tutorial explains SQLT usage on a Oracle 11g Database. (11.2.0.1.0) You can still use this tutorial and comfortably use this tool against 9i and 10g versions of Oracle.

The tuning mechanism of SQLT connects to the Oracle Database to collect SQL execution plans, Optimizer statistics, metadata information and other performance specific metrics that influence the timing of a SQL statement execution.We will begin this series, by explaining how to install this utility for your database. Once this is completed, we can see how to optimize SQL queries using SQLTXPLAIN (SQLT).

The step by step installation instructions / installation guide for SQLTXPLAIN utility are documented below:

1) The utility installs in a new schema SQLTXPLAIN.If you have already made an attempt to install this tool, you can run the delivered uninstallation script to remove any traces of this tuning application.To do this, you have to execute “sqdrop.sql” located in sqlt/install directory.We will not look more into this as I don’t have this installed for my Database. If you do have, run this and clear your existing installation.

2) To begin installing SQLT, connect as SYSDBA and invoke the script “sqcreate.sql” located in sqlt/install directory. You will be prompted for a number of values during installation. The first among them is UDUMP directory. Refer to the log below

SQL> START sqcreate.sql TADOBJ completed. PL/SQL procedure successfully completed. SQDOLD completed. Ignore errors from this script ... creating SQLT$ UDUMP/BDUMP/STAGE Server Directories Notes: 1. Directories cannot contain "?", "*" or "$" symbols. 2. Specify existing server directories that ORACLE can access. 3. To accept a directory within [brackets] hit the "Enter" key. Enter UDUMP directory (where traces are created). Hit "Enter" key if you agree with recommended value below: <A default location> UDUMP directory:

3) UDUMP directory is where the trace files are created. You may wish to accept the recommended value or specify your own value.

4) Once this is done, you will prompted for BDUMP directory. [ Enter BDUMP directory (where PX traces are created) ]. Again, you can choose to accept the provided value or input your specific location for this.

5) After this is done, you will be prompted to enter STAGE directory.[Enter STAGE directory used as SQLT workarea (defaults to UDUMP).].I will leave the default location for this provided by Oracle during installation.

6) You will be prompted for optional connect identifier after this step. I learned from the help file that it needs to be used in some access restricted systems.This is NA for my installation, and I would prefer to skip this step.

Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key

7) We mentioned that this tool uses its own schema. You will be prompted to enter the password for this schema in the next step. Enter the password that you wish to.[ you will have to enter the password twice for confirmation ]

Define SQLTXPLAIN password (hidden and case sensitive).
Password for user SQLTXPLAIN:

8)You have to specify the SQLTXPLAIN Default Tablespace in the next step. Make sure that you have sufficient space in this tablespace.

Tablespace name is case sensitive.
Default tablespace [UNKNOWN]:

9) Specify the SQLTXPLAIN Temporary Tablespace in the next step. You will be provided with a list of temporary tablespaces to choose from at this step.

TEMPORARY_TABLESPACE
------------------------------
TEMP
Tablespace name is case sensitive.
Temporary tablespace [UNKNOWN]:

10) You have to mention the main application user in the next step. This depends on your application. I will enter HR here. If you are trying to install this for a PeopleSoft application, you should enter the PSFT application schema name. (SYSADM, PSFTADM etc).

The main application user of SQLT is the schema owner that issued the SQL to be analyzed.For example, on an EBS application you would enter APPS as the main application user.You will not be asked to enter its password.To add more SQLT users after this installation is complete simply grant them the SQLT_USER_ROLE role, or execute sqlt/install/sqguser.sql.

Main application user of SQLT:

11) As a last step, you can enter any additional license pack to which you have access to.

SQLT can make extensive use of licensed features provided by the Oracle Diagnostic and the Oracle Tuning Packs, including SQL Tuning Advisor (STA),SQL Monitoring and Automatic Workload Repository(AWR).To enable or disable access to these featuresfrom the SQLT tool enter one of the following values when asked:

"T" if you have license for Diagnostic and Tuning
"D" if you have license only for Oracle Diagnostic
"N" if you do not have these two licenses
Oracle Pack license [T]:

I will enter N at this stage. Once you provide all these inputs, the installation will begin. All required objects will be created at this stage. Some of the key steps performed at this stage include;

1) Installing required packages to support SQLT.

2) Create SQLT schema objects.

3) Migrating relevant objects from old to new repository.

4) Taking snapshots of some existing data dictionary objects

That is all you have to do to set up this performance tuning utility. We will discuss how to use this Oracle performance optimization tool in the upcoming posts.