AskDBA.org Forum Forum for Oracle DBA's

About This Topic

• Posted 2 years ago
• By Amit Bansal
• Latest reply from Amit Bansal
• This topic is not a support question
• RSS feed for this topic

Tags

• FTS
• performance

1. 

   Amit Bansal
   

   Oracle DBA
   Joined: Jun '08
   Posts: 73

   offline

   Reputation:96

    

   
   Posted 2 years ago
   #

   This is very straight forward SQL which can be used to identify objects on which Full Table Scan (FTS) is being performed.

   select object_owner,object_name from V$SQL_PLAN where
   operation='TABLE ACCESS' and
   options='FULL' and
   object_owner<>'SYS';

   Please note that this will work with queries cached in shared pool. In 10g and above, you can also query DBA_HIST_SQL_PLAN to get historical information.

   Cheers
   Amit
   http://askdba.org/weblog/