Yuan Yao, An Oracle ACE's Blog

The PGA_ALLOCATED column in ASH records the amount of PGA memory consumed by the session at the time the sample was taken, so if PGA memory usage is excessive, the following query can identify the offending SQL statements:

SELECT username,
       module,
       sql_id,
       TRUNC(MAX(pga_allocated) / 1024 / 1024) AS pga_allocated_mb
FROM   dba_hist_active_sess_history a,
       dba_users u
WHERE  trunc(sample_time) = trunc(sysdate) - 1
  AND  u.user_id = a.user_id
  AND  u.username NOT IN ('SYS', 'DBSNMP')
GROUP BY username, module, sql_id
ORDER BY pga_allocated_mb DESC
FETCH FIRST 10 ROWS ONLY;

Another useful column in ASH is TEMP_SPACE_ALLOCATED which indicates the temporary tablespace consumed by the session at the time the sample was taken, you can easily modify the query to identify the top consumers of temporary tablespace.

• Top SQL statements
• Top Wait Events
• Top Accessed Objects
• Top PGA or Temp Space Consumers
• General Query Formula