Tag Archives: SQLT

Troubleshooting Long Running Queries

Troubleshooting Flow For Long Running Queries

Contents
___________________________________________________________________________________________________

1. Flow Chart
2. Logon to Database
3. Find Long Running Sessions
4. Blocking sessions
5. Kill Sessions
6. SQL History
7. Find Force Matching Signature
8. SQL Tuning Adviser
9. SQLT
10. SQL Health Check
11. SQL Plan Flip
12. Find Stale Stats
13. Gather Stats
14. PIN Optimal Plan
15. Find Fragmentation
16. De-Fragmentation
_________________________________________________________________________________________________


1. Flow Chart

*** Please click on each box in flow chart below to take you to respective script section

2. Logon to Database

select name,open_mode from v$database;


3. Find Long Running Sessions

/*************************************************************************
Check the ALL Active/Inactive session
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER'
 order by logon_time;

/*************************************************************************
Check the all Active session
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER' and b.status='ACTIVE'
 order by logon_time;


/*************************************************************************
Check the ALL Active/Inactive sessions by SID
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER' and b.SID='&SID'
-- and b.status='ACTIVE'
-- and b.status='INACTIVE'
 order by logon_time;

/*************************************************************************
Check the ALL Active/Inactive sessions by Username
**************************************************************************/

set linesize 750 pages 9999
column box format a30
column spid format a10
column username format a30 
column program format a30
column os_user format a20
col LOGON_TIME for a20  

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
 substr(b.username,1,30) username,
 substr(b.osuser,1,20) os_user,
 substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id 
 from gv$session b,gv$process a 
 where b.paddr = a.addr 
 and a.inst_id = b.inst_id  
 and type='USER' and b.username='&username'
-- and b.status='ACTIVE'
-- and b.status='INACTIVE'
 order by logon_time;


/*************************************************************************
SQL Monitor
**************************************************************************/
set lines 1000 pages 9999 
column sid format 9999 
column serial for 999999
column status format a15
column username format a10 
column sql_text format a80
column module format a30
col program for a30
col SQL_EXEC_START for a20

SELECT * FROM
       (SELECT status,inst_id,sid,SESSION_SERIAL# as Serial,username,sql_id,SQL_PLAN_HASH_VALUE,
     MODULE,program,
         TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start,
         ROUND(elapsed_time/1000000)                      AS "Elapsed (s)",
         ROUND(cpu_time    /1000000)                      AS "CPU (s)",
         substr(sql_text,1,30) sql_text
       FROM gv$sql_monitor where status='EXECUTING' and module not like '%emagent%' 
       ORDER BY sql_exec_start  desc
       );

/*************************************************************************
---- Sql-Monitor report for a sql_id         ( Like OEM report)
**************************************************************************/
column text_line format a254
set lines 750 pages 9999
set long 20000 longchunksize 20000
select 
 dbms_sqltune.report_sql_monitor_list() text_line 
from dual;

select 
 dbms_sqltune.report_sql_monitor() text_line 
from dual;


4. Blocking sessions

**** To find Blocking GOOD query 

set lines 750 pages 9999
col blocking_status for a100 
 select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine
 || ' ( SID=' || s1.sid || ' )  is blocking '
 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = l2.id2
order by s1.inst_id;

**** Check who is blocking who in RAC, including objects

SELECT DECODE(request,0,'Holder: ','Waiter: ') || gv$lock.sid sess, machine, do.object_name as locked_object,id1, id2, lmode, request, gv$lock.type
FROM gv$lock join gv$session on gv$lock.sid=gv$session.sid and gv$lock.inst_id=gv$session.inst_id
join gv$locked_object lo on gv$lock.SID = lo.SESSION_ID and gv$lock.inst_id=lo.inst_id
join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID 
WHERE (id1, id2, gv$lock.type) IN (
  SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request;


5. Kill Sessions

select 'alter system kill session ' || '''' || sid || ',' || serial# ||',@'|| inst_id || '''' || ' immediate;' from gv$session where sid='&sid';


6. SQL History

set lines 1000 pages 9999
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,
       TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM dba_hist_sqlstat h, 
       dba_hist_snapshot s
 WHERE h.sql_id = '&sql_id'
   AND h.executions_total > 0 
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
UNION ALL  
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id, 
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions,
       TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM gv$sqlarea_plan_hash h 
 WHERE h.sql_id = '&sql_id'
   AND h.executions > 0 
order by source ;


7. Find Force Matching Signature

col exact_matching_signature for 99999999999999999999999999
col sql_text for a50
set long 20000
set  lines 750 pages 9999
select sql_id, exact_matching_signature, force_matching_signature, SQL_TEXT from v$sqlarea where sql_id='&sql_id';
UNION ALL
select sql_id,force_matching_signature,SQL_TEXT from dba_hist_sqltext where sql_id='&sql_id'
/

-- If you want to find Bind variable for SQL_ID

col VALUE_STRING for a50  
SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM gv$sql_bind_capture WHERE sql_id='&sql_id';


8. SQL Tuning Advisor


9. SQLT


10. SQL Health Check

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)


11. SQL Plan Flip

set lines 1000 pages 9999
COL instance_number FOR 9999 HEA 'Inst';
COL end_time HEA 'End Time';
COL plan_hash_value HEA 'Plan|Hash Value';
COL executions_total FOR 999,999 HEA 'Execs|Total';
COL rows_per_exec HEA 'Rows Per Exec';
COL et_secs_per_exec HEA 'Elap Secs|Per Exec';
COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';
COL io_secs_per_exec HEA 'IO Secs|Per Exec';
COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';
COL ap_secs_per_exec HEA 'App Secs|Per Exec';
COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';
COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';
COL ja_secs_per_exec HEA 'Java Secs|Per Exec';
SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,
       TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions_total,
       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM dba_hist_sqlstat h, 
       dba_hist_snapshot s
 WHERE h.sql_id = '&sql_id'
   AND h.executions_total > 0 
   AND s.snap_id = h.snap_id
   AND s.dbid = h.dbid
   AND s.instance_number = h.instance_number
UNION ALL  
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id, 
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
       h.sql_id,
       h.plan_hash_value, 
       h.executions,
       TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
       TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,
       TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,
       TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,
       TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,
       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,
       TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,
       TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec
  FROM gv$sqlarea_plan_hash h 
 WHERE h.sql_id = '&sql_id'
   AND h.executions > 0 
order by source ;


12. Find Stale Stats

http://www.br8dba.com/statistics/

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='&owner;

*** statistics of objects of a specific sql id 

set lines 300 set pages 300
col table_name for a40
col owner for a30 
select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked
  from dba_tab_statistics
  where (owner, table_name) in
  (select distinct owner, table_name
          from dba_tables
          where ( table_name)
          in ( select object_name
                  from gv$sql_plan
                  where upper(sql_id) = upper('&sql_id') and object_name is not null))
  --and STALE_STATS='YES'
/


13. Gather Stats


14. PIN Optimal Plan

-- Run below script, you can download this script from SQLT
-- Reference: http://www.br8dba.com/how-to-create-custom-sql-profile/
@coe_xfr_sql_monitor.sql <sql_id> <plan_hash_value> -- to create SQL profile
The coe_xfr_sql_profile.sql script would create another sql file, which should be run to create manual sql profile for the sql 
The new sql file created.


ls -ltr coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql
cat coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql

@coe_xfr_sql_profile_<sql_id>_<plan_hash_value>.sql


15. Find Fragmentation

*** Table Fragmentation

select 
   table_name,round((blocks*8),2) "size (kb)" , 
   round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
   (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from 
   dba_tables
where  owner='&OWNER' and table_name='&TABLE_NAME' and 
   (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;


16. De-Fragmentation

*** There are many methods.

Option 1: Shrink command

alter table  enable row movement;
/*
Using the enable row movement clause can reduce the clustering_factor for a primary access index, causing excessive I/O.  Oracle introduced the sorted gash cluster as a way to keep an index in-order with the table rows, a technique that greatly reduces I/O for common queries. 
Beware that using "enable row movement" can cause Oracle to move rows to discontinuous data blocks, causing a performance problem.  Remember, the physical placement of rows on data blocks can still make a huge difference in query performance. 
*/
alter table  shrink space compact;
alter table  shrink space cascade;

http://www.dba-oracle.com/t_enable_row_movement.htm

Option 2: Table move

Alter table move - The alter table xxx move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same.  The alter table move syntax also preserves the index and constraint definitions.

ALTER TABLE <table_name>  MOVE;

Option 3: EXPORT / IMPORT

** EXPORT
** DROP ALL RESPECTIVE OBJECTS
** IMPORT FROM EXPORT BACKUP


Option 4: EXPORT / IMPORT WITH TABLE_EXISTS_ACTION=REPLACE

** EXPORT
** IMPORT USING TABLE_EXISTS_ACTION=REPLACE

Option 5: Other methods

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Sugivaradhan & Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp No: +65-94693551

How to Run SQLT

How to Run SQLT

Step 1: Grant SQLT_USER_ROLE to appication user
Step 2: Find the SQL ID and PLAN_HASH_VALUE for problem query
Step 3: Run sqltxtract.sql
Step 4: Find the ZIP file created by above sql
Step 5: Review the mail.html


Step 1: Grant SQLT_USER_ROLE to appication user

SQL> grant SQLT_USER_ROLE to <application_user>;


Step 2: Find the SQL ID and PLAN_HASH_VALUE for problem query

sqlplus / as sysdba
set echo on
set linesize 200 pagesize 1000
col sql_text format a50
select sid, serial#,status,sql_id,event from v$session where username='&username';
select SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PROFILE FROM V$SQL where sql_id='&SQL_ID';
select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%from test t1, test t2%';
select * from table(dbms_xplan.display_cursor('dkz7v96ym42c6',null,'TYPICAL'));
select * from table(dbms_xplan.display_cursor('dkz7v96ym42c6',null,'ADVANCED ALLSTATS LAST'));


Step 3: Run sqltxtract.sql

-- 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 dkz7v96ym42c6 SQLTXPLAIN


Step 4: Find the ZIP file created by above sql

cd /home/oracle/sqlt/run
ls -ltr *sqlid*

eg: 
ls -ltr *dkz7v96ym42c6*
-rw-r--r-- 1 oracle oinstall 1842900 Oct 17 22:49 sqlt_s41093_xtract_dkz7v96ym42c6.zip

Please copy the zip file to your desktop for review.


Step 5: Review the mail.html

This mail.html file would have all the previous and current execution plans used by the sql along with timestamp when they were used.

-- click on Execution plans hyperlink under plans

Please check the execution plan currently used by the sql, by comparing the timestamp. Also identify the best execution plan for the sql.

The plan hash value for the best execution plan would have suffix [B]
Then plan hash value for the worst exection plan would have a suffix[W]

In case if you find the BEST EXECUTION PLAN, go ahead and create the custom sql profile. 

If you want to know how to create CUSTOM SQL PROFILE, please click below
How to create Custome SQL Profile

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

How to Install SQLT

How to Install SQLT

Step 0: Find SQLT Already Instlled or Not
Step 1: Download SQLT
Step 2: How to Install SQLT
Step 3: Verify


Step 0: Find SQLT Already Instlled or Not

SELECT
'SQLT version number: '||sqltxplain.sqlt$a.get_param('tool_version')||CHR(10)||
'SQLT version date : '||sqltxplain.sqlt$a.get_param('tool_date')||CHR(10)||
'Installation date : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
FROM DUAL;

output

SQL> SELECT
'SQLT version number: '||sqltxplain.sqlt$a.get_param('tool_version')||CHR(10)||
'SQLT version date : '||sqltxplain.sqlt$a.get_param('tool_date')||CHR(10)||
'Installation date : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
FROM DUAL;  2    3    4    5
'Installation date : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
                        *
ERROR at line 4:
ORA-00904: "SQLTXPLAIN"."SQLT$A"."GET_PARAM": invalid identifier <-- Not installed

SQL>


Step 1: Download SQLT

All About the SQLT Diagnostic Tool (Doc ID 215187.1)


Step 2: How to Install SQLT

unzip sqlt_latest.zip
cd /home/oracle/sqlt/install
sqlplus / as sysdba
@sqcreate.sql

output

[oracle@rac1 ~]$ cd /home/oracle/sqlt/install
[oracle@rac1 install]$ ls -ltr sqcreate.sql
-rw-r--r-- 1 oracle oinstall 4771 Oct 30  2014 sqcreate.sql
[oracle@rac1 install]$

[oracle@rac1 install]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 17 12:18:23 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @sqcreate.sql

zip error: Nothing to do! (SQLT_installation_logs_archive.zip)

zip error: Nothing to do! (SQLT_installation_logs_archive.zip)

PL/SQL procedure successfully completed.


Specify optional Connect Identifier (as per Oracle Net)
Include "@" symbol, ie. @PROD
If not applicable, enter nothing and hit the "Enter" key.
You *MUST* provide a connect identifier when installing
SQLT in a Pluggable Database in 12c
This connect identifier is only used while exporting SQLT
repository everytime you execute one of the main methods.

Optional Connect Identifier (ie: @PROD): <-- Just hit enter


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Define SQLTXPLAIN password (hidden and case sensitive).

Password for user SQLTXPLAIN: <--- Enter the password
Re-enter password:


PL/SQL procedure successfully completed.

... please wait

TABLESPACE                     FREE_SPACE_MB
------------------------------ -------------
EXAMPLE                                  216
USERS                                  32735


Specify PERMANENT tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Default tablespace [UNKNOWN]: USERS <---

PL/SQL procedure successfully completed.

... please wait

TABLESPACE
------------------------------
TEMP


Specify TEMPORARY tablespace to be used by SQLTXPLAIN.

Tablespace name is case sensitive.

Temporary tablespace [UNKNOWN]: TEMP <---

PL/SQL procedure successfully completed.


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.
You will not be asked to enter its password.
To add more SQLT users after this installation
is completed simply grant them the SQLT_USER_ROLE
role.

Main application user of SQLT: scott  <-----

PL/SQL procedure successfully completed.


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 features
from 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]: <----

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

TADOBJ completed.

PL/SQL procedure successfully completed.
..
..
SQLT users must be granted SQLT_USER_ROLE before using this tool. <----

SQCREATE completed. Installation completed successfully.
SQL>


Step 3: Verify

SELECT
'SQLT version number: '||sqltxplain.sqlt$a.get_param('tool_version')||CHR(10)||
'SQLT version date : '||sqltxplain.sqlt$a.get_param('tool_date')||CHR(10)||
'Installation date : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
FROM DUAL;

output

SQL> SELECT
'SQLT version number: '||sqltxplain.sqlt$a.get_param('tool_version')||CHR(10)||
'SQLT version date : '||sqltxplain.sqlt$a.get_param('tool_date')||CHR(10)||
'Installation date : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
FROM DUAL;  2    3    4    5

SQLT_VERSION
-----------------------------------------
SQLT version number: 12.1.14
SQLT version date : 2015-12-06
Installation date : 2016-10-17/12:20:51


SQL>

Reference:

FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions (Doc ID 1454160.1)
All About the SQLT Diagnostic Tool (Doc ID 215187.1)
SQLT Usage Instructions (Doc ID 1614107.1)

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala