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:

 executions * total_large_scans sum_large_scans 
 count ( *) total_large_scans, 
 sys.v_$sql_plan a, 
 sys.dba_segments b, 
 sys.v_$sql c 
 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