Friday, June 24, 2011

Find Large table scans

SQL statement that contains a large table scan (defined in this query as a table over 1MB), along with a
count of how many large scans it causes for each execution, the total number of times the statement has been executed, and then the sum total of all scans it has caused on the system:

select
 sql_text,
 total_large_scans, 
 executions,
 executions * total_large_scans sum_large_scans 
from 
(select
 sql_text,
 count ( *) total_large_scans, 
 executions 
from
 sys.v_$sql_plan a, 
 sys.dba_segments b, 
 sys.v_$sql c 
where
 a.object_owner (+) = b.owner 
 and a.object_name (+) = b.segment_name 
and b.segment_type  in ('TABLE', 'TABLE PARTITION') 
and a.operation like '%TABLE%' 
and a.options = 'FULL' 
and c.hash_value = a.hash_value 
and b.bytes / 1024 > 1024 
group by
 sql_text, executions) 
order by
 4 desc;

No comments:

Post a Comment