Oracle Database SQLTXPLAIN (SQLT) Installation and report generation

 

There are  lot of documents online sqlt however  i have documented below for my quick reference and if it helps someone 

Sqlt Needs  installation which  becomes challenge sometimes , in that case lighter version sqlhc can be used  

Installation : 

Install SQLT

Install SQL by executing the installation script sqlt/install/sqcreate.sql connected as SYS:

# cd sqlt/install

# sqlplus / as sysdba

SQL> START sqcreate.sql

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

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.

SQLTXPLAIN password - Case sensitive in most systems.

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.

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

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

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.

Report Generation : 

Note  :  after 19c migration  sqltadmin object becomes invalid and  sqlt has to be reinstalled as per doc id 2920902.1 

== EXTRACT SQLT reports:

Starting with 12cR1, SQLT can be executed under SYS user -- for so, do as follows before starting execution:

Fix for :  ORA-06598   - inherit any  and  sqlt role 

SQL> CONN /as sysdba

SQL> GRANT INHERIT PRIVILEGES ON USER sys TO sqltxadmin;

1: To speedup SQLT Extraction -- connect as SQLTXADMIN user and run:

EXEC sqltxplain.sqlt$a.set_param('sta_time_limit_secs', '30');

EXEC sqltxplain.sqlt$a.set_sess_param('sql_tuning_advisor','N');

EXEC sqltxadmin.sqlt$a.set_param('ash_reports', '0');

EXEC sqltxadmin.sqlt$a.set_param('awr_reports', '0');

-- sqltxtract.sql script gather the details from memory or else from AWR snapshots.

cd sqlt/run

sqlplus ep/ep; -- connect as application user

@sqltxtract.sql sqlid sqltpassword

@/home/oracle/sqlt/run/sqltxtract.sql jkz7v96ym42c6 SQLTXPLAIN

 

Reference : 

How to Collect Standard Diagnostic Information Using SQLT for SQL Issues (Doc ID 1683772.1)

Document 215187.1 SQLT Diagnostic Tool