A slow SQL using TEMP, when and how much.

A really slow SQL clause using a lot of temp space. How much and when? Here is a query that reports hourly maximum usage of temp from ASH.

select dy
     , hr
     , coalesce(tmp,0) tmp
     , coalesce(tm,' ') tm
  from (
  select trunc(sample_time,'hh24') samplehr
        , max(tempgb) tmp
        , rpad(' ',max(tempgb)/mx*80,'*') tm
   from ( 
   select sample_time
        , tempgb
        , max(tempgb)over() mx
     from (
    select sample_time
         , sample_id
         , trunc(sum(temp_space_allocated)/1024/1024/104) tempgb
      from dba_hist_active_sess_history 
     where sql_id like coalesce(:sql_id,'%')
     group by sample_time,sample_id
         )
        )
   group by trunc(sample_time,'hh24') 
          , mx
      ) tmps 
 right outer join (
 select hr - level/24 hr 
      , to_char(hr - level/24,'dy','NLS_DATE_LANGUAGE = AMERICAN') dy
   from (
  select trunc(min(sample_time),'hh24') mi
       , trunc(sysdate,'hh24') hr
    from dba_hist_active_sess_history
       ) connect by level < (hr - mi)*24
      ) hours 
    on hours.hr=tmps.samplehr
order by hr desc
;

If you need more detailed information just browse the views as I have done earlier.