查詢全表掃描的sql

      今天因客戶需要,需要查詢出全表掃描的有哪些sql,並且哪些是小表,如果有可能

cache在記憶體中,減少從硬碟讀的次數

   select to_char(sysdate,'yyyymm') as tjyf,a.object_owner, a.object_name,c.BYTES/1024/1024,sum(b.EXECUTIONS)
   from
     (select object_owner,object_name,HASH_VALUE
      from v$sql_plan
      where object_owner not in ('SYS', 'SYSTEM','DBSNMP','OUTLN','PERFSTAT','PUBLIC','SQLAB','WMSYS') and ptions = 'FULL'
      group by object_owner,object_name,HASH_VALUE) a,
     v$sqlarea b, dba_segments c
   where a.HASH_VALUE = b.HASH_VALUE
         and a.OBJECT_OWNER=c.owner
         and a.object_name=c.segment_name
         and c.segment_type='TABLE'
   group by to_char(sysdate,'yyyymm'),a.object_owner, a.object_name,c.BYTES/1024/1024
   order by sum(b.EXECUTIONS);

我們就可以根據以上sql來做進一步的選擇了,比如buffer區的2%以下為小表,和執行次數大於多少以上。