Finding out the full table scans

There could be cases, queries are fetching too many rows by full tables scans and there are huge "consistent gets" causing the use of more cpu cycles. It might be the case that we missed an index or need to repartition tables. So, first of all we have to find out the full table scans.

SET LONG 1000000

COL object_name FORMAT A25
COL object_name FORMAT A25
COL sql_fulltext FORMAT A50

SELECT sp.object_name, dtab.num_rows, sa.sql_fulltext, sa.executions
FROM v$sql_plan sp
JOIN dba_tables dtab ON (dtab.table_name = sp.object_name)
JOIN v$sqlarea sa ON (sa.address = sp.address AND sa.hash_value =sp.hash_value)
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY sa.executions DESC;

Another faster sql version could be -

SELECT sp.object_name
,(SELECT num_rows FROM dba_tables WHERE table_name = sp.object_name AND owner = sp.object_owner) num_rows
,(SELECT sql_fulltext FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) sql_fulltext
,(SELECT executions FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) full_scans
FROM v$sql_plan sp
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY full_scans DESC;

Now we need to apply both technical and business knowledge to find out why those sqls are doing full scans and if re-indexing would be helpful or something out of the box!