Oracle DBA and beyond; these are practical tips for day to day DBA operation and maintenance; a place where you would come to look for a quick fix for a burning situation. I hope that by sharing all these, we all will become better in what we do. And on the way, I hope to save you some sweat :-)
Wednesday, 5 December 2018
How to find the top queries TEMP space consumers, historical data?
The query below identifies the queries which used more then 5 GB of temp space, during the last 2 days:
select sql_id,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig
from DBA_HIST_ACTIVE_SESS_HISTORY
where
sample_time > sysdate-2 and
TEMP_SPACE_ALLOCATED > (5*1024*1024*1024)
group by sql_id order by 2
/
No comments:
Post a Comment