获取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);