Wait Event Names and Parameters (veventn.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.

Wait Event Names and Parameters

This script provides a comprehensive reference of all Oracle wait events and their parameters, essential for understanding and analyzing database performance issues.

Script: veventn.sql

rem  veventn.sql
rem
ttitle 'Event Names'
rem
col event# format 990 heading 'ID'
col name format a35 heading 'NAME'
col parameters format a39 heading 'PARAMETERS'
rem
select event#,
       name,
/*
       parameter1,
       parameter2,
       parameter3,
*/
       parameter1 ||
       decode( parameter2, null, null, ', ' || parameter2 ) ||
       decode( parameter3, null, null, ', ' || parameter3 )
       parameters
  from v$event_name
 order by name;

What This Script Does

This script creates a complete reference listing of Oracle wait events including:

• Event Numbers: Unique identifiers for each wait event
• Event Names: Descriptive names of wait events
• Parameters: Up to three parameters that provide context for each event
• Alphabetical Listing: Events sorted by name for easy lookup

Usage

-- List all wait events
@veventn.sql

-- Find specific event patterns
SELECT event#, name, parameters
FROM v$event_name
WHERE UPPER(name) LIKE '%LOCK%'
ORDER BY name;

Required Privileges

• SELECT privilege on V$EVENT_NAME
• Typically requires DBA role or SELECT_CATALOG_ROLE

Sample Output

                                 Event Names

 ID NAME                                PARAMETERS
--- ----------------------------------- ---------------------------------------
141 ASM Cached Disk DnL                 kfdBlockNumber, kfdMsgNumber, flags
156 ASM Fixed Package DnL               level, kfdMsgNumber, flags
 98 CSS group membership                wait time
 55 Data file init write               file#, blocks, requests
 78 Disk file operations I/O          FileOperation, fileno, filetype
 12 SQL*Net break/reset to client
 45 SQL*Net message from client
 46 SQL*Net message to client         bytes
157 asynch descriptor resize           count, bytes, operation
  5 buffer busy waits                 file#, block#, class#
  8 buffer deadlock                   file#, block#, tries
 23 control file parallel write      files, block#, requests
 24 control file sequential read     file#, block#, blocks
 25 control file single write        file#, block#, blocks
 64 db file parallel read            files, blocks, requests
 65 db file parallel write           files, blocks, requests
 66 db file scattered read           file#, block#, blocks
 67 db file sequential read          file#, block#, blocks
 68 db file single write             file#, block#, blocks

Key Output Columns

Column	Description
ID	Event number - unique identifier for the wait event
NAME	Descriptive name of the wait event
PARAMETERS	Up to 3 parameters that provide additional context

Understanding Wait Event Parameters

Common Parameter Types

File Operations:

• file# - Database file number
• block# - Block number within file
• blocks - Number of blocks involved

I/O Operations:

• requests - Number of I/O requests
• bytes - Size of I/O operation
• files - Number of files involved

Locking:

• id1, id2 - Lock identifiers
• mode - Lock mode requested
• class# - Lock class

Network:

• bytes - Data transmitted
• messages - Message count

Common Use Cases

1. Research Specific Wait Events

-- Find all locking-related events
SELECT event#, name, parameters
FROM v$event_name
WHERE UPPER(name) LIKE '%LOCK%'
   OR UPPER(name) LIKE '%LATCH%'
   OR UPPER(name) LIKE '%ENQUEUE%'
ORDER BY name;

2. I/O Wait Events Reference

-- Find all I/O related events
SELECT event#, name, parameters
FROM v$event_name
WHERE UPPER(name) LIKE '%READ%'
   OR UPPER(name) LIKE '%WRITE%'
   OR UPPER(name) LIKE '%I/O%'
ORDER BY name;

3. Network Wait Events

-- Find network-related events
SELECT event#, name, parameters
FROM v$event_name
WHERE UPPER(name) LIKE '%NET%'
   OR UPPER(name) LIKE '%CLIENT%'
   OR UPPER(name) LIKE '%NETWORK%'
ORDER BY name;

4. Buffer-Related Events

-- Find buffer management events
SELECT event#, name, parameters
FROM v$event_name
WHERE UPPER(name) LIKE '%BUFFER%'
   OR UPPER(name) LIKE '%CACHE%'
ORDER BY name;

5. Create Custom Wait Event Reference

-- Generate formatted wait event documentation
SET PAGESIZE 1000
SET LINESIZE 100
COLUMN event_info FORMAT A80

SELECT event# || ': ' || name ||
       DECODE(parameters, NULL, '', ' (' || parameters || ')') as event_info
FROM v$event_name
ORDER BY event#;

Performance Analysis Integration

Correlate with Active Session History

-- Find which events are actually occurring in your system
SELECT en.name, en.parameters,
       COUNT(*) as occurrence_count,
       ROUND(AVG(ash.time_waited)/1000, 2) as avg_wait_ms
FROM v$active_session_history ash,
     v$event_name en
WHERE ash.event# = en.event#
  AND ash.sample_time >= SYSDATE - 1/24  -- Last hour
GROUP BY en.name, en.parameters
ORDER BY occurrence_count DESC;

Top Wait Events by Category

-- Categorize and count wait events from recent activity
SELECT
  CASE
    WHEN UPPER(en.name) LIKE '%READ%' OR UPPER(en.name) LIKE '%WRITE%' THEN 'I/O'
    WHEN UPPER(en.name) LIKE '%LOCK%' OR UPPER(en.name) LIKE '%ENQUEUE%' THEN 'Locking'
    WHEN UPPER(en.name) LIKE '%NET%' OR UPPER(en.name) LIKE '%CLIENT%' THEN 'Network'
    WHEN UPPER(en.name) LIKE '%BUFFER%' OR UPPER(en.name) LIKE '%CACHE%' THEN 'Buffer'
    WHEN UPPER(en.name) LIKE '%CPU%' THEN 'CPU'
    ELSE 'Other'
  END as event_category,
  COUNT(*) as event_count
FROM v$session_wait sw,
     v$event_name en
WHERE sw.event# = en.event#
  AND sw.state = 'WAITING'
GROUP BY
  CASE
    WHEN UPPER(en.name) LIKE '%READ%' OR UPPER(en.name) LIKE '%WRITE%' THEN 'I/O'
    WHEN UPPER(en.name) LIKE '%LOCK%' OR UPPER(en.name) LIKE '%ENQUEUE%' THEN 'Locking'
    WHEN UPPER(en.name) LIKE '%NET%' OR UPPER(en.name) LIKE '%CLIENT%' THEN 'Network'
    WHEN UPPER(en.name) LIKE '%BUFFER%' OR UPPER(en.name) LIKE '%CACHE%' THEN 'Buffer'
    WHEN UPPER(en.name) LIKE '%CPU%' THEN 'CPU'
    ELSE 'Other'
  END
ORDER BY event_count DESC;

Event Parameter Interpretation

File-Based Events

For events with file#, block#, blocks parameters:

-- Interpret file-based wait events from current sessions
SELECT s.sid, s.username, en.name,
       df.file_name,
       sw.p1 as file_number,
       sw.p2 as block_number,
       sw.p3 as block_count
FROM v$session_wait sw,
     v$event_name en,
     v$session s,
     dba_data_files df
WHERE sw.event# = en.event#
  AND sw.sid = s.sid
  AND sw.p1 = df.file_id (+)
  AND en.parameter1 = 'file#'
  AND sw.state = 'WAITING';

Lock-Based Events

For events with lock identifiers:

-- Interpret lock wait events
SELECT s.sid, s.username, en.name,
       sw.p1 as lock_id1,
       sw.p2 as lock_id2,
       sw.p3 as lock_mode_or_class
FROM v$session_wait sw,
     v$event_name en,
     v$session s
WHERE sw.event# = en.event#
  AND sw.sid = s.sid
  AND (UPPER(en.name) LIKE '%LOCK%' OR UPPER(en.name) LIKE '%ENQUEUE%')
  AND sw.state = 'WAITING';

Oracle Version Considerations

New Events by Version

Different Oracle versions introduce new wait events:

Oracle 12c+:

• PDB-related wait events
• In-Memory column store events
• Enhanced backup/recovery events

Oracle 19c+:

• Autonomous database events
• Machine learning wait events
• Enhanced parallel execution events

Oracle 21c+:

• Blockchain table events
• JSON processing events
• Graph database events

Integration with Other Scripts

This script works well with:

• Active Session History (gvsessactive.sql) - For current wait analysis
• Session Wait Analysis (gvsessw.sql) - For session wait patterns
• Wait Statistics (vwait.sql) - For system-wide wait analysis
• Latch Analysis (vlatch.sql) - For latch-specific waits

Create Reference Documentation

Generate Wait Event Handbook

-- Create comprehensive wait event reference
SET PAGESIZE 0
SET LINESIZE 200
SPOOL wait_events_reference.txt

SELECT 'Event #' || event# || ': ' || name || chr(10) ||
       'Parameters: ' || NVL(parameters, 'None') || chr(10) ||
       '----------------------------------------' || chr(10)
FROM v$event_name
ORDER BY event#;

SPOOL OFF

Create Category-Based Reference

-- Organize events by category
SELECT
  CASE
    WHEN UPPER(name) LIKE '%READ%' OR UPPER(name) LIKE '%WRITE%' THEN 'I/O EVENTS'
    WHEN UPPER(name) LIKE '%LOCK%' OR UPPER(name) LIKE '%ENQUEUE%' THEN 'LOCKING EVENTS'
    WHEN UPPER(name) LIKE '%NET%' OR UPPER(name) LIKE '%CLIENT%' THEN 'NETWORK EVENTS'
    WHEN UPPER(name) LIKE '%BUFFER%' OR UPPER(name) LIKE '%CACHE%' THEN 'BUFFER EVENTS'
    ELSE 'OTHER EVENTS'
  END as category,
  event#,
  name,
  parameters
FROM v$event_name
ORDER BY 1, 2;

Best Practices

1. Reference Tool: Use this as a quick reference when analyzing wait events
2. Combine with Monitoring: Use alongside active wait monitoring scripts
3. Parameter Understanding: Learn common parameter patterns for faster diagnosis
4. Version Awareness: Note that event lists vary by Oracle version
5. Documentation: Keep customized wait event references for your environment

This script serves as an essential reference tool for Oracle performance tuning and wait event analysis, providing the foundation for understanding what Oracle is waiting for during database operations.