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'
