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.
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?
Thanks.
Comment by PD — August 5, 2011 @ 7:57 pm BST Aug 5,2011 |
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 |
[…] 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 |
[…] could be combined with this script (https://jonathanlewis.wordpress.com/segment-scans/) 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 |
[…] or you could use Jonathan’s script https://jonathanlewis.wordpress.com/segment-scans/ 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 |