User Session Analysis (vuser.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides comprehensive analysis of user sessions, combining session information with I/O statistics and process details. It shows database connections, resource usage patterns, and user activity, making it valuable for security monitoring, performance analysis, and session management.

Script

rem  vuser.sql
rem
ttitle 'User Sessions'
rem
set linesize 100
rem
col pid format 999 heading 'PID'
col spid format a6 heading 'SERVER|PID'
col sid format 9999 heading 'SID'
col serial# format 99999 heading 'SERIAL'
col process format a6 heading 'CLIENT|PID'
col osuser format a8 heading 'OS|USERNAME'
col username format a10 heading 'ORACLE|USERNAME'
col shadow format a10 heading 'SHADOW|USERNAME'
col program format a30 heading 'PROGRAM'
col logical format b999999999 heading 'LOGICAL|READS'
col physical_reads format b9999999 heading 'PHYSICAL|READS'
col audsid format b9999999 heading 'AUDIT|SESSION'
col logon_time format a14 heading 'LOGON TIME'
rem
select s.process,
       p.spid,
       p.pid,
       s.sid,
       s.serial#,
       s.osuser,
       s.username,
       i.block_gets + i.consistent_gets logical,
       i.physical_reads,
       s.audsid,
       to_char(s.logon_time, 'MM/DD HH24:MI:SS') logon_time
  from v$process p, v$session s, v$sess_io i
 where i.sid = s.sid
   and s.paddr = p.addr
   and ( s.osuser like '&osuser'
         or s.osuser is null )
   and ( s.username like upper('&user')
         or s.username is null )
 order by i.block_gets + i.consistent_gets desc;

Usage

-- Run the script in SQL*Plus or SQLcl
@vuser.sql

-- When prompted, enter:
-- osuser: Operating system username pattern (% for all)
-- user: Oracle database username pattern (% for all)

-- Examples
Enter value for osuser: %
Enter value for user: %

-- Or filter specific users
Enter value for osuser: appuser
Enter value for user: SALES_USER

Parameters

• &osuser: Operating system username pattern (use % for wildcard)
• &user: Oracle database username pattern (use % for wildcard)

Required Privileges

• SELECT on V$PROCESS
• SELECT on V$SESSION
• SELECT on V$SESS_IO
• Generally available to most database users

Sample Output

User Sessions

CLIENT SERVER PID   SID SERIAL OS       ORACLE      LOGICAL PHYSICAL AUDIT    LOGON TIME
PID    PID             #      USERNAME USERNAME      READS    READS   SESSION
------ ------ --- ----- ------ -------- ---------- --------- -------- --------- --------------
12345  23456   45   145  34567 appuser  SALES_USER  45678901   123456  87654321 12/15 09:15:23
67890  34567   67   234  45678 dbadmin  SYS          2345678    45678  98765432 12/15 08:30:45
54321  45678   89   345  56789 webuser  WEB_APP      1234567    23456  12345678 12/15 10:22:17
98765  56789  123   456  67890 batchjob BATCH_USER   9876543   234567  23456789 12/15 07:45:33

Key Output Columns

• CLIENT PID: Client process ID (from client machine)
• SERVER PID: Oracle server process ID
• PID: Oracle process number
• SID: Session ID
• SERIAL#: Session serial number
• OS USERNAME: Operating system username
• ORACLE USERNAME: Database username
• LOGICAL READS: Total logical I/O (block gets + consistent gets)
• PHYSICAL READS: Physical disk reads performed
• AUDIT SESSION: Audit session ID
• LOGON TIME: Session login timestamp

Understanding Session Metrics

Resource Usage Indicators

• High LOGICAL READS: Sessions performing intensive database operations
• High PHYSICAL READS: Sessions causing disk I/O
• Read Ratios: Physical/Logical ratio indicates buffer cache efficiency

Session Identification

• SID + SERIAL#: Unique session identifier for administration
• Process mapping: Links database sessions to OS processes
• User correlation: Maps database users to OS users

Security Monitoring

• Login patterns: Unusual login times or frequencies
• User activity: Resource usage by user
• Connection sources: Client process identification

Performance Analysis

High-Resource Sessions

1. I/O Intensive users: High LOGICAL or PHYSICAL READS
2. Long-running sessions: Old LOGON_TIME with high activity
3. System resource impact: Sessions affecting overall performance

Session Efficiency

• Buffer cache hit ratio: (LOGICAL - PHYSICAL) / LOGICAL * 100
• Read efficiency: Low PHYSICAL READS relative to LOGICAL
• Activity patterns: Resource usage distribution

Common Use Cases

Security Monitoring

-- Monitor all active user sessions
@vuser.sql
-- Enter % for both parameters
-- Review for suspicious activity or unauthorized access

Performance Analysis

-- Identify high-resource users
@vuser.sql
-- Focus on sessions with high I/O values
-- Correlate with application performance issues

Session Management

-- Find specific user sessions for termination
@vuser.sql
-- Enter specific username patterns
-- Get SID and SERIAL# for ALTER SYSTEM KILL SESSION

Capacity Planning

-- Analyze user resource consumption patterns
@vuser.sql
-- Review resource usage by user type
-- Plan system capacity requirements

Session Administration

Killing Sessions

-- Use SID and SERIAL# from output
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Example based on output
ALTER SYSTEM KILL SESSION '145,34567' IMMEDIATE;

Session Monitoring

-- Get additional session details
SELECT sid, username, status, machine, program,
       sql_id, last_call_et, blocking_session
FROM v$session
WHERE sid = &session_sid;

Resource Limits

-- Check session resource usage against limits
SELECT username, resource_name, current_utilization,
       max_utilization, initial_allocation, limit_value
FROM v$resource_limit
WHERE username IS NOT NULL;

Troubleshooting Session Issues

High I/O Sessions

1. Identify problematic SQL:

   SELECT sid, sql_id, sql_text
   FROM v$session s, v$sql q
   WHERE s.sql_address = q.address
   AND s.sid = &high_io_sid;

2. Check execution plans:

   SELECT plan_table_output
   FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

Blocked Sessions

1. Find blocking relationships:

   SELECT waiting.sid waiting_sid,
          blocking.sid blocking_sid,
          waiting.username waiting_user,
          blocking.username blocking_user
   FROM v$session waiting, v$session blocking
   WHERE waiting.blocking_session = blocking.sid;

Long-Running Sessions

1. Identify old sessions:

   SELECT sid, username, status,
          ROUND((SYSDATE - logon_time) * 24, 2) hours_connected,
          last_call_et seconds_since_last_call
   FROM v$session
   WHERE logon_time < SYSDATE - 1;

Advanced Analysis

Session Resource Summary

-- Aggregate resource usage by user
SELECT username,
       COUNT(*) session_count,
       SUM(i.block_gets + i.consistent_gets) total_logical_reads,
       SUM(i.physical_reads) total_physical_reads,
       ROUND(AVG(i.block_gets + i.consistent_gets), 0) avg_logical_reads
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
AND s.username IS NOT NULL
GROUP BY username
ORDER BY total_logical_reads DESC;

Historical Session Analysis

-- Track session patterns over time
SELECT TO_CHAR(logon_time, 'HH24') login_hour,
       COUNT(*) login_count,
       COUNT(DISTINCT username) unique_users
FROM v$session
WHERE logon_time > SYSDATE - 1
GROUP BY TO_CHAR(logon_time, 'HH24')
ORDER BY login_hour;

Connection Source Analysis

-- Analyze connection sources
SELECT machine, program, COUNT(*) connection_count,
       COUNT(DISTINCT username) unique_users
FROM v$session
WHERE username IS NOT NULL
GROUP BY machine, program
ORDER BY connection_count DESC;

Related Scripts

• All User Sessions (gvuserall.sql) - RAC-wide user session analysis
• Active Session Analysis (../performance-analysis/gvsess.md) - Current activity monitoring
• Session I/O Statistics (../performance-analysis/vsessio.md) - Detailed I/O analysis
• User Account Analysis (duser.sql) - Database user account information