High Wait Time Analysis (high_wait_times.sql)

What This Script Does

Provides comprehensive analysis of high wait times in Oracle sessions, including blocking session identification, wait event analysis, and SQL statement investigation. This script contains multiple queries to diagnose performance bottlenecks and waiting patterns.

The Script

rem  high_wait_times.sql
rem
ttitle 'High Wait Time Analysis'
rem
set linesize 180
rem
-- Get all session stats of sessions waiting on the database
select sid, username, event, blocking_session,
   seconds_in_wait, wait_time
from v$session where state in ('WAITING')
and wait_class != 'Idle'
/
rem
-- Get the SQL from the blocker and blockee from above query
select sid, sql_text
from v$session s, v$sql q
where sid in (254,270)
and (
   q.sql_id = s.sql_id or
   q.sql_id = s.prev_sql_id)
/
rem
-- Query the groupings of wait class / session
select wait_class_id, wait_class,
   total_waits, time_waited
from v$session_wait_class
where sid = 270
/
rem
-- Check specific wait times for each wait event
select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and wait_class_id = 4217450380
/
rem
-- Shows how many wait events in ms(bucket) waited
select event, wait_time_milli bucket, wait_count
 from v$event_histogram
 where event =
    'enq: TX - row lock contention'
/

Usage

-- Run the complete analysis
@high_wait_times.sql

-- Note: You may need to modify SID values in the second query
-- based on results from the first query

Parameters

The script contains hardcoded values that may need modification:

SID values (254,270): Update based on first query results
SID value (270): Update for specific session analysis
wait_class_id (4217450380): Modify for different wait classes
Event name: Change ‘enq: TX - row lock contention’ as needed

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT on V$SESSION
SELECT on V$SQL
SELECT on V$SESSION_WAIT_CLASS
SELECT on V$SYSTEM_EVENT
SELECT on V$EVENT_NAME
SELECT on V$EVENT_HISTOGRAM

Sample Output

Query 1: Waiting Sessions

   SID USERNAME             EVENT                          BLOCKING_SESSION SECONDS_IN_WAIT  WAIT_TIME
------ -------------------- ------------------------------ ---------------- --------------- ----------
   254 SCOTT               enq: TX - row lock contention               270             120          0
   289 HR_USER             db file sequential read                       0              15          0
   312 SALES_USER          log file sync                                 0               5          0

Query 2: SQL Text from Blocking Sessions

   SID SQL_TEXT
------ --------------------------------------------------------------------------------
   254 UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10
   270 UPDATE employees SET department_id = 20 WHERE employee_id = 1001

Query 3: Wait Class Summary

WAIT_CLASS_ID WAIT_CLASS           TOTAL_WAITS TIME_WAITED
------------- -------------------- ----------- -----------
   1893977003 System I/O                  1245      125000
   4217450380 Application                  567       89000
   3875070507 Concurrency                  234       45000

Query 4: System Event Details

EVENT                          TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
enq: TX - row lock contention         234       45000
enq: TM - DML lock                     89       12000
buffer busy waits                     156       23000

Query 5: Wait Event Histogram

EVENT                          BUCKET WAIT_COUNT
------------------------------ ------ ----------
enq: TX - row lock contention      1         145
enq: TX - row lock contention      2          89
enq: TX - row lock contention      4          67
enq: TX - row lock contention      8          45

Key Analysis Components

1. Waiting Sessions Query

Identifies sessions currently waiting
Shows blocking relationships
Excludes idle waits for focused analysis

2. SQL Text Analysis

Retrieves SQL causing blocks
Shows both current and previous SQL
Helps identify root cause statements

3. Wait Class Grouping

Categorizes waits by class
Shows total waits and time waited
Provides high-level wait distribution

4. System Event Analysis

Details specific wait events
Shows system-wide wait statistics
Helps identify global bottlenecks

5. Wait Event Histogram

Shows wait time distribution
Buckets waits by millisecond ranges
Reveals wait time patterns

Common Wait Events

Concurrency Waits

enq: TX - row lock contention
enq: TM - DML lock
buffer busy waits

I/O Related Waits

db file sequential read
db file scattered read
direct path read/write

System Waits

log file sync
log file parallel write
checkpoint completed

Common Use Cases

Blocking Session Analysis

-- Step 1: Run first query to identify blockers
@high_wait_times.sql
-- Step 2: Update SID values in script
-- Step 3: Re-run to get SQL details

Performance Bottleneck Investigation

-- Analyze wait patterns and events
@high_wait_times.sql
-- Focus on highest time_waited values

Lock Contention Analysis

-- Identify row-level locking issues
@high_wait_times.sql
-- Look for enq: TX events and blocking sessions

System-Wide Wait Analysis

-- Review overall wait distribution
@high_wait_times.sql
-- Analyze wait class summaries

Customization Tips

Modify SID Values

-- Update based on first query results
where sid in (your_sid_1, your_sid_2)

Change Wait Class Focus

-- Use different wait_class_id values
-- Common IDs: Application, Concurrency, System I/O

Filter Specific Events

-- Modify event name in histogram query
where event = 'your_specific_event'

Performance Impact

Low Impact: Reads system views only
Snapshot Analysis: Provides point-in-time view
Diagnostic Focus: Targets specific performance issues

Troubleshooting Workflow

Run initial query to identify waiting sessions
Note blocking sessions and their SIDs
Update script with relevant SID values
Analyze SQL text to understand root causes
Review wait patterns for systematic issues
Take corrective action based on findings

gvlock - Lock analysis
gvsess - Active session analysis
sqltext - SQL text retrieval
gvtrans - Transaction analysis