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

Home Forums DBA – Wiki 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 7 years, 7 months ago.

  • Author
    Posts
  • #1903

    Amit Bansal
    Keymaster

    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.

You must be logged in to reply to this topic.