V$SESSION V$PROCESS Join - Oracle Session to OS Process Mapping

V$SESSION V$PROCESS Join - Complete Guide

Joining V$SESSION and V$PROCESS is essential for Oracle DBAs to map database sessions to operating system processes. This guide provides ready-to-run queries for session analysis and troubleshooting.

Quick Join Query

-- Basic V$SESSION to V$PROCESS join
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.program,
  p.spid AS os_pid,
  p.pga_used_mem/1024/1024 AS pga_mb
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.type = 'USER'
ORDER BY p.pga_used_mem DESC;

---

Understanding the Join

Key Columns

V$SESSION Column	V$PROCESS Column	Purpose
PADDR	ADDR	Join key - Process address
SID	-	Session identifier
SERIAL#	-	Session serial number
USERNAME	-	Database user
PROGRAM	-	Client program name
-	SPID	OS process ID
-	PGA_USED_MEM	PGA memory used

The Join Explained

-- PADDR in V$SESSION points to ADDR in V$PROCESS
FROM v$session s
JOIN v$process p ON s.paddr = p.addr

---

Common Use Cases

1. Find OS Process for a Session

-- Get OS PID for a specific SID
SELECT p.spid AS os_process_id
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = &sid;

2. Find Session from OS Process

-- Get session details from OS PID
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.sql_id,
  s.event,
  s.program
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE p.spid = '&os_pid';

3. All Active Sessions with OS Details

-- Active sessions with full details
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.machine,
  s.program,
  p.spid AS os_pid,
  s.sql_id,
  s.event,
  s.seconds_in_wait,
  s.last_call_et AS seconds_active
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.status = 'ACTIVE'
  AND s.type = 'USER'
ORDER BY s.last_call_et DESC;

4. Sessions Using Most Memory

-- Top PGA consumers
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  p.spid AS os_pid,
  ROUND(p.pga_used_mem/1024/1024, 2) AS pga_used_mb,
  ROUND(p.pga_alloc_mem/1024/1024, 2) AS pga_alloc_mb,
  ROUND(p.pga_max_mem/1024/1024, 2) AS pga_max_mb
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.type = 'USER'
ORDER BY p.pga_used_mem DESC
FETCH FIRST 20 ROWS ONLY;

5. Sessions with Current SQL

-- Sessions with their executing SQL
SELECT
  s.sid,
  s.serial#,
  s.username,
  p.spid AS os_pid,
  s.sql_id,
  SUBSTR(q.sql_text, 1, 100) AS sql_text
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
LEFT JOIN v$sql q ON s.sql_id = q.sql_id AND s.sql_child_number = q.child_number
WHERE s.status = 'ACTIVE'
  AND s.type = 'USER'
ORDER BY s.sid;

6. Generate Kill Commands

-- Generate OS kill commands for specific sessions
SELECT
  'kill -9 ' || p.spid AS os_kill_command,
  'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' AS db_kill_command,
  s.username,
  s.program
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.username = '&username';

---

RAC Considerations

For RAC environments, include instance information:

-- RAC: Sessions across all instances
SELECT
  s.inst_id,
  s.sid,
  s.serial#,
  s.username,
  p.spid AS os_pid,
  s.program
FROM gv$session s
JOIN gv$process p ON s.paddr = p.addr AND s.inst_id = p.inst_id
WHERE s.type = 'USER'
ORDER BY s.inst_id, s.sid;

---

Extended Join with More Views

Include Wait Events

SELECT
  s.sid,
  s.serial#,
  s.username,
  p.spid,
  s.event,
  s.wait_class,
  s.seconds_in_wait,
  sw.state
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
JOIN v$session_wait sw ON s.sid = sw.sid
WHERE s.type = 'USER'
  AND s.wait_class != 'Idle';

Include Blocking Information

SELECT
  s.sid,
  s.serial#,
  s.username,
  p.spid AS os_pid,
  s.blocking_session,
  s.event,
  bs.username AS blocker_user,
  bp.spid AS blocker_os_pid
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
LEFT JOIN v$session bs ON s.blocking_session = bs.sid
LEFT JOIN v$process bp ON bs.paddr = bp.addr
WHERE s.blocking_session IS NOT NULL;

---

Troubleshooting

Session Not Found

If the join returns no rows, the session might be:

• A background process (filter with s.type = 'USER')
• Already disconnected
• Using a different address mapping

-- Check all session types
SELECT type, COUNT(*)
FROM v$session
GROUP BY type;

Permission Issues

Required privileges:

GRANT SELECT ON v$session TO username;
GRANT SELECT ON v$process TO username;
-- Or grant the view role
GRANT SELECT_CATALOG_ROLE TO username;

---

Related Scripts

• gvsess.sql - Active session analysis
• vprocess.sql - Process information
• vsession.sql - Session overview
• gvlockb.sql - Blocking lock analysis