Current Session Wait Statistics (SESSION_WAIT.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 session-specific wait event analysis by:

• Displaying wait statistics for the current user session only
• Showing total waits, cumulative wait time, and maximum wait times
• Focusing on the session executing the script for personalized performance insight
• Converting wait times to user-friendly units (seconds and milliseconds)
• Filtering to show only events that have occurred in the current session

Script

rem  session_wait.sql
rem
set linesize 200
rem
col event for A35
COL twaits for a5
ttitle 'Session Wait Statistics'
rem
select substr(e.event,1,35) event,
--  substr(e.sid,1,5) sid,
  substR(e.total_waits,1,12) twaits,
--  substr(e.total_timeouts,1,12) ttimeouts,
--  substr(100 * e.total_timeouts / e.total_waits,1,12) timeout_pct,
  substr(ROUND(e.time_waited_micro / 1000000),1,8) wait_sec,
  substr(e.MAX_WAIT * 10,1,12) max_wait_ms
--  SUBSTR(e.time_waited_micro / e.total_waits / 1000,1,11) avg_wait_ms
from   v$session_event e,
  v$session s
Where e.sid = s.sid
and s.audsid = userenv('sessionid')
order by e.event, e.sid;

rem
set linesize 80

Usage

SQL> @session_wait.sql

Required Privileges

• SELECT on V$SESSION_EVENT
• SELECT on V$SESSION

Sample Output

Session Wait Statistics

EVENT                               TWAITS   WAIT_SEC MAX_WAIT_MS
----------------------------------- -------- -------- -----------
SQL*Net message from client        456      1234     15000
db file sequential read            234      45       120
db file scattered read             67       23       85
direct path read                   12       8        45
log file switch completion         3        12       4500
undo segment extension             2        1        350

Key Output Columns

• EVENT: Name of the wait event (truncated to 35 characters)
• TWAITS: Total number of waits for this event
• WAIT_SEC: Total time waited in seconds
• MAX_WAIT_MS: Maximum single wait time in milliseconds

Understanding Session Wait Events

Common Wait Events for Interactive Sessions

SQL*Net message from client

• Meaning: Time waiting for client to send next request
• Normal: High values indicate user thinking time or network delays
• Investigation: Usually not a database performance issue

db file sequential read

• Meaning: Single block reads (index lookups, table access by ROWID)
• Performance Target: < 10ms average
• High Values: May indicate I/O bottlenecks or inefficient SQL

db file scattered read

• Meaning: Multi-block reads (full table scans)
• Performance Target: < 20ms average
• High Values: Large table scans or storage performance issues

log file sync

• Meaning: Waiting for redo log writes to complete
• Performance Target: < 5ms average
• High Values: Redo log I/O performance problems

Performance Analysis

Session Performance Assessment

-- Enhanced version with calculations
SELECT event,
       total_waits,
       ROUND(time_waited_micro/1000000, 2) total_wait_secs,
       ROUND(time_waited_micro/total_waits/1000, 2) avg_wait_ms,
       max_wait * 10 max_wait_ms
FROM v$session_event e, v$session s
WHERE e.sid = s.sid
AND s.audsid = userenv('sessionid')
AND total_waits > 0
ORDER BY time_waited_micro DESC;

Wait Time Distribution

-- Show wait time percentages
SELECT event,
       total_waits,
       ROUND(time_waited_micro/1000000, 2) wait_secs,
       ROUND(time_waited_micro * 100 /
             SUM(time_waited_micro) OVER(), 2) wait_pct
FROM v$session_event e, v$session s
WHERE e.sid = s.sid
AND s.audsid = userenv('sessionid')
AND total_waits > 0
ORDER BY time_waited_micro DESC;

Common Use Cases

1. Personal Performance Analysis

   • Understand your session’s resource consumption
   • Identify performance bottlenecks in your work
   • Compare different approaches to same task

2. SQL Development and Tuning

   • Measure impact of SQL changes
   • Identify resource-intensive operations
   • Validate optimization efforts

3. Application Testing

   • Benchmark different code paths
   • Measure transaction response times
   • Identify performance regressions

Session Performance Troubleshooting

High I/O Wait Times

If db file sequential/scattered read times are high:

1. Check SQL Execution Plans: Look for inefficient access paths
2. Review Indexing: Ensure proper indexes exist
3. Analyze Storage: Check I/O subsystem performance

High Log File Sync Times

If log file sync waits are high:

1. Check Commit Frequency: Reduce unnecessary commits
2. Review Redo Configuration: Ensure proper redo log sizing
3. Analyze Storage: Check redo log storage performance

Unusual Wait Events

For unexpected wait events:

1. Research Event Meaning: Understand what the event represents
2. Check Recent Changes: Correlate with recent code or config changes
3. Compare with Baselines: Compare against normal session behavior

Advanced Analysis

Compare with System Averages

-- Compare session waits with system-wide averages
SELECT se.event,
       se.total_waits session_waits,
       ROUND(se.time_waited_micro/se.total_waits/1000, 2) session_avg_ms,
       ROUND(sye.time_waited/sye.total_waits*10, 2) system_avg_ms
FROM v$session_event se, v$system_event sye, v$session s
WHERE se.sid = s.sid
AND s.audsid = userenv('sessionid')
AND se.event = sye.event
AND se.total_waits > 0
ORDER BY se.time_waited_micro DESC;

Session Resource Summary

-- Get complete session resource picture
SELECT 'CPU Time' metric,
       ROUND(value/100, 2) seconds
FROM v$sesstat st, v$statname sn, v$session s
WHERE st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.audsid = userenv('sessionid')
AND sn.name = 'CPU used by this session'

UNION ALL

SELECT 'Total Wait Time' metric,
       ROUND(SUM(time_waited_micro)/1000000, 2) seconds
FROM v$session_event e, v$session s
WHERE e.sid = s.sid
AND s.audsid = userenv('sessionid');

Monitoring Session Changes

Before/After Comparison

-- Save current state for comparison
CREATE GLOBAL TEMPORARY TABLE session_wait_before AS
SELECT event, total_waits, time_waited_micro
FROM v$session_event e, v$session s
WHERE e.sid = s.sid
AND s.audsid = userenv('sessionid');

-- Run your code here...

-- Compare with after state
SELECT a.event,
       (a.total_waits - NVL(b.total_waits, 0)) new_waits,
       ROUND((a.time_waited_micro - NVL(b.time_waited_micro, 0))/1000000, 2) new_wait_secs
FROM v$session_event a, session_wait_before b, v$session s
WHERE a.sid = s.sid
AND s.audsid = userenv('sessionid')
AND a.event = b.event(+)
AND (a.total_waits - NVL(b.total_waits, 0)) > 0
ORDER BY new_wait_secs DESC;

Session Statistics Integration

Complete Session Performance Picture

-- Combine wait events with session statistics
SELECT metric_name, value, units
FROM (
    -- Wait events
    SELECT event metric_name,
           ROUND(time_waited_micro/1000000, 2) value,
           'seconds' units
    FROM v$session_event e, v$session s
    WHERE e.sid = s.sid
    AND s.audsid = userenv('sessionid')
    AND total_waits > 0

    UNION ALL

    -- Key session statistics
    SELECT sn.name metric_name,
           ROUND(st.value/100, 2) value,
           'seconds' units
    FROM v$sesstat st, v$statname sn, v$session s
    WHERE st.statistic# = sn.statistic#
    AND st.sid = s.sid
    AND s.audsid = userenv('sessionid')
    AND sn.name IN ('CPU used by this session',
                    'DB time')
)
ORDER BY value DESC;

Best Practices

1. Baseline Establishment

   • Run script at session start to establish baseline
   • Compare periodic snapshots during long operations
   • Document normal wait patterns for your typical work

2. Performance Testing

   • Clear session statistics before testing: ALTER SESSION SET EVENTS ‘immediate trace name resetstat level 1’
   • Measure specific operations in isolation
   • Compare different approaches quantitatively

3. Regular Monitoring

   • Check during long-running operations
   • Monitor after making SQL or application changes
   • Use for capacity planning personal workload

Integration with SQL Tuning

SQL Performance Correlation

-- Show recent SQL with session waits
SELECT sql_id, executions, buffer_gets, disk_reads
FROM v$sql
WHERE parsing_user_id = (SELECT user_id FROM dba_users
                        WHERE username = USER)
ORDER BY last_active_time DESC;

Related Scripts

• All Session Analysis
• Session Statistics
• SQL by Session