How to find which sid is doing full table scan

Last updated on December 21st, 2015 at 05:42 am

Many times the performance of the database will be slow.We need to first find out  if any big table full table scan is going on.

We can use below query to find out what all the sid are doing full tables scan

column user_process heading “Name |SID” format a20;

column long_scans heading “Long Scans” format 999,999,999;
column short_scans heading “Short Scans” format 999,999,999;
column rows_retreived heading “Rows Retrieved” format 999,999,999;
set linesize 1000
set timing on
select ss.username||'(‘||se.sid||’) ‘ “USER_PROCESS”,
sum(decode(name,’table scans (short tables)’,value)) “SHORT_SCANS”,
sum(decode(name,’table scans (long tables)’, value)) “LONG_SCANS”,
sum(decode(name,’table scan rows gotten’,value)) “ROWS_RETRIEVED”
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and (name like ‘%table scans (short tables)%’
or name like ‘%table scans (long tables)%’
or name like ‘%table scan rows gotten%’ )
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'(‘||se.sid||’) ‘
order by LONG_SCANS desc

Leave a Reply