User Wait Time Analysis (waitID_group.sql)

What This Script Does

This script provides comprehensive user performance analysis by:

Combining wait event times and CPU usage for complete user resource consumption
Filtering to application users (those containing ‘USR’ in username)
Excluding SQL*Net events to focus on database-internal waits
Aggregating across all RAC instances for cluster-wide user analysis
Ranking users by total resource consumption (wait time + CPU time)

Script

SELECT uname,sum(tws) FROM (
select s.username uname,
   sum(e.time_waited_micro) / 1000000 tws
  from gv$session_event e, gv$session s
where s.sid -0 = e.sid
    and s.username like '%USR%'
    and s.inst_id = e.inst_id
and e.event not like '%SQL*Net%'
group by s.username
UNION ALL
select ss.username uname, SUM(s.value) / 100 tws
  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 like '%USR%'
   and N.NAME = 'CPU used by this session'
GROUP BY ss.username
)
group by uname
ORDER BY 2 desc
/

Usage

SQL> @waitID_group.sql

Required Privileges

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

Sample Output

UNAME                    SUM(TWS)
-------------------- ------------
SALES_USR               12,345.67
REPORTS_USR              8,901.23
BATCH_USR                6,789.45
WEBAPP_USR               4,567.89
ETL_USR                  3,456.78
ANALYTICS_USR            2,345.67
MOBILE_USR               1,234.56

Key Output Columns

UNAME: Username containing ‘USR’ pattern
SUM(TWS): Total time in seconds (wait time + CPU time) for the user across all sessions and instances

Understanding the Metrics

Total Time Calculation

The script combines two key components:

Wait Time: Time spent waiting for database events (I/O, locks, etc.)
CPU Time: Actual CPU processing time used by sessions

Why Both Metrics Matter

Wait Time Only: Shows database bottlenecks but misses CPU-intensive operations
CPU Time Only: Shows processing load but misses I/O and contention issues
Combined: Provides complete picture of user resource consumption

Common Use Cases

Resource Usage Analysis
- Identify users consuming most database resources
- Plan capacity based on user workload patterns
- Charge-back analysis for multi-tenant environments
Performance Troubleshooting
- Find users experiencing performance issues
- Prioritize optimization efforts by impact
- Identify application users vs system overhead
Workload Characterization
- Understand user behavior patterns
- Plan maintenance windows around user activity
- Size infrastructure for peak user loads

Performance Analysis

Baseline Expectations

Low Resource Users: < 100 seconds total time
Moderate Users: 100-1000 seconds total time
Heavy Users: > 1000 seconds total time
Critical Users: > 3600 seconds (1 hour) total time

Workload Classification

Users can be classified by their resource consumption patterns:

CPU-Intensive: High CPU time relative to wait time
I/O-Intensive: High wait time from I/O events
Mixed Workload: Balanced CPU and wait time

Advanced Analysis

User Performance Details

-- Get detailed breakdown for specific user
SELECT s.username, e.event,
       COUNT(*) sessions,
       SUM(e.total_waits) total_waits,
       SUM(e.time_waited_micro)/1000000 total_wait_secs,
       ROUND(AVG(e.time_waited_micro/GREATEST(e.total_waits,1))/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 = 'SALES_USR'
AND e.time_waited_micro > 0
GROUP BY s.username, e.event
ORDER BY total_wait_secs DESC;

Session Distribution Analysis

-- Check session distribution across instances
SELECT inst_id, username, COUNT(*) session_count
FROM gv$session
WHERE username LIKE '%USR%'
AND status = 'ACTIVE'
GROUP BY inst_id, username
ORDER BY inst_id, session_count DESC;

Monitoring and Optimization

Resource Threshold Monitoring

Set up alerts for users exceeding normal resource consumption:

Warning: Users consuming > 30 minutes total time
Critical: Users consuming > 1 hour total time

Performance Tuning Actions

High CPU Users: Review SQL efficiency, consider better algorithms
High Wait Users: Investigate I/O bottlenecks, locking issues
Growing Usage: Plan for capacity expansion

Customization Options

Username Pattern Modification

-- Modify for different user patterns
AND s.username LIKE 'APP_%'     -- Application users
AND s.username NOT LIKE 'SYS%'  -- Exclude system users
AND s.username IN ('USER1', 'USER2', 'USER3')  -- Specific users

Event Filtering

-- Include/exclude specific events
AND e.event NOT LIKE '%idle%'             -- Exclude idle events
AND e.event IN ('db file sequential read', 'db file scattered read')  -- I/O only

Integration with Application Monitoring

Time-Based Analysis

Track user activity patterns by time of day to:

Identify peak usage periods
Plan maintenance windows
Size infrastructure for demand

Application Module Correlation

Combine with V$SESSION.MODULE to understand:

Which applications drive resource usage
Performance patterns by application component
Connection pool efficiency

Best Practices

Regular Monitoring
- Run during different time periods
- Compare business hours vs. off-hours
- Track trends over time
Threshold Management
- Establish user resource baselines
- Set appropriate alert thresholds
- Account for business cycle variations
Action Planning
- Prioritize optimization by total impact
- Focus on users with growing resource usage
- Plan capacity based on user growth patterns