Oracle Scratchpad

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	Script:		seg_scan.sql
rem	Author:		Jonathan Lewis
rem	Dated:		Dec 2005
rem	Purpose:
rem	Last tested
rem	Not tested
rem	Not relevant
rem	Notes:
rem	Identifying objects subject to tablescans
rem	or index fast full scans.
rem	v$segstat is the more efficient, but less
rem	informative - you have to join interesting
rem	looking objects to dba_objects etc.
rem	v$segment_statistics is more informative,
rem	but more expensive to run.
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	Other stats were available in 9i, the segment
rem	scan only appeared in 10 (possibly 10.2)

set linesize 156
set trimspool on
set pagesize 60

spool seg_scan


        statistic# = 17
and     value != 0
order by

break on owner skip 1

        owner, object_type, object_name,
        subobject_name, tablespace_name,
        value scans
        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.


  1. Hello Jonathan,

    I can see DBA_HIST_SEG_STAT has TABLE_SCANS_DELTA & TABLE_SCANS_TOTAL and they are being populated as well.

    Are these different than what you’ve mentioned in your writeup that AWR doesn’t record ‘segment scans’ please?


    Comment by PD — August 5, 2011 @ 7:57 pm BST Aug 5,2011 | Reply

    • PD

      My note says that AWR doesn’t report them.
      Of course, time has passed since I wrote the note, and in 11g the AWR report does include Segment Scans, so I’ve updated the note accordingly.

      Comment by Jonathan Lewis — August 6, 2011 @ 8:48 am BST Aug 6,2011 | Reply

  2. […] Jonathan Lewis’s post or this one […]

    Pingback by Real-Time segments statistics « bdt's oracle blog — December 10, 2012 @ 6:29 am GMT Dec 10,2012 | Reply

  3. […] could be combined with this script ( which helps to detect full table scans and index fast full scans:select      […]

    Pingback by Oracle Index Rows Per Leaf Block – Musings of an IT Implementor — March 29, 2020 @ 3:28 pm BST Mar 29,2020 | Reply

  4. […] or you could use Jonathan’s script to see if the index segment(s) has been scanned (full scan).  But these don’t […]

    Pingback by Monitoring Index Usage In Oracle 10G Using V$OBJECT_USAGE – Musings of an IT Implementor — March 29, 2020 @ 3:32 pm BST Mar 29,2020 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: