Unlocking Advanced Oracle Diagnostics: A Guide to Installing SQLT

   SQLT aids in diagnosing SQL statement performance problems by collecting and analyzing detailed trace data. 

SCOPE

Oracle Database administrators often encounter challenging performance issues that require deep diagnostics and analysis. One essential tool in the arsenal of Oracle performance tuning is SQLTXPLAIN (SQLT), a powerful diagnostic tool developed by Carlos Sierra. 

In this blog post, I will guide you through the installation process of SQLT step-by-step. Whether you're troubleshooting a specific SQL performance problem or looking to enhance your database diagnostic capabilities, installing SQLT will empower you to gain deeper insights into SQL execution within your Oracle environment.

Let's dive into the installation process and unleash the potential of SQLT for effective Oracle performance tuning.

SQLT Installation

1. Download the sqlt zip file from MOS: 215187.1
2. Copy to the server using scp/winscp
3. Unzip the sqlt zip file to the $ORACLE_HOME/bin directory

$ cp /home/oracle/sqlt_latest_24.zip /u01/app/oracle/product/18.1.0.2/db_4/bin/
$ unzip sqlt_latest_24.zip
Archive:  sqlt_latest_24.zip

4. Upon completion of the unzip process, a directory named "sqlt" will be generated. Navigate to this directory to proceed with the installation. 

$ cd sqlt/
$ ls -lhrt
total 116K
drwxr-xr-x 3 oracle oinstall 4.0K Nov 13 12:21 input
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 install
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 run
drwxr-xr-x 7 oracle oinstall 4.0K Nov 20 16:57 utl
drwxr-xr-x 2 oracle oinstall 4.0K Nov 20 16:57 doc
-rw-r--r-- 1 oracle oinstall  56K Dec  6 19:26 sqlt_instructions.html
-rw-r--r-- 1 oracle oinstall  38K Dec  6 19:27 sqlt_instructions.txt

5.Connect to the database as SYSDBA and execute the sqcreate.sql script to initialize SQLT within the database environment. 

$ sqlplus /nolog
SQL> connect / as sysdba
Connected.
SQL> start install/sqcreate.sql

6. During the execution of sqcreate.sql, you will be prompted to provide specific values for certain options. Please enter the appropriate values as requested during this interactive process.

Optional Connect Identifier (ie: @PROD): just type enter don't give any value in case of local server

Password for user SQLTXPLAIN: Provide the new password for the SQLTXPLAIN user that must satisfy the password verify function 

                              otherwise the installation will fail

Re-enter password: Proivde the same password as above

Default tablespace [UNKNOWN]:   USERS  Provide the tablespace on which you want to install the SQLTXPLAIN user
Temporary tablespace [UNKNOWN]: TEMP Provide the temp tablespace
Main application user of SQLT: SYS
Oracle Pack license [T]: T

Upon successful execution of sqcreate.sql, the installation process will create two database users as follows:

SQLTXADMIN: This user is responsible for managing SQLT operations and diagnostics.
SQLTXPLAIN: This user is utilized for executing SQLT operations and collecting diagnostic data.

Additionally, the necessary database privileges and grants will be automatically assigned to these users as part of the installation process to ensure proper functionality of SQLT within your Oracle environment.

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where trunc(CREATED)=trunc(sysdate);

USERNAME                                   ACCOUNT_STATUS
------------------------------------------ --------------------------------
SQLTXADMIN                                 EXPIRED & LOCKED
SQLTXPLAIN                                 OPEN

SQL> alter user SQLTXADMIN identified by SQLTXADMIN account unlock;

User altered.

SQL> grant create connect,resource,SQLT_USER_ROLE to SQLTXPLAIN,SQLTXADMIN;

Grant succeeded.

SQL> GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;

Grant succeeded.

Note : Please ensure that all SQLT users are granted the SQLT_USER_ROLE to enable access to SQLT functionalities. In cases where using SYS as the SQLT user is not feasible, grant the INHERIT PRIVILEGES privilege to the SQLTXADMIN user on the SYS user. This step is crucial for ensuring proper authorization and functionality within the SQLT framework.

SQLT REPORT GENERATION

1. Identify the sql_id of the SQL statement for which you wish to generate a SQLT report.

2. Connect to SQLTXPLAIN user 

3. Navigate to the SQLT directory

4. Execute the sqltxtract.sql script to generate the SQLT report. This script will prompt you to enter the sql_id and the password for the SQLTXPLAIN user when required.

5. Provide the necessary sql_id and password as prompted. Once the script completes execution, the SQLT report will be generated for the specified SQL statement.

SQL> conn SQLTXPLAIN/SQLTXPLAIN
Connected.

SQL> ! pwd
/u01/app/oracle/product/18.1.0.2/db_4/bin/sqlt

SQL> start run/sqltxtract.sql

............
............

SQLTXTRACT completed.

SQL>! ls -lhrt|tail

-rw-r--r-- 1 oracle oinstall   Jun 02 13:54 sqlt_s62619_xtract_8aethawt674q1b.zip

Happy Learning :)

Best Regards,

Nikhil