Enhanced User Session Analysis (vuserx.sql)

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 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 a11 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') 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.sid = &sid
 order by block_gets + consistent_gets desc;

What This Script Does

This script provides a comprehensive analysis of a specific Oracle database session, combining process information, I/O statistics, and session metadata. It’s particularly useful for:

Process Correlation: Links client and server processes with the database session
I/O Analysis: Shows logical and physical reads for the session
Session Identification: Provides complete session identification details
Audit Trail: Includes audit session ID for security tracking
Performance Investigation: Helps diagnose session-specific performance issues

Usage

Run the script in SQL*Plus or a compatible Oracle client:

@vuserx.sql

When prompted, enter the Session ID (SID) you want to analyze:

Enter value for sid: 142

Parameters

&sid: The Session ID (SID) to analyze (prompted at runtime)

Required Privileges

SELECT on V$SESSION
SELECT on V$PROCESS
SELECT on V$SESS_IO

Sample Output

                                User Sessions

CLIENT SERVER                     ORACLE       LOGICAL    PHYSICAL      AUDIT
PID    PID    PID  SID SERIAL  OS      USERNAME      READS      READS   SESSION LOGON TIME
------ ------ --- ---- ------ -------- ---------- ---------- --------- --------- -----------
24567  12845   25  142  34521 oracle   SCOTT       12,450,890   234,567   8901234 01/15 09:45

Key Output Columns

Process Information

CLIENT PID: Process ID on the client machine
SERVER PID: Operating system process ID of the server process
PID: Oracle process identifier

Session Identification

SID: Session identifier
SERIAL: Serial number (used with SID for session operations)
OS USERNAME: Operating system user who initiated the session
ORACLE USERNAME: Database user account

I/O Statistics

LOGICAL READS: Total logical reads (block_gets + consistent_gets)
PHYSICAL READS: Disk reads performed by the session

Audit Information

AUDIT SESSION: Audit session identifier for tracking
LOGON TIME: When the session connected (MM/DD HH24:MI format)

Understanding the Metrics

Logical vs Physical Reads

High Logical Reads: Indicates heavy buffer cache usage
High Physical Reads: Suggests poor buffer cache hit ratio
Ratio Analysis: Physical/Logical ratio indicates cache efficiency

Process Correlation

Client PID: Useful for tracing application processes
Server PID: For OS-level monitoring and troubleshooting
Oracle PID: Internal process identifier

Common Use Cases

1. Session Performance Analysis

-- Run vuserx.sql for a problematic session
@vuserx.sql
Enter value for sid: 142

-- Compare I/O metrics with other sessions

2. Process Troubleshooting

-- Identify OS process consuming resources
-- Match SERVER PID with OS monitoring tools

3. Security Auditing

-- Track session activity using AUDIT SESSION ID
-- Correlate with audit logs

4. Application Debugging

-- Use CLIENT PID to match with application logs
-- Track session lifecycle from logon time

Performance Analysis

High I/O Sessions

If a session shows extremely high logical or physical reads:

Check the SQL being executed using gvsessev9_bo.sql
Analyze wait events with vsessw.sql
Review execution plans for inefficient queries

Buffer Cache Efficiency

Calculate buffer cache hit ratio for the session:

Hit Ratio = (Logical Reads - Physical Reads) / Logical Reads * 100

Process Resource Usage

Use the SERVER PID to check OS-level resource consumption:

# On Unix/Linux
ps -ef | grep <SERVER_PID>
top -p <SERVER_PID>

Troubleshooting

Session Not Found

no rows selected

Solution: Verify the SID exists in V$SESSION

Permission Errors

ORA-00942: table or view does not exist

Solution: Grant necessary privileges on V$ views

Invalid SID Format

ORA-01722: invalid number

Solution: Enter a numeric SID value

Best Practices

Regular Monitoring: Check high-activity sessions periodically
Baseline Metrics: Establish normal I/O patterns for comparison
Correlation: Use with other monitoring scripts for complete picture
Documentation: Record findings for trend analysis

gvsess.sql - Overview of all current sessions
vsession.sql - Detailed session information
vsessio.sql - Session I/O statistics
gvsessev9_bo.sql - Session wait events
vsessw.sql - Current session waits
vuser.sql - Basic user session information

Notes

The script orders results by logical reads (descending), though with a single SID this ordering isn’t significant
Process IDs are OS-specific and may vary in format
Audit session ID is useful for correlating with database audit trails
Consider session state (ACTIVE/INACTIVE) when analyzing metrics