Large User Sessions Analysis (vuserbig.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 identifies resource-intensive user sessions by analyzing I/O activity patterns, showing sessions with high physical reads or logical reads based on configurable thresholds. It provides per-second I/O rates, session duration, and program information to help identify performance issues, resource-heavy operations, and user behavior patterns that may impact system performance.

The Script

rem  vuserbig.sql
rem
set linesize 132
rem
ttitle 'Large User Sessions by Program'
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 log_per_sec format 99999 heading 'LOG|PER|SEC'
col logical format b999999999 heading 'LOGICAL|READS'
col phy_per_sec format 9999 heading 'PHY|PER|SEC'
col physical_reads format b99999999 heading 'PHYSICAL|READS'
col audsid format b9999999 heading 'AUDIT|SESSION'
col program format a37 heading 'PROGRAM NAME'
col logon_time format a8 heading 'LOGON|TIME'
col duration format a8 heading 'DURATION'
rem
select s.process,
       p.spid,
       p.pid,
       s.sid,
       s.serial#,
       s.osuser,
       s.username,
       ( i.block_gets + i.consistent_gets ) /
       ( ( sysdate - s.logon_time ) * 86400 ) log_per_sec,
       i.block_gets + i.consistent_gets logical,
       physical_reads /
       ( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec,
       i.physical_reads,
/*
       s.audsid,
*/
       to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration,
       s.program
  from v$process p, v$session s, v$sess_io i
 where i.sid = s.sid
   and s.paddr = p.addr
   and ( s.username like upper('&user')
      or s.username is null )
   and ( s.osuser like '&os_username'
      or s.osuser is null )
   and s.program like '&program'
   and ( physical_reads >= &physread
      or block_gets + consistent_gets >= &logread )
 order by block_gets + consistent_gets desc;
rem
set linesize 80

Key Features

• Resource-Intensive Session Identification: Finds sessions with high I/O activity
• Configurable Filtering: Multiple filter criteria for targeted analysis
• Per-Second Rate Calculation: Shows I/O rates normalized by session duration
• Process Correlation: Links sessions to server and client processes
• Program Identification: Shows applications and tools generating load
• Duration Analysis: Displays session duration for context
• User Activity Tracking: Correlates activity with specific users and programs

Usage

@vuserbig.sql

Input Parameters:

• user: Database username pattern (use % for all)
• os_username: OS username pattern (use % for all)
• program: Program name pattern (use % for all)
• physread: Minimum physical reads threshold
• logread: Minimum logical reads threshold

Required Privileges

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

Sample Output

                                      Large User Sessions by Program

CLIENT SERVER                      OS       ORACLE    LOG LOGICAL   PHY PHYSICAL              PROGRAM NAME
PID    PID    PID  SID SERIAL USERNAM USERNAME PER SEC     READS   PER    READS DURATION
------ ------ --- ---- ------ ------- -------- ------- --------- ----- -------- -------- ---------------------------------
12345  23456  101  234  45678 jsmith  HR_USER     1234 987654321   123 12345678 02:34:56 hr_application.exe
23456  34567  102  345  56789 rjones  SALES_APP   2345 876543210   234 23456789 01:23:45 sales_batch_process
34567  45678  103  456  67890 admin   DBA_USER     456 765432109    45  3456789 04:56:78 SQL*Plus
45678  56789  104  567  78901 batch   BATCH_USER  3456 654321098   345  4567890 00:45:12 batch_loader.exe
56789  67890  105  678  89012 report  REPORT_USR   567 543210987    56  5678901 12:34:56 Crystal Reports

Key Output Columns

• CLIENT PID: Client process ID
• SERVER PID: Oracle server process ID
• PID: Process ID in Oracle
• SID: Session ID
• SERIAL: Session serial number
• OS USERNAME: Operating system username
• ORACLE USERNAME: Database username
• LOG PER SEC: Logical reads per second since session start
• LOGICAL READS: Total logical reads (block gets + consistent gets)
• PHY PER SEC: Physical reads per second since session start
• PHYSICAL READS: Total physical reads since session start
• DURATION: Session duration (HH:MI:SS)
• PROGRAM NAME: Application or program name

Understanding I/O Rate Metrics

Per-Second Rate Analysis

Logical Reads Per Second

-- Logical read rate interpretation:
-- <100 reads/sec: Low activity
-- 100-1000 reads/sec: Moderate activity
-- 1000-10000 reads/sec: High activity
-- >10000 reads/sec: Very high activity (investigation needed)

Physical Reads Per Second

-- Physical read rate interpretation:
-- <10 reads/sec: Good cache utilization
-- 10-100 reads/sec: Moderate I/O load
-- 100-1000 reads/sec: High I/O activity
-- >1000 reads/sec: Very high I/O (optimization needed)

Session Duration Context

Short vs. Long Sessions

-- Duration analysis:
-- Short sessions (<1 hour): May be batch operations
-- Medium sessions (1-8 hours): Typical user sessions
-- Long sessions (>8 hours): Long-running processes or connections
-- Very long sessions (>24 hours): Potential connection leaks

Activity Intensity

-- Intensity assessment:
-- High rates + short duration: Intensive operations
-- High rates + long duration: Sustained heavy usage
-- Low rates + long duration: Idle or low-activity sessions
-- Variable rates: Interactive user sessions

Common Use Cases

1. Performance Troubleshooting

   • Identify sessions causing system load
   • Find resource-intensive operations
   • Support incident resolution
   • Analyze system performance issues

2. Capacity Planning

   • Monitor user activity patterns
   • Assess resource consumption trends
   • Plan infrastructure scaling
   • Support growth planning

3. User Activity Analysis

   • Monitor user behavior patterns
   • Identify unusual activity
   • Support security investigations
   • Analyze application usage

4. System Monitoring

   • Real-time load identification
   • Resource utilization tracking
   • Performance baseline establishment
   • Alert system support

Advanced Analysis

Program-Based Analysis

Application Performance Assessment

-- Application-specific analysis:
-- Compare performance across applications
-- Identify inefficient application modules
-- Support application optimization
-- Plan application infrastructure

Batch vs. Interactive Workloads

-- Workload pattern identification:
-- Batch processes: High I/O rates, specific programs
-- Interactive sessions: Variable I/O, user applications
-- Reporting tools: Moderate to high I/O, specific duration
-- Administrative tasks: Variable patterns, administrative tools

User Behavior Analysis

User Activity Patterns

-- User behavior assessment:
-- Heavy users with consistent high activity
-- Occasional heavy users
-- Light users with minimal activity
-- Abnormal usage patterns requiring investigation

Resource Consumption Trends

-- Consumption pattern analysis:
-- Consistent resource usage
-- Spiky or variable consumption
-- Growing resource requirements
-- Optimization opportunities

Filtering Examples

High I/O Sessions

-- Find very active sessions:
Enter value for user: %
Enter value for os_username: %
Enter value for program: %
Enter value for physread: 100000
Enter value for logread: 1000000

Specific User Analysis

-- Analyze specific user activity:
Enter value for user: HR_USER
Enter value for os_username: %
Enter value for program: %
Enter value for physread: 1000
Enter value for logread: 10000

Application-Specific Monitoring

-- Monitor specific applications:
Enter value for user: %
Enter value for os_username: %
Enter value for program: %batch%
Enter value for physread: 10000
Enter value for logread: 100000

All Active Sessions

-- Comprehensive active session analysis:
Enter value for user: %
Enter value for os_username: %
Enter value for program: %
Enter value for physread: 1000
Enter value for logread: 10000

Performance Impact Assessment

System Resource Impact

I/O Subsystem Load

-- I/O impact assessment:
-- Total physical reads from identified sessions
-- I/O rate impact on storage subsystem
-- Buffer cache pressure from logical reads
-- Resource contention potential

Memory Utilization

-- Memory impact analysis:
-- Buffer cache utilization by sessions
-- PGA memory consumption
-- Shared pool pressure
-- Overall memory efficiency

Application Performance

User Experience Impact

-- User experience factors:
-- Response time correlation with I/O rates
-- Application performance degradation
-- Concurrent user impact
-- Service level compliance

System Scalability

-- Scalability assessment:
-- Resource consumption per user
-- Concurrent session capacity
-- Growth impact on performance
-- Infrastructure scaling requirements

Troubleshooting Applications

High Resource Consumption

Session Investigation

-- High-consumption session analysis:
-- Identify SQL statements being executed
-- Analyze execution plans
-- Review application logic
-- Assess optimization opportunities

Resolution Strategies

-- Performance improvement approaches:
-- SQL optimization
-- Index tuning
-- Application code optimization
-- Resource allocation adjustment

Abnormal Activity Detection

Unusual Pattern Identification

-- Abnormal activity indicators:
-- Excessive I/O rates for user type
-- Unusual program activity
-- Long-duration high-intensity sessions
-- Unexpected resource consumption patterns

Security Considerations

-- Security assessment:
-- Unauthorized heavy usage
-- Potential data extraction activities
-- Suspicious program execution
-- Unusual access patterns

Integration with Monitoring Systems

Automated Monitoring

Alert Configuration

-- Alert criteria:
-- I/O rate thresholds
-- Session duration limits
-- Resource consumption spikes
-- User activity anomalies

Dashboard Integration

-- Dashboard components:
-- Current high-activity sessions
-- User activity summaries
-- Application performance metrics
-- Resource utilization trends

Historical Analysis

Trend Tracking

-- Long-term analysis:
-- User activity evolution
-- Application performance trends
-- Resource consumption patterns
-- Capacity planning data

Performance Baseline

-- Baseline establishment:
-- Normal user activity levels
-- Typical application I/O patterns
-- Expected resource consumption
-- Performance benchmarks

Best Practices

Regular Monitoring

Monitoring Schedule

-- Regular assessment:
-- Daily high-activity session reviews
-- Weekly user activity analysis
-- Monthly trend assessment
-- Quarterly capacity planning

Threshold Management

-- Threshold optimization:
-- Adjust based on system capacity
-- Consider business requirements
-- Account for growth trends
-- Align with performance goals

Performance Optimization

Optimization Strategy

-- Systematic optimization:
-- Focus on highest-impact sessions
-- Analyze application-specific patterns
-- Implement targeted improvements
-- Monitor optimization effectiveness

User Communication

-- User engagement:
-- Communicate performance guidelines
-- Provide optimization recommendations
-- Support user training initiatives
-- Maintain performance awareness

Related Scripts

• vsession.sql - Comprehensive session analysis
• vsessio.sql - Session I/O analysis
• gvsess.sql - RAC session analysis
• vprocess.sql - Process analysis

Security and Compliance

User Activity Monitoring

Compliance Support

-- Compliance requirements:
-- User activity tracking
-- Resource usage monitoring
-- Audit trail maintenance
-- Performance accountability

Security Monitoring

-- Security considerations:
-- Unusual activity detection
-- Resource abuse prevention
-- Performance impact assessment
-- System protection measures

Summary

This script is essential for:

1. Performance Monitoring - Identifying resource-intensive sessions and their impact on system performance
2. User Activity Analysis - Understanding user behavior patterns and resource consumption
3. Capacity Planning - Monitoring resource usage trends and planning infrastructure capacity
4. Troubleshooting - Rapid identification and resolution of performance issues caused by specific sessions
5. System Optimization - Supporting systematic performance improvement through targeted session analysis