Tables with full scans in explain plans


select
p.owner,
p.name,
t.num_rows,
ltrim(t.cache) is_table_cached,
decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') is_in_Keep_pool,
s.blocks blocks,
sum(a.executions) number_Full_Table_Scans
from
dba_tables   t,
dba_segments s,
v$sqlarea    a,
(select distinct
address,
object_owner owner,
object_name name
from
v$sql_plan
where
operation = 'TABLE ACCESS'
and
options = 'FULL') p
where
a.address = p.address
and
t.owner = s.owner
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.owner not in ('SYS','SYSTEM')
having
sum(a.executions) > 9
group by
p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
sum(a.executions) desc;

 

Leave a Reply

Your email address will not be published. Required fields are marked *