How to use SQLT tool in Oracle 11g

How to install:

1.  # cd sqlt/install

2.  # sqlplus / as sysdba

3.  SQL> START sqcreate.sql

During the installation you will be asked to enter values for these parameters:

1. Optional Connect Identifier.

In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key.

2. SQLTXPLAIN password.

Case sensitive in most systems.

3. SQLTXPLAIN Default Tablespace.

Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.

4. SQLTXPLAIN Temporary Tablespace.

Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.

5. Optional Application User.

This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won't be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE or by using provided script sqlt/install/sqguser.sql

6. Licensed Oracle Pack. (T, D or N)

You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.

XTRACT Method

Use this method if you know the SQL_ID or the HASH_VALUE of the SQL to be analyzed, else use XECUTE. The SQL_ID can be found on an AWR report, and the HASH_VALUE on any SQL Trace.

# cd sqlt/run

# sqlplus apps

SQL> START sqltxtract.sql [SQL_ID]|[HASH_VALUE]

SQL> START sqltxtract.sql 0w6uydn50g8cx

SQL> START sqltxtract.sql 2524255098

XTRXEC Method

# cd sqlt/run

# sqlplus apps

SQL> START sqltxtrxec.sql [SQL_ID]|[HASH_VALUE]

SQL> START sqltxtrxec.sql 0w6uydn50g8cx

SQL> START sqltxtrxec.sql 2524255098

XPLAIN Method

# cd sqlt

# sqlplus apps

SQL> START [path]sqltxplain.sql [path]filename

SQL> START run/sqltxplain.sql input/sample/sql1.sql