HOW TO FIND THE SESSION CONSUMING HIGH MEMORY AT DATABASE LEVEL.

 ###########Top 10 highest memory sessions#############

SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       p.spid AS os_pid,
       ROUND(st.value/1024/1024,2) AS pga_memory_mb
FROM v$session s
JOIN v$sesstat st ON s.sid = st.sid
JOIN v$statname sn ON st.statistic# = sn.statistic#
JOIN v$process p ON s.paddr = p.addr
WHERE sn.name = 'session pga memory'
---and s.program not like '%ORACLE%'
ORDER BY st.value DESC;

For Multi Tennant DB

col PDB_NAME  form a15
col USERNAME form a15
col MACHINE form a18
col Module form a15
col OS_PID form a10
SELECT 
       c.name AS pdb_name,
       s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       s.module,
       s.sql_id,
       p.spid AS os_pid,
       ROUND(st.value/1024/1024,2) AS pga_memory_mb,
       s.logon_time
FROM gv$session s
JOIN gv$sesstat st 
     ON s.sid = st.sid 
     AND s.inst_id = st.inst_id
JOIN gv$statname sn 
     ON st.statistic# = sn.statistic# 
     AND st.inst_id = sn.inst_id
JOIN gv$process p 
     ON s.paddr = p.addr 
     AND s.inst_id = p.inst_id
LEFT JOIN v$containers c 
     ON s.con_id = c.con_id
WHERE sn.name = 'session pga memory'
AND s.type = 'USER'
AND s.program NOT LIKE '%ORACLE%'
ORDER BY st.value DESC;

#####This shows sessions active about 1 hour ago with PGA usage###########

SELECT 
    ash.sample_time,
    ash.session_id,
    ash.session_serial#,
    ash.sql_id,
    ash.program,
    ROUND(ash.pga_allocated/1024/1024,2) AS pga_mb
FROM dba_hist_active_sess_history ash
WHERE ash.sample_time BETWEEN SYSDATE - 1/24 AND SYSDATE - 55/1440
ORDER BY ash.pga_allocated DESC;

###Top SQL by PGA Memory Usage

SELECT 
    c.name AS pdb_name,
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    s.program,
    ROUND(st.value/1024/1024,2) AS pga_mb
FROM gv$session s
JOIN gv$sesstat st 
     ON s.sid = st.sid 
     AND s.inst_id = st.inst_id
JOIN gv$statname sn 
     ON st.statistic# = sn.statistic#
     AND st.inst_id = sn.inst_id
LEFT JOIN v$containers c 
     ON s.con_id = c.con_id
WHERE sn.name = 'session pga memory'
AND s.type='USER'
ORDER BY st.value DESC;

##Query: SQL_ID Consuming Highest Memory in a Time Window

SELECT 
    c.name AS pdb_name,
    ash.sql_id,
    COUNT(*) samples,
    ROUND(MAX(ash.pga_allocated)/1024/1024,2) AS max_pga_mb,
    ROUND(AVG(ash.pga_allocated)/1024/1024,2) AS avg_pga_mb
FROM dba_hist_active_sess_history ash
LEFT JOIN v$containers c
       ON ash.con_id = c.con_id
WHERE ash.sample_time BETWEEN 
      TO_DATE('08-MAR-2026 01:00','DD-MON-YYYY HH24:MI')
  AND TO_DATE('08-MAR-2026 02:30','DD-MON-YYYY HH24:MI')
AND ash.sql_id IS NOT NULL
GROUP BY c.name, ash.sql_id
ORDER BY max_pga_mb DESC;

--OR only for PDBS

SELECT 
    c.name AS pdb_name,
    ash.sql_id,
    COUNT(*) samples,
    ROUND(MAX(ash.pga_allocated)/1024/1024,2) AS max_pga_mb,
    ROUND(AVG(ash.pga_allocated)/1024/1024,2) AS avg_pga_mb
FROM dba_hist_active_sess_history ash
LEFT JOIN v$containers c
       ON ash.con_id = c.con_id
WHERE ash.sample_time BETWEEN 
      TO_DATE('08-MAR-2026 01:00','DD-MON-YYYY HH24:MI')
  AND TO_DATE('08-MAR-2026 02:30','DD-MON-YYYY HH24:MI')
AND ash.sql_id IS NOT NULL AND c.name <>'CDB$ROOT'
GROUP BY c.name, ash.sql_id
ORDER BY max_pga_mb DESC;