Session I/O Summary by Program (vjobsumi.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.

Session I/O Summary by Program

This script provides a comprehensive view of I/O activity grouped by program name, showing both logical reads (buffer cache hits) and physical reads (disk I/O) for all active sessions. The metrics are divided by 1000 for better readability, making it easier to identify which applications are generating the most database activity.

Script: vjobsumi.sql

rem  vjobsumi.sql
rem
ttitle 'Session Summary by Program ( /1000 )'
rem
col program format a48 heading 'PROGRAM'
col program_count format 9999 heading 'JOB|COUNT'
col logical_reads format b999999 heading 'LOGICAL|READS'
col log_job format b9999 heading 'LOG|/JOB'
col physical_reads format b99999 heading 'DISK|READS'
col phy_job format b999 heading 'PHY|/JOB'
rem
break on report
compute sum of program_count logical_reads physical_reads on report
rem
select program,
       (block_gets+consistent_gets)/1000 logical_reads,
       (physical_reads)/1000 physical_reads
  from v$session s,
       v$sess_io i
 where s.sid = i.sid(+)
 order by program, block_gets + consistent_gets desc;

What This Script Does

• Groups I/O metrics by program - Aggregates all session I/O activity by the connecting program name
• Shows logical reads - Displays buffer cache reads (block gets + consistent gets) divided by 1000
• Shows physical reads - Displays disk I/O operations divided by 1000
• Identifies resource-intensive applications - Highlights which programs are generating the most database activity
• Provides cumulative totals - Includes summary totals for all programs
• Orders by I/O activity - Sorts results by logical reads to show heaviest consumers first
• Includes all active sessions - Uses outer join to capture sessions even without I/O statistics

Usage

Execute this script during peak hours to identify which applications are driving database load:

SQL> @vjobsumi

For specific program analysis:

SQL> @vjobsumi
-- Review output for specific applications like JDBC, SQL*Plus, or custom programs

Required Privileges

The user executing this script needs:

• SELECT privilege on V$SESSION
• SELECT privilege on V$SESS_IO
• Or the SELECT_CATALOG_ROLE role

Sample Output

Session Summary by Program ( /1000 )

PROGRAM                                          LOGICAL    DISK
                                                  READS   READS
------------------------------------------------ ------- -------
JDBC Thin Client                                  45,231   2,341
  oracle@dbserver01 (J001)                        12,456     892
  oracle@dbserver01 (J002)                         8,234     445
sqlplus@appserver01 (TNS V1-V3)                   23,456   1,234
oracle@dbserver01 (LGWR)                          18,900       0
oracle@dbserver01 (DBW0)                          15,678  12,345
OEM.DesktopAgent.exe                              12,345     567
python@appserver02 (TNS V1-V3)                    8,901     234
oracle@dbserver01 (SMON)                          7,890      45
oracle@dbserver01 (PMON)                          3,456      12
                                                 ------- -------
                                                 154,291  17,870

Key Output Columns

Column	Description	Interpretation
PROGRAM	Application or process name	Identifies the connecting program or background process
LOGICAL READS	Buffer cache reads (thousands)	Higher values indicate memory-intensive operations
DISK READS	Physical disk reads (thousands)	Higher values indicate I/O-intensive operations

Understanding the Analysis

The script helps identify:

1. Application Resource Usage

   • Which applications consume the most database resources
   • Relative I/O patterns between different programs
   • Unexpected resource consumption by specific applications

2. I/O Patterns

   • High logical reads with low physical reads indicate good buffer cache efficiency
   • High physical reads suggest potential tuning opportunities
   • Background process I/O patterns for system health assessment

3. Performance Impact

   • Programs with extremely high I/O may need query optimization
   • Identifies candidates for connection pooling or resource limits
   • Helps prioritize application tuning efforts

Common Use Cases

1. Application Performance Analysis

-- Run during business hours to identify heavy applications
SQL> @vjobsumi

-- Focus on specific application patterns
-- High logical reads = CPU-intensive queries
-- High physical reads = I/O bottlenecks

2. Resource Planning

-- Identify top consumers for capacity planning
SQL> @vjobsumi

-- Use results to:
-- - Plan buffer cache sizing
-- - Evaluate I/O subsystem requirements
-- - Implement resource manager plans

3. Troubleshooting Performance Issues

-- When database is slow, identify culprit applications
SQL> @vjobsumi

-- Look for:
-- - Sudden spikes in I/O for specific programs
-- - Unusual programs with high activity
-- - Background processes with excessive I/O

Advanced Analysis

Combining with Session Details

-- Get detailed session information for high I/O programs
SELECT s.sid, s.serial#, s.username, s.program,
       i.block_gets, i.consistent_gets, i.physical_reads
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
  AND s.program LIKE '%JDBC%'  -- Focus on specific program
  AND (i.block_gets + i.consistent_gets) > 1000000
ORDER BY i.block_gets + i.consistent_gets DESC;

Analyzing I/O Efficiency

-- Calculate buffer cache hit ratio by program
SELECT program,
       SUM(block_gets + consistent_gets) logical_reads,
       SUM(physical_reads) physical_reads,
       ROUND(100 * (1 - SUM(physical_reads) /
             NULLIF(SUM(block_gets + consistent_gets), 0)), 2) hit_ratio
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
GROUP BY program
HAVING SUM(block_gets + consistent_gets) > 10000
ORDER BY 2 DESC;

Time-based Analysis

-- Track I/O patterns over time (requires periodic sampling)
CREATE TABLE io_history AS
SELECT SYSDATE sample_time, program,
       SUM(block_gets + consistent_gets) logical_reads,
       SUM(physical_reads) physical_reads
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
GROUP BY program;

Performance Insights

1. High Logical Reads

   • May indicate inefficient queries with excessive buffer gets
   • Could suggest missing indexes or poor execution plans
   • Might show legitimate high-volume OLTP activity

2. High Physical Reads

   • Indicates data not found in buffer cache
   • May suggest undersized buffer cache
   • Could indicate full table scans or poor clustering

3. Program Patterns

   • Batch programs typically show high I/O in bursts
   • OLTP applications show consistent moderate I/O
   • Background processes have predictable I/O patterns

Troubleshooting

No Output or Limited Results

-- Check session visibility
SELECT COUNT(*) FROM v$session;

-- Verify I/O statistics are populated
SELECT COUNT(*) FROM v$sess_io WHERE block_gets > 0;

Unexpected Program Names

-- Get more details about unknown programs
SELECT DISTINCT program, machine, terminal, osuser
FROM v$session
WHERE program LIKE '%unknown%'
ORDER BY program;

Missing I/O Statistics

-- Some sessions may not have I/O stats immediately
-- Check sessions with activity but no I/O records
SELECT s.sid, s.program, s.status, s.last_call_et
FROM v$session s
WHERE NOT EXISTS (SELECT 1 FROM v$sess_io i WHERE i.sid = s.sid)
  AND s.status = 'ACTIVE';

Integration with Other Scripts

Combine with these complementary scripts for deeper analysis:

• gvsess.sql - Detailed session activity analysis
• vsessio.sql - Individual session I/O metrics
• pgmcnt.sql - Program connection counts
• vio2.sql - System-wide I/O statistics
• gvio.sql - RAC-aware I/O analysis

Best Practices

1. Regular Monitoring

   • Run during different workload periods
   • Compare results over time to identify trends
   • Establish baselines for normal activity

2. Application Identification

   • Ensure applications set meaningful program names
   • Use connection pooling with identifiable names
   • Implement module/action naming for better granularity

3. Performance Optimization

   • Focus on programs with highest I/O first
   • Consider buffer cache sizing based on logical read patterns
   • Implement resource limits for runaway applications

4. Capacity Planning

   • Use cumulative I/O metrics for sizing decisions
   • Plan for growth based on historical trends
   • Consider I/O patterns when designing storage architecture