jump to content

Full Table Scans

Here are some scripts related to Full Table Scans .

System Statistics (Table)

SYSTEM STATISTICS (TABLE) NOTES:

  • Statistic Name - Name of the statistic
  • Bytes - Size

  • This query provides 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.
  • Table fetch by rowid reflect the cumulative number of rows fetched from tables using a TABLE ACCESS BY ROWID operation.
  • Table fetch continued row reflect the cumulative number of continued rows fetched. This value is incremented when accessing a row that is longer than a block in length and when accessing "migrated" rows. Migrated rows are rows that were relocated from their original location to a new location because of an update that increased their size to the point where they could no longer be accommodated inside their original block. Access to migrated rows will cause this statistic's value to increment only if the access is performed by ROWID. Full table scans of tables that contain migrated rows do not cause this counter to increment.
  • Table scan blocks gotten reflect the cumulative number of blocks read for full table scans.
  • Table scans rows gotten reflect the cumulative number of rows read for full table scans.
  • Table scans (cache partitions) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
  • Table scans (long scans) indicate a full scan of a table that has > 5 database blocks.
  • Table scans (rowid ranges) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
  • Table scans (short scans) indicate a full scan of a table that has <= 5 database blocks. 

    select 	NAME,
	VALUE
from 	v$sysstat
where	NAME like '%table'

    Process Table Scans

    PROCESS TABLE SCAN NOTES:

  • User Process - Name of user process
  • Long Scans - Full scan of a table that has > 5 database blocks.
  • Short Scans - Full scan of a table that has <= 5 database blocks.
  • Row Retrieved - Cumulative number of rows read for full table scans. 

    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||') '

    Process Table Scans (Avg)

    PROCESS TABLE SCAN (AVERAGE) NOTES:

  • User Process - Name of user process
  • Short Scans - Number of short scans (<= 5 blocks)
  • Long Scans - Number of long scans (> 5 blocks)
  • Rows Retrieved - Number of rows retrieved
  • Long Scans Length - Average long scan length (i.e. full table scan of > 5 blocks) 

    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",
round((sum(decode(name,'table scan rows gotten',value)) - (sum(decode(name,'table scans (short tables)',value)) * 5)) / (sum(decode(name,'table scans (long tables)', value))),2) "Long Scans Length"
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||') '
having sum(decode(name,'table scans (long tables)', value)) != 0
order by 3 desc