今天因客户需要,需要查找出全表扫描的有哪些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%以下为小表,和执行次数大于多少以上。