Session Events and CPU Analysis (sess.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 detailed analysis of session wait events and CPU statistics across RAC instances, with specific filtering for target users and sessions. It shows comprehensive wait event information including timeouts, average wait times, and CPU consumption patterns. The script is designed for focused analysis of specific application users and can be adapted for different monitoring scenarios by modifying the hardcoded filter criteria.

The Script

rem  gvsessev.sql
rem
ttitle 'Global Session Events'
rem
set linesize 132
rem
col inst_id format 999 heading 'INST'
col CPU format 99,999,999.90
col s_sid format 9999 heading 'SESS|SID'
col e_sid format 9999 heading 'EVNT|SID'
col username format a10 heading 'USERNAME'
col elapsed_secs format 9999999 heading 'ELAPSED|SECS'
col event format a28 heading 'EVENT'
col total_waits format 99999999 heading 'TOTAL|WAITS'
col total_timeouts format b9999999 heading 'TOTAL|TIMEOUTS'
col time_waited_secs format b9999999.00 heading 'TIME|WAITED|(secs)'
col timeout_pct format b999.9 heading 'TIME|OUT|PCT'
col time_waited format b999999999 heading 'TIME|WAITED'
col average_wait format b999999.9 heading 'AVERAGE|WAIT'
col avg_wait_ms format b9999.0 heading 'AVERAGE|WAIT|(ms)'
col MAX_WAIT format b999999.9 heading 'MAX|WAIT'
col MAX_WAIT_MS format b999999 heading 'MAX|WAIT|(ms)'
COLUMN   module HEADING  'MODULE' FORMAT   a29 trunc
rem
break on report on inst_id on s_sid on username on elapsed_secs skip 1
compute sum of total_waits total_timeouts time_waited_secs on report s_sid
set pause off;

select
       s.inst_id,
       s.sid s_sid,
       -- e.sid e_sid,
       -- s.module,
       s.username,
       (sysdate - s.logon_time) * 24 *60 *60 elapsed_secs,
       e.event,
       e.total_waits,
       e.total_timeouts,
       100 * e.total_timeouts / e.total_waits timeout_pct,
       e.time_waited_micro / 1000000 time_waited_secs,
       e.MAX_WAIT * 10 max_wait_ms,
       e.time_waited_micro / e.total_waits / 1000 avg_wait_ms
  from gv$session_event e, gv$session s
   where s.sid like nvl( '&sid', '%')
   and s.inst_id = e.inst_id
   and s.sid -0 = e.sid
   and s.osuser = 'boadmtst'
   and s.username = 'BO_PAL'
 order by elapsed_secs desc, e.inst_id, e.sid, e.event;



select distinct s.statistic#, s.sid, s.value / 100 CPU, n.name
    from gv$sesstat s, gv$statname n, gv$session ss
   where n.statistic# = s.statistic#
     and s.value <> 0
   and ss.inst_id = s.inst_id
     and s.sid like nvl('&sid','%')
   and ss.sid -0 = s.sid
   and ss.osuser = 'boadmtst'
   and ss.username = 'BO_PAL'
  and n.name like '%CPU%'
  order by sid, n.name
/


set pause on;

Key Features

• RAC-Aware Analysis: Shows events across all cluster instances
• Session Wait Event Details: Comprehensive wait event statistics per session
• CPU Usage Analysis: Detailed CPU consumption metrics
• Timeout Analysis: Calculates timeout percentages and patterns
• Duration Context: Shows session elapsed time for perspective
• Dual Analysis: Both wait events and CPU statistics in one script
• Filtering Capability: Supports session ID filtering and user-specific analysis

Usage

@sess.sql

Input Parameters:

• sid: Session ID pattern (use % for all sessions)

Note: This script contains hardcoded filters for specific users (boadmtst and BO_PAL) that should be modified for your environment.

Required Privileges

SELECT on GV$SESSION_EVENT
SELECT on GV$SESSION
SELECT on GV$SESSTAT
SELECT on GV$STATNAME

Sample Output

                                              Global Session Events

INST SESS USERNAME  ELAPSED EVENT                     TOTAL    TOTAL TIME AVERAGE MAX
     SID             SECS                           WAITS TIMEOUTS OUT WAITED WAIT WAIT
                                                                   PCT  (secs) (ms) (ms)
---- ---- ---------- ------- -------------------------- -------- -------- ---- ------- ----- ------
   1  234 BO_PAL      145678 SQL*Net message from client  23456        0  0.0 12345.67  526     0
                              db file sequential read      12345        0  0.0   234.56   19   450
                              db file scattered read        5678        0  0.0   123.45   22   890
                              log file sync                 2345        0  0.0    45.67   19   120

   2  456 BO_PAL       98765 SQL*Net message from client  18901        0  0.0  9876.54  523     0
                              db file sequential read       9876        0  0.0   198.76   20   380
                              enq: TX - row lock contention  234        12  5.1    67.89  290  1200

STATISTIC#    SID      CPU NAME
---------- ------ -------- --------------------------------------------------
        12    234   123.45 CPU used by this session
        13    234    45.67 CPU used when call started
        12    456    89.01 CPU used by this session
        13    456    34.56 CPU used when call started

Key Output Columns

Wait Events Section

• INST: RAC instance ID
• SESS SID: Session ID
• USERNAME: Database username
• ELAPSED SECS: Session duration in seconds
• EVENT: Wait event name
• TOTAL WAITS: Number of times session waited for this event
• TOTAL TIMEOUTS: Number of waits that timed out
• TIME OUT PCT: Percentage of waits that timed out
• TIME WAITED (secs): Total time waited in seconds
• AVERAGE WAIT (ms): Average wait time in milliseconds
• MAX WAIT (ms): Maximum wait time in milliseconds

CPU Statistics Section

• STATISTIC#: Statistic number identifier
• SID: Session ID
• CPU: CPU time in centiseconds (divided by 100)
• NAME: CPU statistic name

Understanding Session Performance Metrics

Wait Event Analysis

Wait Event Categories

-- Common wait event types:
-- User I/O: db file sequential read, db file scattered read
-- System I/O: log file sync, control file operations
-- Network: SQL*Net message events
-- Concurrency: enqueue waits, latch waits
-- Application: SQL*Net message from client (user think time)

Performance Indicators

-- Key performance metrics:
-- High total waits: Heavy activity or inefficiency
-- High timeout percentages: Resource contention
-- High average waits: Performance bottlenecks
-- Long elapsed times: Long-running sessions

CPU Usage Analysis

CPU Metrics Interpretation

-- CPU statistics meaning:
-- "CPU used by this session": Total CPU consumed
-- "CPU used when call started": CPU for current operation
-- High CPU usage: Compute-intensive operations
-- Low CPU with high waits: I/O or lock bound sessions

Common Use Cases

1. Application Performance Analysis

   • Analyze specific application user performance
   • Identify application-specific bottlenecks
   • Support application tuning efforts
   • Monitor application session behavior

2. Session-Level Troubleshooting

   • Diagnose slow-running sessions
   • Identify resource contention issues
   • Support performance incident resolution
   • Analyze session wait patterns

3. RAC Performance Monitoring

   • Compare session performance across instances
   • Identify instance-specific issues
   • Support RAC load balancing
   • Monitor cluster-wide session behavior

4. Capacity Planning

   • Understand session resource consumption
   • Analyze peak usage patterns
   • Support infrastructure planning
   • Monitor growth trends

Advanced Analysis

Wait Event Pattern Analysis

Application-Specific Patterns

-- Typical application patterns:
-- OLTP: Fast events, low wait times
-- Batch: Higher I/O waits, longer duration
-- Reporting: Scattered reads, longer sessions
-- ETL: Mixed patterns, high resource usage

Performance Bottleneck Identification

-- Bottleneck indicators:
-- Consistently high wait times for specific events
-- High timeout percentages
-- Unbalanced performance across instances
-- CPU vs. wait time imbalances

Session Duration Analysis

Duration Context

-- Session duration interpretation:
-- Short sessions: Quick transactions or connections
-- Medium sessions: Interactive user activity
-- Long sessions: Batch processes or reports
-- Very long sessions: Potential connection leaks

Performance Correlation

-- Duration vs. performance correlation:
-- Resource accumulation over time
-- Memory usage patterns
-- Lock duration impact
-- Transaction scope analysis

Script Customization

Modifying Filter Criteria

User and OS User Filters

-- Customize for your environment:
-- Replace 'boadmtst' with target OS user
-- Replace 'BO_PAL' with target database user
-- Use wildcards for broader analysis
-- Remove filters for system-wide analysis

Session Filtering

-- Session filter options:
-- Specific SID: WHERE s.sid = 123
-- SID range: WHERE s.sid BETWEEN 100 AND 200
-- Pattern match: WHERE s.sid LIKE '12%'
-- All sessions: Remove SID filter entirely

Output Customization

Additional Columns

-- Add useful columns:
-- s.program: Application program name
-- s.machine: Client machine name
-- s.status: Session status
-- s.serial#: Session serial number

Event Filtering

-- Focus on specific events:
-- WHERE e.event LIKE '%file%' -- I/O events only
-- WHERE e.event LIKE '%enq%'  -- Enqueue events only
-- WHERE e.time_waited_micro > 1000000 -- Events > 1 second

Performance Optimization Applications

Bottleneck Resolution

Wait Event Optimization

-- Common optimization strategies:
-- I/O waits: Index optimization, query tuning
-- Lock waits: Transaction design improvements
-- Network waits: Application optimization
-- Latch waits: Concurrency improvements

CPU Optimization

-- CPU optimization approaches:
-- SQL tuning for efficiency
-- Index optimization
-- Application logic improvements
-- Parallel processing consideration

Session Monitoring

Real-Time Monitoring

-- Use for real-time analysis:
-- Run periodically during peak hours
-- Monitor during performance issues
-- Track specific user activities
-- Support incident resolution

Best Practices

Monitoring Strategy

Regular Assessment

-- Monitoring schedule:
-- Real-time during performance issues
-- Regular checks for specific applications
-- Peak hour monitoring
-- Trend analysis over time

Baseline Establishment

-- Performance baselines:
-- Normal wait event patterns
-- Expected CPU consumption levels
-- Typical session durations
-- Application-specific benchmarks

Script Maintenance

Environment Adaptation

-- Adapt script for environment:
-- Update user filter criteria
-- Adjust column formatting
-- Add environment-specific metrics
-- Customize for monitoring tools

Related Scripts

• gvsess.sql - Global session analysis
• vsession.sql - Session information
• vsessev.sql - Session events
• vsesstat.sql - Session statistics

Troubleshooting Applications

High Wait Times

Investigation Steps

-- For sessions with high wait times:
-- Identify specific problematic events
-- Analyze SQL statements being executed
-- Review execution plans
-- Check for blocking sessions

Resolution Strategies

-- Wait time reduction:
-- SQL optimization
-- Index improvements
-- Resource allocation adjustments
-- Application design changes

Performance Regression

Trend Analysis

-- Compare with historical data:
-- Previous session performance
-- Normal wait event patterns
-- CPU usage trends
-- Performance degradation timeline

Summary

This script is essential for:

1. Session Performance Analysis - Detailed analysis of specific session wait events and CPU usage
2. Application Monitoring - Focused monitoring of application-specific database sessions
3. Performance Troubleshooting - Diagnosing session-level performance issues and bottlenecks
4. RAC Analysis - Understanding session performance patterns across cluster instances
5. Capacity Planning - Analyzing session resource consumption patterns for planning purposes