If you think you want to find out which objects have been scanned (tablescan full or index fast full scan) then it’s nice to know that Oracle does collect these segment level statistics from 10g onwards.
Unfortunately, although the AWR reports some of the segment stats, the “segment scans” is not one of them.
[Update Aug 2011: although the 10g AWR report doesn’t include Segment Scans, the 11g version does. It should be noted that this includes both tablescans and index fast full scans, but only accumulates data for segment that qualify as “long” segments which, in effect, means larger than 2% of the buffer size]
This is a simple script that checks number of scans since database startup.
rem rem Script: seg_scan.sql rem Author: Jonathan Lewis rem Dated: Dec 2005 rem Purpose: rem rem Last tested rem 10.2.0.1 rem Not tested rem 126.96.36.199 rem 188.8.131.52 rem Not relevant rem 184.108.40.206 rem 220.127.116.11 rem rem Notes: rem Identifying objects subject to tablescans rem or index fast full scans. rem rem v$segstat is the more efficient, but less rem informative - you have to join interesting rem looking objects to dba_objects etc. rem rem v$segment_statistics is more informative, rem but more expensive to run. rem rem Hard-coded for statistic# 17, but this could rem vary with version - so always check against rem v$segstat_name (or check the text column) rem rem Other stats were available in 9i, the segment rem scan only appeared in 10 (possibly 10.2) rem set linesize 156 set trimspool on set pagesize 60 spool seg_scan select statistic#, name from v$segstat_name ; select obj#, dataobj#, value from v$segstat where statistic# = 17 and value != 0 order by value ; break on owner skip 1 select owner, object_type, object_name, subobject_name, tablespace_name, value scans from V$segment_statistics where statistic_name = 'segment scans' and value != 0 order by owner, value ; spool off
Running it (or one of the queries) twice with a gap of a couple of minutes might be enough to identify interesting objects. Obviously you could choose a more restrictive predicate than “value != 0″ to minise the length of the output.
I tend to look for objects by number from V$segstat, then check things like dba_objects and then dba_tables, dba_indexes or dba_segments for sizing information, and v$object_dependency for related SQL if I find anythiong interesting. If you have permanent privileged access you might choose to write a more complex script that puts one of these queries (preferably the v$segstat one) into a materialized subquery and then joins to other objects to produce an all-in-one report.