Identify Full Table Scans

One of the most common issues I see is full-table-scan queries. It’s very easy to fix when you know which query is missing an index. This script will help you find them 🙂 After that, it’s just a matter of a few simple commands to fix it.

SELECT sp.sql_id
,sa.sql_text as sqltext
,sa.sql_fulltext AS sql_fulltext
,sa.executions as no_of_full_scans
FROM v$sql_plan sp
LEFT JOIN v$sqlarea sa
ON sa.address = sp.address
AND sa.hash_value = sp.hash_value
JOIN (SELECT table_name
      ,num_rows AS row_num
      ,buffer_pool AS buff_pool
      FROM dba_tables
      WHERE 1 = 1) tbl
ON tbl.table_name = sp.object_name
AND tbl.owner = sp.object_owner
AND operation = 'TABLE ACCESS'
AND options = 'FULL'
AND object_owner = 'ORACLE_WORLD' -- for a particular user (or comment out to get all)
AND tbl.row_num >= 100000 -- limit the table size to filter out small tables
ORDER BY no_of_full_scans DESC