doc

    Name:   fullscan.sql

    Author: Mark Gurry


The following scripts provide information on the full table scan activity.
If your application is OLTP only, having long full table scans can be an
indicator of having missing or incorrect indexes or untuned SQL. 

#

drop view Full_Table_Scans;

create view Full_Table_Scans as
 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 Retreived"
   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||') ';

column  "User Process"     format a20;  
column  "Long Scans"       format 999,999,999;  
column  "Short Scans"      format 999,999,999;   
column  "Rows Retreived"   format 999,999,999;   
column  "Average Long Scan Length" format 999,999,999;   

ttitle ' Table Access Activity By User '

select "User Process", "Long Scans", "Short Scans", "Rows Retreived"
  from Full_Table_Scans 
 order by "Long Scans" desc;