Hello
Im trying to retrieve information of total TEMP usage over the past 24 hours broken down into minutes
select to_char(sample_time, 'hh24:mi'),sum(temp_space_allocated/1024/1024/1024)
from dba_hist_active_sess_history
where sample_time > sysdate - 1
group by to_char(sample_time, 'hh24:mi'),2
order by 1;
I know the above query is incorrect - it does not infact reflect the truth, for example 2 sessions may runnning in the database be consuming 10MB of TEMP each over the duration of a minute, this SQL will therefore reflect that as 120MB of temp used (considering 10 second samples) when in reality the temp usage would be near 20MB.
I was wondering aside from creating some tables in the db job and recording the total tmp consumption at various intervals, if anyone has any approached this by pulling the information out from dba_hist_active_sess_history?