Skip to content
Oracle DBA Scripts & Database Utilities

Current Session Waits Excluding Idle Events (gvsessw.sql)

What This Script Does

Section titled “What This Script Does”

This script provides Oracle database functionality via the gvsessw.sql script.

The Script

Section titled “The Script”
rem  gvsessw.sql
rem
set linesize 146
rem
ttitle 'Session Waits'
rem
col event format a30 heading 'EVENT'
col sid format 9999 heading 'SID'
col seq# format 99999 heading 'SEQ'
col wait_time format b999 heading 'WAIT|TIME'
col seconds_in_wait format b999999 heading 'SECONDS|IN WAIT'
col state format a17 heading 'STATE'
col parm1 format a20 heading 'PARM1'
col parm2 format a20 heading 'PARM2'
col parm3 format a20 heading 'PARM3'
col parameters format a65 heading 'PARAMETERS'
col inst_id format 999 heading 'INST'
rem
break on event on inst_id
rem
select event, inst_id,sid, seq#,
       wait_time,
       seconds_in_wait,
/*
       state,
       p1text, p1, p1raw,
       p2text, p2, p2raw,
       p3text, p3, p3raw
       p1text || ' = ' || p1 parm1,
       p2text || ' = ' || p2 parm2,
       p3text || ' = ' || p3 parm3
*/
       decode( p1text, null, null,
               p1text || ' = ' || p1 ) ||
       decode( p2text, null, null,
               ', ' || p2text || ' = ' || p2 ) ||
       decode( p3text, null, null,
               ', ' || p3text || ' = ' || p3 )
       parameters
  from gv$session_wait
 where event not in ( 'pmon timer',
                      'rdbms ipc message',
                      'wakeup time manager',
                      'smon timer',
                      'WMON goes to sleep',
                      'pipe get',
                      'Null event',
                      'SQL*Net message from client' ,
                      'SQL*Net message to client',
                      'virtual circuit status',
                      'Queue Monitor Wait',
                      'queue messages',
                      'jobq slave wait',
'Streams AQ: qmn coordinator idle wait',
'Streams AQ: qmn slave idle wait',
'Streams AQ: waiting for messages in the queue',
'Streams AQ: waiting for time management or cleanup tasks',
'ASM background timer',
'class slave wait')
 order by event, inst_id,p1, p2;
rem

What This Script Does

Section titled “What This Script Does”

This script provides a focused view of current session wait events across all RAC instances, specifically excluding idle and background process events. It shows sessions that are experiencing performance-impacting waits, along with detailed wait parameters that help diagnose the root cause of performance issues.

Key Features

Section titled “Key Features”
  • Active Wait Focus: Excludes idle events to show only performance-impacting waits
  • RAC-Aware: Monitors waits across all cluster instances
  • Parameter Details: Shows wait event parameters for detailed diagnosis
  • Real-Time Snapshot: Current wait state of all sessions
  • Organized Output: Grouped by event type and instance for easy analysis

Usage

Section titled “Usage”
@gvsessw.sql

No parameters required - shows all active waits across all instances.

Required Privileges

Section titled “Required Privileges”
SELECT on GV$SESSION_WAIT

Sample Output

Section titled “Sample Output”
                                          Session Waits


EVENT                          INST  SID   SEQ WAIT SECONDS PARAMETERS
                                                  TIME IN WAIT
------------------------------ ---- ---- ----- ---- ------- ------------------------------------------
buffer busy waits                 1  123   456    0      12 file# = 4, block# = 125847, class# = 1
                                  2  156   789    0       8 file# = 4, block# = 125847, class# = 1


db file scattered read            1  234  1234    0       2 file# = 6, block# = 458921, blocks = 8
                                  1  567  2345    0       1 file# = 8, block# = 789123, blocks = 16


db file sequential read           1  345  3456    0       5 file# = 2, block# = 234567, blocks = 1
                                  2  678  4567    0       3 file# = 2, block# = 456789, blocks = 1
                                  2  789  5678    0       1 file# = 5, block# = 123456, blocks = 1


enq: TX - row lock contention    1  456  6789   10      45 name|mode = 1415053318, usn<<16 | slot = 196611, sequence = 1234
                                  2  890  7890   15      67 name|mode = 1415053318, usn<<16 | slot = 327682, sequence = 5678


log file sync                     1  901  8901    0       1 buffer# = 12345678
                                  1  123  9012    0       0 buffer# = 12345679

Key Output Columns

Section titled “Key Output Columns”
  • EVENT: Name of the wait event
  • INST: RAC instance number
  • SID: Session identifier
  • SEQ: Sequence number for the wait
  • WAIT TIME: Time waited (0 = currently waiting)
  • SECONDS IN WAIT: How long session has been waiting
  • PARAMETERS: Event-specific parameters with values

Understanding Wait Events

Section titled “Understanding Wait Events”
Section titled “I/O Related Waits”

db file sequential read

Section titled “db file sequential read”
  • file#: Datafile number
  • block#: Block number being read
  • blocks: Number of blocks (usually 1)
  • Analysis: Single-block I/O, often index reads

db file scattered read

Section titled “db file scattered read”
  • file#: Datafile number
  • block#: Starting block number
  • blocks: Number of blocks being read
  • Analysis: Multi-block I/O, often full table scans

Concurrency Waits

Section titled “Concurrency Waits”

buffer busy waits

Section titled “buffer busy waits”
  • file#: Datafile number
  • block#: Block number causing contention
  • class#: Block class (1=data, 4=segment header, etc.)
  • Analysis: Multiple sessions accessing same block

enq: TX - row lock contention

Section titled “enq: TX - row lock contention”
  • name|mode: Lock name and mode
  • usn<<16 | slot: Undo segment and slot
  • sequence: Sequence number
  • Analysis: Row-level locking conflict

System Waits

Section titled “System Waits”

log file sync

Section titled “log file sync”
  • buffer#: Log buffer number
  • Analysis: Waiting for commit to complete

Performance Analysis

Section titled “Performance Analysis”

Identifying Bottlenecks

Section titled “Identifying Bottlenecks”

  1. High SECONDS IN WAIT Values

    • Sessions stuck for extended periods
    • May indicate blocking or resource contention

  2. Multiple Sessions on Same Event

    • System-wide performance issues
    • Resource bottlenecks affecting multiple users

  3. Repeated Block Numbers

    • Hot blocks causing contention
    • May need application or schema design changes

Wait Event Patterns

Section titled “Wait Event Patterns”

Storage Performance Issues

Section titled “Storage Performance Issues”
-- Look for:
-- High db file sequential read times (>5ms typically)
-- Many db file scattered read events
-- Consistent I/O waits across multiple sessions

Concurrency Problems

Section titled “Concurrency Problems”
-- Look for:
-- enq: TX - row lock contention (blocking)
-- buffer busy waits on same blocks
-- Multiple sessions waiting for same resources

System Resource Issues

Section titled “System Resource Issues”
-- Look for:
-- log file sync delays (storage or archiving issues)
-- latch waits (memory contention)
-- Network-related waits

Common Use Cases

Section titled “Common Use Cases”

  1. Real-Time Performance Troubleshooting

    • Identify currently occurring performance issues
    • Find sessions experiencing waits right now
    • Diagnose blocking situations

  2. Bottleneck Identification

    • Spot system-wide performance issues
    • Identify hot blocks or resources
    • Find resource contention points

  3. Capacity Planning

    • Understand wait patterns during peak periods
    • Identify resource limitations
    • Plan for infrastructure improvements

  4. Application Tuning

    • Find application-specific wait patterns
    • Identify inefficient SQL operations
    • Optimize application design

Advanced Analysis

Section titled “Advanced Analysis”

Finding Blocking Sessions

Section titled “Finding Blocking Sessions”
-- After identifying TX row lock contention, find blockers:
SELECT blocking_session, sid, serial#, username, sql_id
FROM gv$session
WHERE sid IN (SELECT DISTINCT sid FROM gv$session_wait
              WHERE event = 'enq: TX - row lock contention');

Hot Block Analysis

Section titled “Hot Block Analysis”
-- Identify frequently waited-for blocks:
SELECT file#, block#, COUNT(*) wait_count
FROM gv$session_wait
WHERE event IN ('buffer busy waits', 'db file sequential read')
GROUP BY file#, block#
ORDER BY wait_count DESC;

Wait Time Analysis

Section titled “Wait Time Analysis”
-- Find sessions with longest waits:
ORDER BY seconds_in_wait DESC;

Troubleshooting

Section titled “Troubleshooting”

No Results

  • Good news - no sessions are experiencing significant waits
  • All waits may be idle events (filtered out)
  • Database may be lightly loaded

Too Many Results

  • High database activity or performance issues
  • Consider focusing on specific wait events
  • Look for patterns in event types

High Seconds in Wait

  • Sessions may be blocked
  • Check for locking issues
  • Investigate resource bottlenecks

Script Customization

Section titled “Script Customization”

Focus on Specific Wait Classes

Section titled “Focus on Specific Wait Classes”
-- Add WHERE clause for specific events:
and event like 'db file%'          -- I/O events only
and event like 'enq:%'             -- Locking events only
and event like 'latch%'            -- Latch contention only

Add Session Details

Section titled “Add Session Details”
-- Join with gv$session for more context:
SELECT sw.event, sw.inst_id, sw.sid, s.username, s.program,
       sw.seconds_in_wait, sw.wait_time
FROM gv$session_wait sw, gv$session s
WHERE sw.sid = s.sid AND sw.inst_id = s.inst_id
AND sw.event NOT IN (...)
Section titled “Related Scripts”