Session I/O with Audit Duration (vsessioa.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 analyzes session I/O statistics enhanced with accurate session duration information from the audit trail and calculates logical reads per second. By correlating session I/O metrics with audit session timestamps, it provides more accurate performance analysis than scripts relying solely on V$SESSION logon_time. This is essential for understanding session performance patterns, identifying resource-intensive sessions, and calculating meaningful I/O rates.

The Script

rem  vsessioa.sql
rem
set linesize 132
rem
ttitle 'Session I/O'
rem
col sid format 999 heading 'SID'
col username format a12 heading 'USERNAME'
col osuser format a12 heading 'OSUSER'
col block_gets format 99999999 heading 'BLOCK|GETS'
col consistent_gets format 999999999 heading 'CONSISTENT|GETS'
col physical_reads format 99999999 heading 'PHYSICAL|READS'
col block_changes format 99999999 heading 'BLOCK|CHANGES'
col consistent_changes format 999999999 heading 'CONSISTENT|CHANGES'
col duration format a8 heading 'DURATION'
col log_sec format 99999 heading 'LOG|READ|SEC'
rem
select i.sid, s.username, s.osuser,
       to_char( trunc(sysdate) + ( sysdate - a.timestamp ),
                'hh24:mi:ss' ) duration,
       block_gets, consistent_gets,
       ( block_gets + consistent_gets ) / ( ( sysdate - a.timestamp ) * 86400 )
       log_sec,
       physical_reads, block_changes, consistent_changes
  from sys.dba_audit_session a, v$session s, v$sess_io i
 where i.sid = s.sid
   and a.sessionid = s.audsid
   and s.username like nvl(upper('&user'),'%')
 order by i.sid;
rem
set linesize 80

Key Features

• Accurate Duration Calculation: Uses audit trail timestamps for precise session duration
• I/O Rate Analysis: Calculates logical reads per second based on actual session duration
• Comprehensive I/O Metrics: Shows all major I/O statistics per session
• User Filtering: Supports filtering by specific usernames or patterns
• Resource Intensity Analysis: Identifies high I/O sessions and patterns
• Performance Baseline: Establishes session-level performance baselines
• Audit Trail Integration: Leverages audit information for enhanced analysis

Usage

@vsessioa.sql

Input Parameters:

• user: Username pattern (use % for all users, or specific username)

Required Privileges

SELECT on SYS.DBA_AUDIT_SESSION
SELECT on V$SESSION
SELECT on V$SESS_IO

Sample Output

                                              Session I/O

SID USERNAME     OSUSER       DURATION    BLOCK     CONSISTENT  LOG PHYSICAL    BLOCK   CONSISTENT
                                          GETS           GETS READ     READS  CHANGES      CHANGES
                                                                  SEC
--- ------------ ------------ -------- -------- ------------- ---- -------- -------- -------------
123 HR_USER      jsmith       02:34:56   123456     9876543210 1078    45678   234567     1234567890
234 SALES_APP    rjones       01:45:23    567890     5432109876 2890    89012   345678     2345678901
345 BATCH_USER   batch        04:12:45  2345678    19876543210 1345   234567  1234567     9876543210
456 REPORT_USR   sreports     00:23:45    89012      987654321 7234     5678    45678      123456789
567 DBA_USER     admin        12:34:56    45678      123456789   34     1234     2345       12345678

Key Output Columns

• SID: Session ID
• USERNAME: Database username
• OSUSER: Operating system username
• DURATION: Session duration in HH:MI:SS format
• BLOCK GETS: Number of blocks read in current mode (for modifications)
• CONSISTENT GETS: Number of blocks read in consistent mode (for queries)
• LOG READ SEC: Logical reads per second (block gets + consistent gets) / duration
• PHYSICAL READS: Number of blocks read from disk
• BLOCK CHANGES: Number of blocks modified
• CONSISTENT CHANGES: Number of consistent read blocks that were changed

Understanding Session I/O Metrics

I/O Operation Types

Block Gets vs. Consistent Gets

-- Block Gets (Current Mode):
-- Used for INSERT, UPDATE, DELETE operations
-- Reads most current version of blocks
-- Higher values indicate DML-heavy sessions
-- Associated with transaction processing

-- Consistent Gets (Consistent Read Mode):
-- Used for SELECT operations
-- Reads consistent point-in-time data
-- Higher values indicate query-heavy sessions
-- May involve undo data reconstruction

Performance Rate Analysis

Logical Reads Per Second

-- I/O rate interpretation:
-- <100 reads/sec: Low activity session
-- 100-1000 reads/sec: Moderate activity
-- 1000-5000 reads/sec: High activity
-- >5000 reads/sec: Very high activity (investigate)

Session Duration Context

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

Common Use Cases

1. Session Performance Analysis

   • Identify resource-intensive sessions
   • Analyze session I/O efficiency patterns
   • Support individual session optimization
   • Monitor user activity patterns

2. Capacity Planning

   • Assess user resource consumption trends
   • Plan infrastructure scaling based on usage
   • Understand peak usage characteristics
   • Support hardware sizing decisions

3. Application Monitoring

   • Monitor application session behavior
   • Identify performance bottlenecks by user type
   • Support application optimization efforts
   • Validate application efficiency

4. Security and Compliance

   • Track user database access patterns
   • Monitor unusual activity levels
   • Support audit trail analysis
   • Identify potential security issues

Advanced Analysis

Session Activity Patterns

User Type Analysis

-- Analyze patterns by user type:
-- Interactive users: Variable I/O, moderate duration
-- Batch processes: High I/O, predictable duration
-- Application connections: Consistent patterns
-- Administrative sessions: Variable, often high privilege

Temporal Analysis

-- Time-based pattern analysis:
-- Business hours vs. off-hours activity
-- Peak usage period identification
-- Session duration distribution
-- I/O rate variance over time

Performance Efficiency Assessment

I/O Efficiency Metrics

-- Calculate additional efficiency metrics:
-- Physical read ratio: physical_reads / (block_gets + consistent_gets)
-- Change ratio: block_changes / block_gets
-- Read/write ratio: consistent_gets / block_gets
-- Cache hit efficiency assessment

Resource Utilization Correlation

-- Correlate with other metrics:
-- CPU usage per session
-- Memory allocation patterns
-- Lock and latch usage
-- Wait event correlations

Optimization Applications

Session-Level Optimization

High I/O Session Investigation

-- For sessions with high logical reads per second:
-- Analyze SQL statements being executed
-- Review execution plans for inefficiencies
-- Identify missing or suboptimal indexes
-- Consider query optimization opportunities

Long-Running Session Analysis

-- For sessions with extended duration:
-- Investigate if duration is appropriate
-- Check for connection pooling issues
-- Analyze transaction scope and design
-- Consider session state optimization

Application Performance Tuning

Application Pattern Optimization

-- Application-level improvements:
-- Connection pooling optimization
-- Transaction scope reduction
-- Query efficiency improvements
-- Batch processing optimization

User Experience Enhancement

-- User experience improvements:
-- Response time optimization
-- Resource usage balancing
-- Peak load distribution
-- Service level optimization

Filtering Examples

Specific User Analysis

-- Analyze specific user activity:
Enter value for user: HR_USER

Application User Pattern

-- Analyze application users:
Enter value for user: %APP%

All User Analysis

-- Comprehensive user analysis:
Enter value for user: %

System User Exclusion

-- Exclude system users:
Enter value for user: %
-- Then filter out SYS, SYSTEM, etc. in WHERE clause

Integration with Audit Analysis

Audit Trail Correlation

Session Lifecycle Analysis

-- Enhanced analysis with audit data:
-- Session start and end times
-- Login success/failure correlation
-- Privilege usage tracking
-- Resource consumption accountability

Compliance Reporting

-- Compliance support:
-- User activity documentation
-- Resource usage accountability
-- Access pattern analysis
-- Audit trail completeness validation

Historical Analysis

Trend Identification

-- Long-term analysis capabilities:
-- User activity evolution over time
-- Resource consumption growth patterns
-- Peak usage period identification
-- Capacity planning data collection

Performance Baseline Establishment

-- Baseline development:
-- Normal user activity levels
-- Expected I/O rates by user type
-- Typical session duration patterns
-- Performance benchmark establishment

Troubleshooting Applications

High Resource Usage Investigation

Excessive I/O Sessions

-- Investigation steps for high I/O:
-- Identify specific SQL causing high reads
-- Analyze query execution plans
-- Review indexing strategies
-- Consider query optimization

Long Duration Sessions

-- Long session investigation:
-- Check for appropriate business justification
-- Analyze transaction design efficiency
-- Review connection management practices
-- Consider session state optimization

Performance Issues Resolution

Response Time Problems

-- Response time optimization:
-- Correlate I/O rates with response times
-- Identify bottleneck sessions
-- Optimize resource-intensive operations
-- Implement session-level tuning

Capacity Issues

-- Capacity problem resolution:
-- Identify peak usage contributors
-- Plan workload distribution
-- Optimize resource allocation
-- Consider infrastructure scaling

Best Practices

Regular Monitoring

Monitoring Schedule

-- Monitoring frequency:
-- Real-time during peak periods
-- Daily session activity reviews
-- Weekly trend analysis
-- Monthly capacity assessments

Alert Criteria

-- Alert thresholds:
-- Logical reads per second > 5000
-- Session duration > expected maximums
-- Unusual activity patterns
-- Resource consumption spikes

Performance Optimization

Systematic Approach

-- Optimization methodology:
-- Identify highest impact sessions
-- Analyze root causes systematically
-- Implement targeted optimizations
-- Validate improvement effectiveness

Preventive Measures

-- Prevention strategies:
-- Regular performance health checks
-- Proactive capacity planning
-- Application design standards
-- User education and guidelines

Related Scripts

• vsessio.sql - Session I/O without audit correlation
• vuserbig.sql - Large user sessions analysis
• vsession.sql - Comprehensive session analysis
• vmyio.sql - Current session I/O analysis

Security Considerations

Audit Trail Dependency

Audit Configuration Requirements

-- Required audit settings:
-- AUDIT_TRAIL parameter must be enabled
-- Session auditing must be configured
-- Sufficient audit trail storage
-- Regular audit trail maintenance

Data Privacy

-- Privacy considerations:
-- User activity visibility
-- Resource usage disclosure
-- Audit data sensitivity
-- Access control requirements

Summary

This script is essential for:

1. Session Performance Analysis - Detailed analysis of session I/O patterns with accurate duration calculations
2. Resource Monitoring - Understanding user resource consumption patterns and trends
3. Capacity Planning - Supporting infrastructure planning with detailed usage data
4. Application Optimization - Identifying and optimizing resource-intensive application sessions
5. Audit Integration - Leveraging audit trail data for enhanced session analysis and accountability