Session System Statistics (vsesstat.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 detailed session-level system statistics with flexible filtering capabilities. It shows resource consumption, performance metrics, and activity patterns for specific sessions or statistics, making it invaluable for session-level performance analysis and troubleshooting.

Script

rem  vsesstat.sql
rem
ttitle 'Session System Statistics - By Sid'
rem
col sid format 99990 heading 'SID'
col value format 999,999,999,999 heading 'VALUE'
col name format a58 heading 'NAME'
rem
break on sid
rem
select s.sid, s.value, n.name
  from v$sesstat s, v$statname n
 where n.statistic# = s.statistic#
   and s.value <> 0
   and s.sid like nvl('&sid','%')
   and n.name like nvl('&name','%')
   and s.statistic# like nvl('&statistic','%')
 order by sid, n.name;

Usage

-- Run the script in SQL*Plus or SQLcl
@vsesstat.sql

-- When prompted, enter filters (% for all):
-- sid: Session ID pattern
-- name: Statistic name pattern
-- statistic: Statistic number pattern

-- Examples
-- All statistics for specific session
Enter value for sid: 145
Enter value for name: %
Enter value for statistic: %

-- Specific statistic across all sessions
Enter value for sid: %
Enter value for name: %logical reads%
Enter value for statistic: %

-- CPU-related stats for all sessions
Enter value for sid: %
Enter value for name: %CPU%
Enter value for statistic: %

Parameters

• &sid: Session ID pattern (specific SID or % for all)
• &name: Statistic name pattern (use % for wildcard)
• &statistic: Statistic number pattern (use % for wildcard)

Required Privileges

• SELECT on V$SESSTAT
• SELECT on V$STATNAME
• Generally available to most database users

Sample Output

Session System Statistics - By Sid

  SID            VALUE NAME
----- ---------------- ----------------------------------------------------------
  145            1,234 CPU used by this session
                 5,678 CPU used when call started
               234,567 DB time
               123,456 buffer gets
                 8,901 consistent gets
                   234 db block gets
               345,678 logical reads
                 2,345 physical reads
                    12 physical writes
               456,789 redo size
                   567 session logical reads

  234           12,345 CPU used by this session
                98,765 DB time
               567,890 buffer gets
               234,567 consistent gets
                 1,234 db block gets
               789,012 logical reads
                 5,678 physical reads
                    45 physical writes

Key Statistics Categories

CPU and Time Statistics

• CPU used by this session: Total CPU time consumed
• CPU used when call started: CPU time for active calls
• DB time: Total database time (includes waits)
• session connect time: Time since session connected

I/O Statistics

• logical reads: Buffer cache reads (consistent gets + db block gets)
• consistent gets: Read-consistent block access
• db block gets: Current mode block access
• physical reads: Disk reads performed
• physical writes: Disk writes performed
• buffer gets: Total buffer cache accesses

Memory Statistics

• session pga memory: PGA memory currently allocated
• session pga memory max: Peak PGA memory usage
• session uga memory: UGA memory allocated
• sorts (memory): Sorts performed in memory
• sorts (disk): Sorts that spilled to disk

Transaction Statistics

• user commits: Number of commits performed
• user rollbacks: Number of rollbacks performed
• transaction rollbacks: Failed transactions
• redo size: Amount of redo generated

Performance Analysis

Session Efficiency Indicators

1. High logical reads: Memory-intensive operations
2. High physical reads: I/O-intensive operations
3. Physical read ratio: (physical reads / logical reads) * 100
4. CPU efficiency: CPU time relative to elapsed time

Resource Consumption Patterns

1. Memory usage: PGA and UGA allocation patterns
2. I/O patterns: Read vs write activity
3. CPU utilization: Processing intensity
4. Redo generation: DML activity levels

Problem Identification

1. High CPU consumers: Sessions with excessive CPU usage
2. I/O intensive sessions: High physical read activity
3. Memory hogs: Sessions with large PGA/UGA allocation
4. Long-running operations: High DB time values

Common Use Cases

Session Performance Analysis

-- Analyze specific problematic session
@vsesstat.sql
-- Enter specific SID
-- Enter % for name and statistic
-- Review all statistics for the session

I/O Analysis

-- Focus on I/O-related statistics
@vsesstat.sql
-- Enter % for sid
-- Enter %read% or %write% for name
-- Enter % for statistic

CPU Analysis

-- Analyze CPU consumption
@vsesstat.sql
-- Enter % for sid
-- Enter %CPU% for name
-- Enter % for statistic

Memory Analysis

-- Review memory usage patterns
@vsesstat.sql
-- Enter % for sid
-- Enter %memory% or %pga% for name
-- Enter % for statistic

Troubleshooting Scenarios

High CPU Sessions

1. Identify top CPU consumers:

   -- Find sessions with high CPU usage
   @vsesstat.sql
   -- Filter by %CPU% in name
   -- Sort by VALUE column

2. Analyze SQL causing CPU usage:

   SELECT s.sid, s.sql_id, sq.sql_text
   FROM v$session s, v$sql sq
   WHERE s.sql_address = sq.address
   AND s.sid = &high_cpu_sid;

High I/O Sessions

1. Find I/O intensive sessions:

   @vsesstat.sql
   -- Filter by %physical read% or %logical read%
   -- Identify sessions with high values

2. Calculate I/O efficiency:

   SELECT sid,
          SUM(CASE WHEN name = 'physical reads' THEN value END) physical_reads,
          SUM(CASE WHEN name = 'logical reads' THEN value END) logical_reads,
          ROUND(SUM(CASE WHEN name = 'physical reads' THEN value END) /
                SUM(CASE WHEN name = 'logical reads' THEN value END) * 100, 2) phys_read_pct
   FROM v$sesstat s, v$statname n
   WHERE s.statistic# = n.statistic#
   AND n.name IN ('physical reads', 'logical reads')
   GROUP BY sid
   HAVING SUM(CASE WHEN name = 'logical reads' THEN value END) > 0
   ORDER BY phys_read_pct DESC;

Memory Issues

1. Identify memory-intensive sessions:

   @vsesstat.sql
   -- Filter by %pga% or %uga% in name
   -- Look for sessions with high memory allocation

2. Sort analysis:

   @vsesstat.sql
   -- Filter by %sort% in name
   -- Check for disk sorts indicating memory pressure

Advanced Analysis

Session Resource Efficiency

-- Calculate session efficiency metrics
SELECT s.sid,
       MAX(CASE WHEN n.name = 'CPU used by this session' THEN s.value END) cpu_time,
       MAX(CASE WHEN n.name = 'DB time' THEN s.value END) db_time,
       MAX(CASE WHEN n.name = 'logical reads' THEN s.value END) logical_reads,
       MAX(CASE WHEN n.name = 'physical reads' THEN s.value END) physical_reads,
       ROUND(MAX(CASE WHEN n.name = 'CPU used by this session' THEN s.value END) /
             NULLIF(MAX(CASE WHEN n.name = 'DB time' THEN s.value END), 0) * 100, 2) cpu_efficiency
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name IN ('CPU used by this session', 'DB time', 'logical reads', 'physical reads')
GROUP BY s.sid
HAVING MAX(CASE WHEN n.name = 'DB time' THEN s.value END) > 0
ORDER BY cpu_efficiency DESC;

Top Resource Consumers

-- Find top sessions by specific resource
SELECT sid, value, name
FROM (
  SELECT s.sid, s.value, n.name,
         RANK() OVER (PARTITION BY n.name ORDER BY s.value DESC) rnk
  FROM v$sesstat s, v$statname n
  WHERE s.statistic# = n.statistic#
  AND n.name IN ('CPU used by this session', 'logical reads', 'physical reads', 'redo size')
  AND s.value > 0
)
WHERE rnk <= 5
ORDER BY name, rnk;

Session Comparison

-- Compare two sessions side by side
SELECT n.name,
       MAX(CASE WHEN s.sid = &sid1 THEN s.value END) session_1,
       MAX(CASE WHEN s.sid = &sid2 THEN s.value END) session_2
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND s.sid IN (&sid1, &sid2)
AND s.value > 0
GROUP BY n.name
HAVING MAX(CASE WHEN s.sid = &sid1 THEN s.value END) > 0
    OR MAX(CASE WHEN s.sid = &sid2 THEN s.value END) > 0
ORDER BY n.name;

Related Scripts

• User Session Analysis (../security-analysis/vuser.md) - Session overview with I/O totals
• Active Session Analysis (gvsess.md) - Current session activity
• Session I/O Statistics (vsessio.md) - Dedicated I/O analysis
• System Statistics (../database-info/vsysname.md) - System-wide statistics