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
,sp.object_owner
,sp.object_name
,sa.sql_text as sqltext
,sa.sql_fulltext AS sql_fulltext
,sa.executions as no_of_full_scans
,tbl.row_num
,tbl.blocks
,tbl.buff_pool
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
,owner
,num_rows AS row_num
,blocks
,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
--
WHERE 1=1
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