查找全表扫描的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%以下为小表,和执行次数大于多少以上。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-605699/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-605699/