SQL to find objects on which Full table scan is being performed

Tagged: ,

This topic contains 0 replies, has 0 voices, and was last updated by  Amit Bansal 9 years, 11 months ago.

  • Author
  • #1903

    Amit Bansal

    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


    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.

You must be logged in to reply to this topic.