Slow SQL Query troubleshoot steps

Use below step to troubleshoot slow sql query

Tablespace free space 
==============================

set lines 200
set pages 5000
column "Tablespace" format a50
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
column "Total MB"   format 99,999,999
select
  fs.tablespace_name                          "Tablespace",
  (df.totalspace - fs.freespace)              "Used MB",
  fs.freespace                                "Free MB",
  df.totalspace                               "Total MB",
  round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
  (select
     tablespace_name,
     round(sum(bytes) / 1048576) TotalSpace
  from
     dba_data_files
  group by
     tablespace_name
  ) df,
  (select
     tablespace_name,
     round(sum(bytes) / 1048576) FreeSpace
  from
     dba_free_space
  group by
     tablespace_name
  ) fs
where
  df.tablespace_name = fs.tablespace_name order by 5;


Temp tablespace usages report 
======================================

SELECT 
   A.tablespace_name tablespace, 
   D.gb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024/1024 gb_used,
   D.gb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024/1024 gb_free
FROM 
   v$sort_segment A,
(
SELECT 
   B.name, 
   C.block_size, 
   SUM (C.bytes) / 1024 / 1024 /1024 gb_total
FROM 
   v$tablespace B, 
   v$tempfile C
WHERE 
   B.ts#= C.ts#
GROUP BY 
   B.name, 
   C.block_size
) D
WHERE 
   A.tablespace_name = D.name
GROUP by 
   A.tablespace_name, 
   D.gb_total
/

it will give you queries currently running for more than 60 seconds
 =============================================================================

select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s 
join v$sqltext_with_newlines q
on s.sql_address = q.address
 where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece

How to Identify SID Based on OS PID in Oracle
===================================================

col sid format 99999
col username format a20
col osuser format a15
select p.spid,s.sid, s.serial#,s.username, s.osuser
from gv$session s, gv$process p
where s.paddr= p.addr
and p.spid='&spid'
order by p.spid;

query completion percent :
===================================
COLUMN percent FORMAT 999.99 

SELECT sid, to_char(start_time,'hh24:mi:ss') stime, 
message,( sofar/totalwork)* 100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1
/


Query1 : using :  SYS_CONTEXT('USERENV','IP_ADDRESS')
=============================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE, 
SYS_CONTEXT('USERENV','IP_ADDRESS') IP, 
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;


Query2: using  UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP
=========================================================================================

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE, 
UTL_INADDR.GET_HOST_ADDRESS (SUBSTR(machine,INSTR(machine,'')+1)) IP, 
v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
ORDER BY logon_time ASC;


Check for any stale statistics.
=========================================

set lines 200 pages 500
col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';



Check any invalid index/Partition
=======================================

col TABLE_NAME for a30
select owner,index_name,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STATUS from dba_indexes where status not in ('VALID','N/A');

select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,LAST_ANALYZED,STATUS from dba_ind_partitions where status <> 'USABLE';


Check free memory shared pool area. Check too much hard parsing.
======================================================================

select * from (select SQL_ID,PARSING_SCHEMA_NAME, count(1) from v$sql group by SQL_ID,PARSING_SCHEMA_NAME order by  3 desc,2) where rownum<=10;

 Wait/ Blocking analysis.
 ============================


SELECT sid, serial#, blocking_session_status, blocking_session
FROM   v$session
WHERE  blocking_session IS NOT NULL;

-- Display the resource or event the session is waiting for more than 1 minutes

SELECT sid, serial#, event, (seconds_in_wait/1000000) seconds_in_wait
FROM   v$session
where (seconds_in_wait/1000000) > 60
ORDER BY sid;

select sid,seq#,event,state,SECONDS_IN_WAIT from v$session_wait where SECONDS_IN_WAIT > 60;

--Monitor Top Waiting Event Using Active Session History (ASH)

SELECT h.event,
SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC;


Tuning SQL using 
========================

@?/rdbms/admin/sqltrpt


AWR various Report.
=========================

@?/rdbms/admin/awrsqrpt.sql --> awr report for only single sql_id
@?/rdbms/admin/awrrpt.sql   --> Traditional awr report for instance.

RAC Related awr Report

@?/rdbms/admin/awrgrpt.sql -- AWR Global Report (RAC) (global report)
@?/rdbms/admin/awrrpti.sql -- Workload Repository Report Instance (RAC)


Long running jobs 
===========================


 SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;



Check the sessions by username
================================================== 

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;


Check the sessions by SID
==================================================
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;


SQL response time 
=========================

select to_char(begin_time,'hh24:mi') time, round( value * 10, 2) "Response Time (ms)"
     from v$sysmetric
     where metric_name='SQL Service Response Time'
 
Completion Time remianing 
==============================

SELECT s.inst_id,
       s.sid,
       s.serial#,
       sl.qcsid,
       s.username,
       s.module,
       sl.opname,
       sl.time_remaining/60 time_remaining
FROM   gv$session s,
       gv$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.inst_id = sl.inst_id
AND    s.serial# = sl.serial#
and sl.totalwork<>sl.sofar;


Blocking info
======================

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 active sql running in server 
========================================= 
   
   
 set echo off  linesize 200  pages 1000  head on  feedback on
 col username format a30
 col start_time format a15
 col curr_time format a15
 col osuser format a10
 col opname format a35
 col target format a25
 col tremain format 999999.99
 col elamin format 999999.99
 select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,to_char(a.start_time,'dd-mon:hh24:mi:ss') start_time,
 to_char(sysdate,'dd-mon:hh24:mi:ss') curr_time,a.opname,a.target,a.totalwork,a.sofar,(a.elapsed_Seconds)/60 elamin,
 a.time_remaining/60 tremain
 from v$session_longops a,
 v$session b
 where a.totalwork<>a.sofar
 and a.sid=b.sid
 order by 3
 /




select *
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null
   and sesion.sid='&sid'