Segment Scans

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		11.2.0.1
rem		11.1.0.6
rem	Not relevant
rem		 9.2.0.6
rem		 8.1.7.4
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.