Wait Time Analysis by User (waitid_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 comprehensive wait time analysis by username:

• Combines session wait events with CPU usage
• Filters to application users (containing ‘USR’)
• Excludes SQL*Net communication waits
• Shows total resource consumption per user
• Helps identify resource-intensive users or applications

The 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

-- Basic usage (finds USR pattern users)
@waitid_group.sql

-- Modify the script to change the username pattern
-- Edit: s.username like '%USR%'
-- To:   s.username like '%YOUR_PATTERN%'

Required Privileges

SELECT ON GV$SESSION_EVENT
SELECT ON GV$SESSION
SELECT ON GV$SESSTAT
SELECT ON GV$STATNAME

Sample Output

UNAME                    SUM(TWS)
-------------------- ------------
APP_USR_BATCH              245.67
APP_USR_WEB                123.45
APP_USR_REPORTS             89.23
APP_USR_ETL                 67.89
APP_USR_ADMIN               34.56

Key Output Columns

• UNAME - Username matching the filter pattern
• SUM(TWS) - Total wait time in seconds (includes CPU and wait events)

Understanding the Metrics

Total Wait Time Includes:

• CPU Time - Actual processing time
• I/O Waits - Disk read/write waits
• Lock Waits - Row and table lock contention
• Latch Waits - Internal Oracle structure waits
• Other Waits - Various Oracle wait events

Excludes SQL*Net:

• Network communication time between client and server
• Idle time waiting for user input
• Network transmission delays

Performance Analysis

High Resource Users:

• Users at the top of the list consume most resources
• May indicate inefficient applications or queries
• Could represent legitimate batch processing

Usage Patterns:

• BATCH users - Expected to have high resource usage
• WEB users - Should have moderate, consistent usage
• REPORT users - May have periodic spikes
• ETL users - Expected high usage during load windows

Common Use Cases

Resource Usage Analysis

@waitid_group.sql
-- Identify top resource consumers
-- Plan capacity based on user patterns

Performance Troubleshooting

@waitid_group.sql
-- Find users causing database stress
-- Identify applications needing tuning

Capacity Planning

@waitid_group.sql
-- Understand user resource patterns
-- Plan hardware based on usage

Application Monitoring

@waitid_group.sql
-- Monitor different application tiers
-- Compare resource usage across apps

Customization Options

Change Username Pattern:

-- For different application users
and s.username like '%PROD%'

-- For specific schema pattern
and s.username like 'HR_%'

-- For all non-system users
and s.username not in ('SYS','SYSTEM','DBSNMP')

Add Time Filtering:

-- Add to both queries:
and s.logon_time > sysdate - 1  -- Last 24 hours

Include Additional Details:

-- Add program information
select s.username uname, s.program,
       sum(e.time_waited_micro) / 1000000 tws

Troubleshooting

No Results:

• Verify users match the ‘%USR%’ pattern
• Check if target users are currently connected
• Ensure proper privileges on GV$ views

Unexpected High Values:

• Review specific user sessions
• Check for long-running operations
• Investigate wait event details

Low CPU, High Waits:

• May indicate I/O bottlenecks
• Could show lock contention
• Suggests tuning opportunities

Follow-up Analysis

Drill Down to Specific Users:

SELECT event, sum(time_waited_micro)/1000000 wait_secs
FROM gv$session_event e, gv$session s
WHERE s.sid = e.sid
  AND s.username = 'APP_USR_BATCH'
GROUP BY event
ORDER BY 2 DESC;

Check Current Sessions:

SELECT username, count(*),
       sum(case when status='ACTIVE' then 1 else 0 end) active
FROM gv$session
WHERE username like '%USR%'
GROUP BY username;

Related Scripts

• Session Activity - Current session details
• Wait Events - System-wide wait analysis
• Session I/O - User I/O patterns
• Top SQL by User - SQL analysis by user