Find full table Scan query in Oracle

Find full table Scan query in Oracle

This query help to identified the full table scan query for identifying the need of creating index on objects if execution plan is showing more cost and I/O activities.

Find the SQL id and object name which is having full table access

select sql_id,object_owner,object_name from V$SQL_PLAN where
operation='TABLE ACCESS' and
options='FULL' and
object_owner not in ('SYS','SYSTEM','DBSNMP');

Find the SQL Queries which is having full table scans

SELECT s.object_owner, s.object_name,
(SELECT sql_text
FROM v$sqlarea sa
WHERE sa.address = s.address
AND sa.hash_value = s.hash_value) sqltext,
(SELECT executions
FROM v$sqlarea sa
WHERE sa.address = s.address
AND sa.hash_value = s.hash_value) no_of_full_scans
FROM v$sql_plan s
WHERE operation = 'TABLE ACCESS'
AND options = 'FULL'
AND object_owner not in ('SYS','SYSTEM','DBSNMP');

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.