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

 

评论

2 楼 Fangrn 2010-10-24  

实际上我们可以看另外一个视图，其信息更可读一些的：

select * from v$sql_plan;
有一列：opration，options，标识查询的类型，比如全表扫描的话是 table access full

1 楼 wukele 2010-10-21  

查找出来要怎么看，