Skip to content
Oracle DBA Scripts & Database Utilities

Session Status Summary with Wait Events (jobstat1.sql)

What This Script Does

Section titled “What This Script Does”

This script provides a high-level view of database activity by:

  • Grouping sessions by their current status
  • Showing wait events for each status group
  • Separating background processes from user sessions
  • Providing session counts for each status/event combination

Script

Section titled “Script”
rem  jobstat1.sql
rem
ttitle 'Session Status Summary'
rem
col status format a10 heading 'STATUS'
col event format a32 heading 'EVENT'
col session_count format 9999 heading 'SESSION|COUNT'
rem
break on report on status skip 1
compute sum of session_count on report status
rem
select decode( type, 'BACKGROUND', 'BACKGROUND', status ) status,
       event,
       count(*) session_count
  from v$session_wait w,
       v$session s
 where s.sid = w.sid(+)
 group by decode( type, 'BACKGROUND', 'BACKGROUND', status ),
          event;

Usage

Section titled “Usage”
SQL> @jobstat1.sql

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$SESSION_WAIT
  • SELECT on V$SESSION

Sample Output

Section titled “Sample Output”
Session Status Summary


                                                 SESSION
STATUS     EVENT                            COUNT
---------- -------------------------------- -------
ACTIVE     db file sequential read              12
           db file scattered read                8
           log file sync                         3
           SQL*Net message from client           5
                                            -------
sum                                             28


INACTIVE   SQL*Net message from client         145
           PL/SQL lock timer                     2
                                            -------
sum                                            147


BACKGROUND pmon timer                            1
           LGWR wait for redo copy               1
           db file parallel write                1
           DIAG idle wait                        3
                                            -------
sum                                              6

Key Output Columns

Section titled “Key Output Columns”
  • STATUS: Session status (ACTIVE, INACTIVE, BACKGROUND)
  • EVENT: Current wait event for the session
  • SESSION COUNT: Number of sessions in this state

Understanding Session States

Section titled “Understanding Session States”

Status Types

Section titled “Status Types”
  1. ACTIVE: Currently executing SQL or PL/SQL
  2. INACTIVE: Connected but idle
  3. BACKGROUND: Oracle background processes

Common Wait Events

Section titled “Common Wait Events”
  • SQL*Net message from client: Idle, waiting for client
  • db file sequential read: Single block I/O (index reads)
  • db file scattered read: Multi-block I/O (full scans)
  • log file sync: Waiting for redo write on commit

Common Use Cases

Section titled “Common Use Cases”

  1. Database Activity Overview

    • Quick health check
    • Identify busy periods
    • Spot unusual patterns

  2. Performance Monitoring

    • Track active session counts
    • Identify prevalent wait events
    • Monitor background process activity

  3. Capacity Planning

    • Understand session patterns
    • Plan for connection pools
    • Size system resources

Interpreting Results

Section titled “Interpreting Results”

Healthy Patterns

Section titled “Healthy Patterns”
  • Majority of sessions INACTIVE
  • Few blocking wait events
  • Consistent background process count

Warning Signs

Section titled “Warning Signs”
  • High ACTIVE session count
  • Many sessions in lock waits
  • Unusual background process waits

Critical Issues

Section titled “Critical Issues”
  • All sessions in wait states
  • System-wide blocking events
  • Missing background processes

Extended Analysis

Section titled “Extended Analysis”

For more detailed session information:

-- Include username and program
SELECT decode(type, 'BACKGROUND', 'BACKGROUND', status) status,
       username,
       program,
       event,
       count(*) session_count
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid(+)
GROUP BY decode(type, 'BACKGROUND', 'BACKGROUND', status),
         username, program, event;

For wait time analysis:

-- Include wait times
SELECT status, event,
       count(*) sessions,
       round(avg(wait_time)) avg_wait_ms
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid(+)
  AND type != 'BACKGROUND'
GROUP BY status, event
ORDER BY status, count(*) desc;

Performance Impact

Section titled “Performance Impact”
  • Lightweight query with minimal overhead
  • Safe to run frequently
  • Suitable for monitoring dashboards
Section titled “Related Scripts”