Memory used by a DB session

When a database session is slow, it may be helpful on finding the cause by checking the memory consumed by the session.

1. Identify the session ID (SID), if necessary. Such as:
select * from v$session where lower(program) like '%exe%';

2. Identify the process ID (DB_os_pid / spid) on the Database server:

-- If you know the database sesssion ID, run below query
Select s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
from v$session s, v$process p, v$sqlarea t
where s.paddr = p.addr and s.sql_address = t.address(+)
and s.sid = &SID
;

-- If you know the process ID on the client machine (such as Apps server)
Select s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
from v$session s, v$process p, v$sqlarea t
where s.paddr = p.addr and s.sql_address = t.address(+)
and s.process = '&clientpid' -- the process ID on the client/MT server
;

3. Run query to see how much memory is used by the session:
select spid, program, PGA_USED_MEM/1024/1024 memory_mb from v$process
where spid = DB_os_pid;

4. Run about query from time to time when the database session is ACTIVE. If the memory value keeps increasing, there may be a memory leak on the session.

5. On the OS level, run "ps -ef | grep 21561522" to confirm the session time and other characters (assuming 21561522 is the DB_os_pid).