1 Reply Latest reply: Jun 12, 2007 12:45 PM by 187628 RSS

    Full table scan

    Jaffy
      I need a query to find out the tables and corresponding sql_id where full table scan is currently happening in the database.
        • 1. Re: Full table scan
          187628
          Try this one:

          select
          p.owner,
          p.name,
          t.num_rows,
          -- ltrim(t.cache) ch,
          decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
          s.blocks blocks,
          sum(a.executions) nbr_FTS
          from
          dba_tables t,
          dba_segments s,
          v$sqlarea a,
          (select distinct
          address,
          object_owner owner,
          object_name name
          from
          v$sql_plan
          where
          operation = 'TABLE ACCESS'
          and
          options = 'FULL') p
          where
          a.address = p.address
          and
          t.owner = s.owner
          and
          t.table_name = s.segment_name
          and
          t.table_name = p.name
          and
          t.owner = p.owner
          and
          t.owner not in ('SYS','SYSTEM')
          having
          sum(a.executions) > 9
          group by
          p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
          order by
          sum(a.executions) desc;