Session Wait Time Analysis by User (wait_group.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 user-specific performance analysis by:

• Aggregating wait events for all sessions of a specific user
• Combining data from all RAC instances (GV$ views)
• Including CPU usage statistics alongside wait events
• Excluding SQL*Net events to focus on database performance issues
• Ordering results by total time to highlight biggest impacts
• Converting microseconds to seconds for easier interpretation

Script

SELECT * FROM (
select e.event,
   sum(e.time_waited_micro) / 1000000 time_waited_secs
  from gv$session_event e, gv$session s
where s.sid -0 = e.sid
   and s.inst_id = e.inst_id
    and s.username = '&userid'
and e.event not like '%SQL*Net%'
group by e.event
--order by 2 desc
UNION ALL
select N.NAME, SUM(s.value) / 100
  from gv$sesstat s, gv$statname n, gv$session ss
 where n.statistic# = s.statistic#
   and s.value <> 0
   and s.sid = ss.sid
   and s.inst_id = ss.inst_id
   and ss.username = '&userid'
   and N.NAME = 'CPU used by this session'
GROUP BY N.NAME
)
ORDER BY 2 desc
/

Usage

SQL> @wait_group.sql
Enter value for userid: APPUSER

Parameters

• userid: Database username to analyze (case-sensitive)

Required Privileges

• SELECT on GV$SESSION_EVENT
• SELECT on GV$SESSION
• SELECT on GV$SESSTAT
• SELECT on GV$STATNAME

Sample Output

EVENT                                    TIME_WAITED_SECS
---------------------------------------- ----------------
CPU used by this session                         234.56
db file sequential read                           89.23
log file sync                                     45.67
db file scattered read                            23.45
direct path read                                  12.34
buffer busy waits                                  8.91
latch: shared pool                                 4.56
enq: TX - row lock contention                     2.34
gc buffer busy acquire                             1.23
gc current block 2-way                             0.89

Key Output Columns

• EVENT: Wait event name or statistic name
• TIME_WAITED_SECS: Total time in seconds spent on this event/activity

Understanding the Results

CPU vs Wait Time Analysis

• CPU used by this session: Actual processing time
• Wait events: Time spent waiting for resources

Common Wait Events

• db file sequential read: Single block I/O (index reads)
• db file scattered read: Multi-block I/O (full scans)
• log file sync: Commit wait time
• buffer busy waits: Block contention
• enq: TX - row lock contention: Row-level locking waits

RAC-Specific Events

• gc%: Global cache coordination waits
• gcs%: Global cache service waits
• ges%: Global enqueue service waits

Performance Analysis Techniques

CPU vs I/O Ratio

-- Calculate workload characteristics
SELECT
    CASE
        WHEN cpu_time > io_time THEN 'CPU Bound'
        WHEN io_time > cpu_time * 2 THEN 'I/O Bound'
        ELSE 'Balanced'
    END workload_type,
    ROUND(cpu_time, 2) cpu_seconds,
    ROUND(io_time, 2) io_seconds,
    ROUND(cpu_time * 100 / (cpu_time + io_time), 1) cpu_pct
FROM (
    SELECT
        NVL((SELECT SUM(value)/100 FROM gv$sesstat s, gv$statname n, gv$session ss
             WHERE n.statistic# = s.statistic#
             AND s.sid = ss.sid AND s.inst_id = ss.inst_id
             AND ss.username = '&userid'
             AND n.name = 'CPU used by this session'), 0) cpu_time,
        NVL((SELECT SUM(time_waited_micro)/1000000 FROM gv$session_event e, gv$session s
             WHERE s.sid = e.sid AND s.inst_id = e.inst_id
             AND s.username = '&userid'
             AND e.event IN ('db file sequential read', 'db file scattered read',
                           'direct path read', 'direct path write')), 0) io_time
    FROM dual
);

Top Consumer Identification

The script automatically orders by time waited, making it easy to identify:

1. Primary bottleneck: First result (highest time)
2. Secondary issues: Results 2-5
3. Minor contributors: Lower values

User-Specific Analysis Scenarios

Application User Monitoring

-- Monitor specific application user
Enter value for userid: SALES_APP

Batch Job Analysis

-- Analyze batch processing user
Enter value for userid: BATCH_USER

Connection Pool Monitoring

-- Check connection pool performance
Enter value for userid: POOL_USER

RAC Environment Considerations

Cross-Instance Aggregation

The script uses GV$ views to aggregate across all instances:

• Provides complete picture for user activity
• Shows RAC-specific wait events
• Helps identify instance affinity issues

Instance Distribution Analysis

-- See activity by instance
SELECT s.inst_id,
       COUNT(*) session_count,
       SUM(e.time_waited_micro)/1000000 total_wait_secs
FROM gv$session_event e, gv$session s
WHERE s.sid = e.sid
AND s.inst_id = e.inst_id
AND s.username = '&userid'
GROUP BY s.inst_id
ORDER BY s.inst_id;

Filtering SQL*Net Events

Why Exclude SQL*Net

• SQL*Net message from client: User think time
• SQL*Net message to client: Network latency
• SQL*Net more data: Large result sets

These events don’t indicate database performance issues.

Manual SQL*Net Analysis

-- Analyze SQL*Net events separately
SELECT e.event,
       SUM(e.time_waited_micro)/1000000 time_waited_secs,
       SUM(e.total_waits) total_waits,
       ROUND(SUM(e.time_waited_micro)/SUM(e.total_waits)/1000, 2) avg_wait_ms
FROM gv$session_event e, gv$session s
WHERE s.sid = e.sid
AND s.inst_id = e.inst_id
AND s.username = '&userid'
AND e.event LIKE '%SQL*Net%'
GROUP BY e.event
ORDER BY 2 DESC;

Common Use Cases

1. Application Performance Tuning

   • Identify user-specific bottlenecks
   • Compare different application users
   • Validate tuning efforts

2. Capacity Planning

   • Understand user resource consumption
   • Plan for user growth
   • Optimize resource allocation

3. Problem Diagnosis

   • Isolate issues to specific users
   • Correlate performance with application behavior
   • Support user complaints with data

Trend Analysis

Before/After Comparisons

1. Run script before changes
2. Implement tuning or application changes
3. Re-run script to measure improvement

Historical Comparison

-- Create snapshot table
CREATE TABLE user_wait_history (
    capture_date DATE,
    username VARCHAR2(128),
    event VARCHAR2(64),
    time_waited_secs NUMBER
);

-- Insert current snapshot
INSERT INTO user_wait_history
SELECT SYSDATE, '&userid', event, time_waited_secs
FROM (...script results...);

Troubleshooting Patterns

High CPU Usage

• Symptoms: CPU used by this session at top
• Investigation: Check for inefficient SQL
• Actions: Review execution plans, tune queries

I/O Bound Operations

• Symptoms: db file reads dominate
• Investigation: Check buffer cache hit ratio
• Actions: Add indexes, tune SQL, increase SGA

Lock Contention

• Symptoms: enq: TX waits appear
• Investigation: Find blocking sessions
• Actions: Optimize application locking

RAC Coordination Issues

• Symptoms: gc% events in top results
• Investigation: Check interconnect performance
• Actions: Optimize cluster configuration

Integration with Other Tools

AWR Integration

Use this script to:

1. Identify problem users during AWR collection
2. Focus AWR reports on specific time periods
3. Validate AWR findings at user level

ASH Integration

-- Find active sessions for this user
SELECT * FROM gv$active_session_history
WHERE user_id = (SELECT user_id FROM dba_users WHERE username = '&userid')
AND sample_time > SYSDATE - 1/24;  -- Last hour

Best Practices

1. Regular Monitoring

   • Check key application users daily
   • Establish baseline metrics
   • Alert on unusual patterns

2. Comprehensive Analysis

   • Don’t focus only on top wait event
   • Consider cumulative impact
   • Look for patterns across users

3. Action Planning

   • Address highest time consumers first
   • Consider business impact
   • Plan changes during low-usage periods

Limitations

1. Point-in-Time: Shows cumulative data since session start
2. Active Sessions Only: Only includes currently connected sessions
3. No History: Doesn’t show historical trends

Enhanced Versions

Include Session Details

-- Add session information
SELECT s.inst_id, s.sid, s.serial#, s.program,
       e.event, SUM(e.time_waited_micro)/1000000 time_waited_secs
FROM gv$session_event e, gv$session s
WHERE s.sid = e.sid AND s.inst_id = e.inst_id
AND s.username = '&userid'
AND e.event NOT LIKE '%SQL*Net%'
GROUP BY s.inst_id, s.sid, s.serial#, s.program, e.event
ORDER BY time_waited_secs DESC;

Related Scripts

• Global Session Analysis
• Session I/O Statistics
• User Session Statistics
• System Wait Events