SQL Tuning reading plans and temp usage

What the heck is using so much temp space? Luckily we have an environment with

1. Diagnostic pack purchased

2. 11.2.0.1 version - TEMP_SPACE_ALLOCATED in v$active_session_history

3. software using dbms_application info - action and module populated

And following queries will help. First to get overview and the last queries give pointers inside execution of the queries. Now we will have something where to start fixing our temp wasters.

Trying to figure out stuff from execution plans? Here are some presentations worth reading from Tanel Poder, Christian Antognini and Kyle Hailey.

select sql_id,action,module
      ,min(sql_exec_start),max(sql_exec_start),max(sample_time)
      ,min(TEMP_SPACE_ALLOCATED) mi
      ,trunc(avg(TEMP_SPACE_ALLOCATED)) av
      ,max(TEMP_SPACE_ALLOCATED) mx 
  from v$active_session_history h
 group by sql_id,action,module
 having max(TEMP_SPACE_ALLOCATED) > 0
 order by mx desc;

select * 
  from v$active_session_history h
 order by TEMP_SPACE_ALLOCATED desc nulls last;

select sql_id,action,module
      ,o.object_type,o.object_name,h.sql_plan_operation,h.sql_plan_options
      ,min(sample_time),max(sample_time)
      ,min(TEMP_SPACE_ALLOCATED) mi
      ,trunc(avg(TEMP_SPACE_ALLOCATED)) av
      ,max(TEMP_SPACE_ALLOCATED) mx 
  from v$active_session_history h 
   left outer join dba_objects o on o.object_id = h.current_obj#
 group by sql_id,action,module,o.object_type,o.object_name
         ,SQL_PLAN_OPERATION,SQL_PLAN_OPTIONS
 having max(TEMP_SPACE_ALLOCATED) > 0
 order by mx desc;

DBA_HIST_ACTIVE_SESS_HISTORY for searching temp users from a longer period.

select sql_opname,sql_id,module,action
      ,count(distinct sql_exec_start),max(temp_space_allocated) mx 
  from DBA_HIST_ACTIVE_SESS_HISTORY
  where temp_space_allocated > 0
  group by sql_opname,sql_id,module,action
  order by mx desc
;