Traditionally speaking, full table scans are not welcome in an OLTP environment. This SQL script will help identify full table scans very easily as there is a 9i view that stores all executed statement plans. Prior to 9i, it would have been a bit more difficult to do this.
Please note that the output will also include something as simple as
SELECT * FROM DUAL
The script can be easily modified to pick out the more resource-intensive FTS by filtering and/or ordering by one of the selected columns or other columns in V$SQLAREA.
set pages 1000
set feedback off
set lines 150
spool fts.lst
Column sql_text Format A50 Heading 'Full Table Scans' Wrap
break on sql_text skip 1
Select Executions, Sorts, Disk_Reads, Buffer_Gets, CPU_Time,
Elapsed_Time, sql_text
from v$sqlarea
where (address, hash_value) in (Select address, hash_value from
v$sql_plan where options like '%FULL%' and operation like '%TABLE%')
Order by Elapsed_Time
/
spool off
set feedback on
