SQLT Installation and Report Generation

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.zip /u01/app/oracle/product/12.1.0.2/db_1/bin/
$ unzip sqlt_latest.zip
Archive:  sqlt_latest.zip

4. After unzip completed it will creates the sqlt directory. Go to the sqlt directory
$ 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 run the sqcreate.sql
$ sqlplus /nolog
SQL> connect / as sysdba
Connected.
SQL> start install/sqcreate.sql
It will prompt for the below options just give the respective values

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

Password for user SQLTXPLAIN: Proivde the new password for the SQLTXPLAIN user that must satisfy the password verify function otherwise you installation will fail
Re-enter password: Proivde the same above password

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

Once everything is completed then it will creates the below 2 users. And provide the necessary grants.

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.
Note: All SQLT users must be granted the SQLT_USER_ROLE before they can use any of the main methods. If It's not possible to use SYS as SQLT user, SQLTXADMIN needs to be granded INHERIT PRIVILEGES on SYS.

SQL> GRANT INHERIT PRIVILEGES ON USER SYS TO SQLTXADMIN;

Grant succeeded.

SQLT report Generation for the specific sql_id:
***********************************************
1. Identify the which sql_id you want to generate the sqlt report
2.
SQL> ho pwd
/u01/app/oracle/product/12.1.0.2/db_1/bin/sqlt
SQL> conn SQLTXPLAIN/SQLTXPLAIN
Connected.
SQL> start run/sqltxtract.sql

The script will prompt for the sql_id and SQLTXPLAIN password please provide the same.
............
............
SQLTXTRACT completed.
SQL>exit
The script will generate the zip with the sql_id.
$ ls -lhrt|tail
-rw-r--r-- 1 oracle oinstall  29M Apr 22 03:59 sqlt_s62619_xtract_5xagduq334p7t.zip

Happy Learning :)

How to tune a SQL statement

Please follow the below steps for tuning a SQL statement.

Take the sql_id before going to start.

1. Create Tuning Task:

SQL> set serveroutput on
declare
sql_tune_task_id  varchar2(100);
begin
sql_tune_task_id := dbms_sqltune.create_tuning_task (
                          sql_id      => '03h94xbcu25c3',            //give your sql_id
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'sql_tune_03h',
                          description => 'tuning task for statement 03h94xbcu25c3');
  dbms_output.put_line('sql_tune_task_id: ' || sql_tune_task_id);
end;
/

2. Execute Tuning Task:
SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'sql_tune_03h');

3. TO GET SUMMARY INFORMATION:

SQL> set long 100000
set longchunksize 1000
set pagesize 10000
set linesize 100
select dbms_sqltune.report_tuning_task('sql_tune_03h') as recommendations from dual;

The above query result gives the recommendations if you want to follow you can. After completion of your work drop the tuning task which you created earlier.

4. Drop the tuning task:

SQL> begin
DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tune_03h');
end;
 /

To know the status of the tuning task
SQL> SELECT task_name, status FROM dba_advisor_log WHERE owner = '<OWNER_NAME>';

You can tune the sql statement by using the below script provided by the Oracle
script location: ORACLE_HOME/rdbms/admin/sqltrpt.sql
It will prompt for the sql_id, provide the sql_id.

Happy Learning :)