获取oracle全表扫描的sql列表

oracle sql优化是一项比较艰巨的任务了，有时我们可以将sql放在pl/sql dev中看执行计划去分析sql的效率，那是个很不错的方式，这里我也给出一个获取sql全表扫描的语句。

 

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 OPTIONS = '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);