Global Session Events Analysis (SESS.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 RAC session analysis by:

• Displaying wait events for sessions across all instances
• Calculating session elapsed time since login
• Showing timeout percentages and average wait times
• Including CPU usage statistics for complete performance picture
• Supporting flexible filtering by session ID and username
• Providing detailed timeout and wait time analysis

Script

rem  gvsessev.sql
rem
ttitle 'Global Session Events'
rem
set linesize 132
rem
col inst_id format 999 heading 'INST'
col CPU format 99,999,999.90
col s_sid format 9999 heading 'SESS|SID'
col e_sid format 9999 heading 'EVNT|SID'
col username format a10 heading 'USERNAME'
col elapsed_secs format 9999999 heading 'ELAPSED|SECS'
col event format a28 heading 'EVENT'
col total_waits format 99999999 heading 'TOTAL|WAITS'
col total_timeouts format b9999999 heading 'TOTAL|TIMEOUTS'
col time_waited_secs format b9999999.00 heading 'TIME|WAITED|(secs)'
col timeout_pct format b999.9 heading 'TIME|OUT|PCT'
col time_waited format b999999999 heading 'TIME|WAITED'
col average_wait format b999999.9 heading 'AVERAGE|WAIT'
col avg_wait_ms format b9999.0 heading 'AVERAGE|WAIT|(ms)'
col MAX_WAIT format b999999.9 heading 'MAX|WAIT'
col MAX_WAIT_MS format b999999 heading 'MAX|WAIT|(ms)'
COLUMN   module HEADING  'MODULE' FORMAT   a29 trunc
rem
break on report on inst_id on s_sid on username on elapsed_secs skip 1
compute sum of total_waits total_timeouts time_waited_secs on report s_sid
set pause off;

select
       s.inst_id,
       s.sid s_sid,
       s.username,
       (sysdate - s.logon_time) * 24 *60 *60 elapsed_secs,
       e.event,
       e.total_waits,
       e.total_timeouts,
       100 * e.total_timeouts / e.total_waits timeout_pct,
       e.time_waited_micro / 1000000 time_waited_secs,
       e.MAX_WAIT * 10 max_wait_ms,
       e.time_waited_micro / e.total_waits / 1000 avg_wait_ms
  from gv$session_event e, gv$session s
   where s.sid like nvl( '&sid', '%')
   and s.inst_id = e.inst_id
   and s.sid = e.sid
   and s.username like nvl(upper('&username'), '%')
   and s.username IS NOT NULL
 order by elapsed_secs desc, e.inst_id, e.sid, e.event;



select distinct s.statistic#, s.sid, s.value / 100 CPU, n.name
    from gv$sesstat s, gv$statname n, gv$session ss
   where n.statistic# = s.statistic#
     and s.value <> 0
   and ss.inst_id = s.inst_id
     and s.sid like nvl('&sid','%')
   and ss.sid = s.sid
   and ss.username like nvl(upper('&username'), '%')
   and ss.username IS NOT NULL
  and n.name like '%CPU%'
  order by sid, n.name
/

set pause on;

Usage

SQL> @sess.sql
Enter value for sid: %
Enter value for username: SCOTT

Parameters

• sid: Session ID pattern (% for all sessions, specific SID for single session)
• username: Username pattern (% for all users, specific username for single user)

Required Privileges

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

Sample Output

Session Wait Events

Global Session Events

INST SESS USERNAME   ELAPSED EVENT                        TOTAL  TOTAL    TIME  TIME    MAX  AVERAGE
     SID             SECS                                 WAITS  TIMEOUTS OUT   WAITED  WAIT WAIT
                                                                           PCT   (secs)  (ms) (ms)
---- ---- ---------- ------- ---------------------------- ------ ------- ----- ------- ---- -------
   1  123 SCOTT        3,456 SQL*Net message from client    245      0   0.0    2,890  15000    11.8
                               db file sequential read        89      0   0.0       45    120     0.5
                               log file sync                  23      0   0.0       12     45     0.5
                               undo segment extension          2      0   0.0        1    350     0.5

   2  256 SCOTT        2,134 SQL*Net message from client    156      0   0.0    1,890  12000    12.1
                               db file scattered read         45      0   0.0       23     85     0.5
                               direct path write               8      0   0.0        5     25     0.6

CPU Statistics

STATISTIC#  SID        CPU NAME
---------- ---- ---------- ------------------------------
         1  123      12.45 CPU used by this session
         2  123       8.90 CPU used when call started
         1  256       7.23 CPU used by this session
         2  256       5.67 CPU used when call started

Key Output Columns

Session Wait Analysis

• INST: RAC instance number
• SESS SID: Session identifier
• USERNAME: Database username
• ELAPSED SECS: Time since session login
• EVENT: Wait event name
• TOTAL WAITS: Number of times session waited for this event
• TOTAL TIMEOUTS: Number of timeouts for this event
• TIME OUT PCT: Percentage of waits that timed out
• TIME WAITED (secs): Total time waited for this event
• MAX WAIT (ms): Maximum single wait time in milliseconds
• AVERAGE WAIT (ms): Average wait time per occurrence

CPU Statistics

• STATISTIC#: Internal statistic number
• SID: Session identifier
• CPU: CPU time in seconds
• NAME: CPU statistic description

Performance Analysis

Session Performance Assessment

• High Elapsed Time: Long-running sessions
• High Total Waits: Resource-intensive operations
• High Average Wait: Performance bottlenecks
• High Timeout Percentage: Network or resource contention issues

RAC-Specific Analysis

• Cross-Instance Comparison: Compare same user across instances
• Load Distribution: Check if workload is balanced
• Instance Affinity: Identify sessions tied to specific instances

Common Use Cases

1. Performance Troubleshooting

   • Identify sessions with performance issues
   • Analyze wait patterns for specific users
   • Compare performance across RAC instances

2. Capacity Planning

   • Monitor resource consumption by user/application
   • Identify peak usage patterns
   • Plan for infrastructure scaling

3. Application Analysis

   • Understand application behavior patterns
   • Identify resource-intensive operations
   • Optimize connection strategies

Advanced Analysis

Top Resource Consumers

-- Find sessions with highest resource usage
SELECT inst_id, sid, username,
       elapsed_secs,
       total_wait_time,
       cpu_time,
       (total_wait_time + cpu_time) total_resource_time
FROM (
    SELECT s.inst_id, s.sid, s.username,
           (SYSDATE - s.logon_time) * 86400 elapsed_secs,
           SUM(e.time_waited_micro)/1000000 total_wait_time,
           MAX(st.value)/100 cpu_time
    FROM gv$session s, gv$session_event e, gv$sesstat st, gv$statname sn
    WHERE s.sid = e.sid
    AND s.inst_id = e.inst_id
    AND s.sid = st.sid
    AND s.inst_id = st.inst_id
    AND st.statistic# = sn.statistic#
    AND sn.name = 'CPU used by this session'
    AND s.username IS NOT NULL
    GROUP BY s.inst_id, s.sid, s.username, s.logon_time
)
ORDER BY total_resource_time DESC;

Wait Event Distribution

-- Analyze wait event distribution across sessions
SELECT event,
       COUNT(*) session_count,
       AVG(total_waits) avg_waits_per_session,
       AVG(time_waited_micro/1000000) avg_wait_time_secs
FROM gv$session_event e, gv$session s
WHERE e.sid = s.sid
AND e.inst_id = s.inst_id
AND s.username IS NOT NULL
AND e.total_waits > 0
GROUP BY event
ORDER BY session_count DESC;

Session Efficiency Analysis

-- Calculate session efficiency metrics
SELECT inst_id, sid, username,
       ROUND(cpu_pct, 2) cpu_efficiency_pct,
       ROUND(avg_wait_ms, 2) avg_wait_ms,
       CASE
         WHEN cpu_pct > 70 THEN 'CPU Intensive'
         WHEN avg_wait_ms > 50 THEN 'I/O Bound'
         ELSE 'Balanced'
       END workload_type
FROM (
    SELECT s.inst_id, s.sid, s.username,
           st.value/100 cpu_time,
           SUM(e.time_waited_micro)/1000000 wait_time,
           st.value*100/(st.value + SUM(e.time_waited_micro)/100) cpu_pct,
           AVG(e.time_waited_micro/e.total_waits)/1000 avg_wait_ms
    FROM gv$session s, gv$session_event e, gv$sesstat st, gv$statname sn
    WHERE s.sid = e.sid
    AND s.inst_id = e.inst_id
    AND s.sid = st.sid
    AND s.inst_id = st.inst_id
    AND st.statistic# = sn.statistic#
    AND sn.name = 'CPU used by this session'
    AND s.username IS NOT NULL
    AND e.total_waits > 0
    GROUP BY s.inst_id, s.sid, s.username, st.value
)
ORDER BY cpu_efficiency_pct DESC;

Troubleshooting Common Issues

High Timeout Percentages

• Network Issues: Check network connectivity and latency
• Resource Contention: Look for blocking sessions or locks
• Parameter Tuning: Review timeout-related parameters

Long Elapsed Times

• Idle Sessions: May need cleanup or timeout configuration
• Long-Running Operations: Verify if expected or optimize
• Connection Pooling: Consider implementing for better resource usage

High Average Wait Times

• I/O Performance: Check storage subsystem performance
• Contention: Look for hot blocks or popular resources
• SQL Tuning: Optimize inefficient queries

Session Management

Session Cleanup

-- Find long-running idle sessions
SELECT inst_id, sid, username, status,
       ROUND((SYSDATE - logon_time) * 24, 2) hours_connected,
       ROUND((SYSDATE - last_call_et/86400), 2) hours_idle
FROM gv$session
WHERE username IS NOT NULL
AND status = 'INACTIVE'
AND (SYSDATE - logon_time) > 1/24  -- More than 1 hour
ORDER BY hours_connected DESC;

Resource Monitoring

-- Monitor resource usage trends
SELECT TO_CHAR(SYSDATE, 'HH24:MI') check_time,
       COUNT(*) active_sessions,
       AVG(cpu_usage) avg_cpu_per_session,
       SUM(cpu_usage) total_cpu_usage
FROM (
    SELECT st.value/100 cpu_usage
    FROM gv$sesstat st, gv$statname sn, gv$session s
    WHERE st.statistic# = sn.statistic#
    AND st.sid = s.sid
    AND st.inst_id = s.inst_id
    AND sn.name = 'CPU used by this session'
    AND s.username IS NOT NULL
    AND s.status = 'ACTIVE'
);

Best Practices

1. Regular Monitoring

   • Check during peak business hours
   • Monitor long-running sessions
   • Track resource consumption patterns

2. Performance Analysis

   • Focus on sessions with high resource usage
   • Compare performance across instances
   • Identify optimization opportunities

3. Capacity Planning

   • Track session count and resource trends
   • Plan for peak load scenarios
   • Monitor growth patterns

Related Scripts

• Session Analysis
• Session Wait Details
• User Performance Analysis