Long Operations Monitoring (vlopa.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 monitors currently running long operations across all RAC instances by querying the GV$SESSION_LONGOPS view. It identifies operations that are still in progress (SOFAR < TOTALWORK), providing real-time visibility into long-running database activities such as table scans, large sorts, index builds, backup operations, and data loading activities. This is essential for monitoring system performance and identifying potential bottlenecks.

The Script

-- oracle@duo017u>desc v$session_longops
--  Name                                      Null?    Type
--  ----------------------------------------- -------- ----------------------------
--  SID                                                NUMBER
--  SERIAL#                                            NUMBER
--  OPNAME                                             VARCHAR2(64)
--  TARGET                                             VARCHAR2(64)
--  TARGET_DESC                                        VARCHAR2(32)
--  SOFAR                                              NUMBER
--  TOTALWORK                                          NUMBER
--  UNITS                                              VARCHAR2(32)
--  START_TIME                                         DATE
--  LAST_UPDATE_TIME                                   DATE
--  TIME_REMAINING                                     NUMBER
--  ELAPSED_SECONDS                                    NUMBER
--  CONTEXT                                            NUMBER
--  MESSAGE                                            VARCHAR2(512)
--  USERNAME                                           VARCHAR2(30)
--  SQL_ADDRESS                                        RAW(4)
--  SQL_HASH_VALUE                                     NUMBER
--  QCSID                                              NUMBER
--

set lines 500

col sid format 9999
col opname format a14 head "Operation Name"
col message format a95 wrap head "Message"
col lut head "Last|Update"
col elapsed_seconds head "Elapsed|Seconds"
col osuser format a10 heading 'OS|USERNAME'
col username format a11 heading 'ORACLE|USERNAME'


clear breaks
break on sid nodup

select s.inst_id,slop.sid,s.sql_id,
       -- slop.opname,
    OSUSER,s.USERNAME,
       slop.message,
       to_char(slop.last_update_time,'hh24:mi:ss') lut,
       slop.elapsed_seconds
  from gv$session_longops slop,
       gv$session s
 where sofar < totalwork
   and s.sid = slop.sid
  and s.sql_id is not null
 order by s.inst_id,sid, last_update_time;

Key Features

• RAC-Aware Monitoring: Shows long operations across all instances
• Real-Time Progress: Displays current progress and timing information
• Active Operations Filter: Shows only operations currently in progress
• Session Context: Correlates operations with user sessions and SQL statements
• Progress Tracking: Provides detailed operation progress messages
• Multi-Instance Coordination: Monitors cluster-wide long operations
• Performance Impact Assessment: Helps identify resource-intensive operations

Usage

@vlopa.sql

No parameters required - shows all currently active long operations.

Required Privileges

SELECT on GV$SESSION_LONGOPS
SELECT on GV$SESSION

Sample Output

INST_ID  SID SQL_ID        OS         ORACLE      MESSAGE                                                                        Last     Elapsed
                           USERNAME   USERNAME                                                                               Update   Seconds
------- ---- ------------- ---------- ----------- ------------------------------------------------------------------------------ -------- -------
      1  145 8k2m9p3x7v5q  appuser    SALES_REP   Table Scan: SALES_HISTORY: 1,234,567 out of 5,000,000 Blocks done           14:23:45    1,245

      1  289 3p7w9m2k8v4q  batchjob   ETL_USER    Hash Join: Build Hash Table: 567,890 out of 2,000,000 Rows done             14:25:12      890

      2  167 7k3m8p9x2v1q  oracle     SYS         RMAN Backup: Channel 1: 45,678 out of 100,000 Blocks done                   14:26:33    2,156

      2  234 9m5k2p7x4v6q  dataload   DW_ADMIN    Sort Output: 2,345,678 out of 8,000,000 Rows done                            14:28:17      567

      1  356 6v8m3k9p5x2q  analyst    REPORT_USER Index Fast Full Scan: CUSTOMER_IDX: 123,456 out of 500,000 Blocks done       14:29:41      234

Key Output Columns

• INST_ID: RAC instance ID where the operation is running
• SID: Session ID performing the long operation
• SQL_ID: SQL statement identifier associated with the operation
• OS USERNAME: Operating system user running the session
• ORACLE USERNAME: Database username
• MESSAGE: Detailed progress message showing operation type and completion status
• Last Update: Time of last progress update (HH24:MI:SS)
• Elapsed Seconds: Total time the operation has been running

Understanding Long Operations

Common Long Operation Types

Table and Index Operations

-- Table Scan operations:
-- Full table scans on large tables
-- Index fast full scans
-- Table access by index rowid
-- Partition scans

Sort and Hash Operations

-- Sort operations:
-- Large ORDER BY processing
-- Group BY with aggregations
-- DISTINCT processing
-- Sort-merge joins

-- Hash operations:
-- Hash join build phases
-- Hash aggregation
-- Hash distinct processing

Backup and Recovery

-- RMAN operations:
-- Database backups
-- Tablespace backups
-- Archive log backups
-- Recovery operations

Data Loading

-- Data loading operations:
-- SQL*Loader direct path loads
-- INSERT /*+ APPEND */ operations
-- CREATE TABLE AS SELECT
-- Partition maintenance

Progress Interpretation

Progress Messages

-- Message format typically includes:
-- Operation type (Table Scan, Sort Output, etc.)
-- Target object (table, index, partition)
-- Progress (X out of Y units done)
-- Units (Blocks, Rows, Bytes)

Completion Estimation

-- Estimate completion:
-- Progress percentage = (SOFAR / TOTALWORK) * 100
-- Remaining work = TOTALWORK - SOFAR
-- Estimated completion = Current time + TIME_REMAINING

Common Use Cases

1. Performance Monitoring

   • Monitor long-running queries and operations
   • Identify performance bottlenecks
   • Track operation progress and completion
   • Assess system resource utilization

2. Batch Job Monitoring

   • Monitor ETL process progress
   • Track data loading operations
   • Monitor backup and recovery activities
   • Assess batch processing efficiency

3. Capacity Planning

   • Understand operation duration patterns
   • Plan maintenance windows
   • Assess resource requirements
   • Support infrastructure planning

4. Troubleshooting

   • Identify stuck or abnormally slow operations
   • Diagnose performance degradation
   • Monitor cluster-wide activity
   • Support incident resolution

Advanced Analysis

Performance Pattern Analysis

Operation Duration Trends

-- Analyze operation patterns:
-- Peak operation times
-- Resource contention periods
-- Seasonal processing variations
-- System load correlations

Resource Impact Assessment

-- Assess resource impact:
-- I/O utilization during operations
-- CPU consumption patterns
-- Memory usage spikes
-- Network traffic increases

Progress Monitoring

Completion Prediction

-- Predict completion times:
-- Current progress rate
-- Historical operation durations
-- Resource availability
-- System load factors

Performance Comparison

-- Compare with baselines:
-- Historical operation times
-- Expected vs. actual progress
-- Performance degradation detection
-- Optimization opportunities

Troubleshooting Applications

Stuck Operations

Identification

-- Signs of stuck operations:
-- No progress updates for extended periods
-- Elapsed time significantly exceeding normal
-- Last update time not advancing
-- Zero or minimal progress rate

Resolution Actions

-- Address stuck operations:
-- Check for blocking sessions
-- Verify resource availability
-- Assess I/O subsystem health
-- Consider operation cancellation

Performance Degradation

Slow Progress Detection

-- Identify slow operations:
-- Progress rate below expectations
-- Elapsed time exceeding baselines
-- Resource utilization patterns
-- Concurrent operation conflicts

Optimization Strategies

-- Optimize slow operations:
-- Increase parallel degree
-- Adjust memory allocation
-- Optimize I/O configuration
-- Reschedule conflicting operations

RAC-Specific Considerations

Cross-Instance Operations

Global Coordination

-- Monitor cross-instance operations:
-- Parallel query coordination
-- Global cache operations
-- Inter-instance data transfers
-- Cluster resource utilization

Load Balancing

-- Assess load distribution:
-- Operation distribution across instances
-- Resource utilization balance
-- Instance-specific bottlenecks
-- Workload optimization opportunities

Instance Affinity

Operation Placement

-- Monitor operation placement:
-- Data locality considerations
-- Resource availability by instance
-- Network traffic optimization
-- Processing efficiency

Integration with Monitoring

Automated Monitoring

Alert Configuration

-- Set up automated alerts:
-- Operations exceeding time thresholds
-- Stuck operation detection
-- Resource utilization spikes
-- Critical operation failures

Dashboard Integration

-- Dashboard components:
-- Real-time operation status
-- Progress visualization
-- Historical trend analysis
-- Performance metrics

Historical Tracking

Trend Analysis

-- Track operation trends:
-- Duration patterns over time
-- Frequency of long operations
-- Resource consumption trends
-- Performance improvement tracking

Capacity Planning

-- Support capacity planning:
-- Peak operation periods
-- Resource requirement forecasting
-- Infrastructure scaling needs
-- Maintenance window planning

Best Practices

Regular Monitoring

-- Establish monitoring routine:
-- Check during peak hours
-- Monitor critical batch windows
-- Track maintenance operations
-- Validate backup processes

Performance Optimization

-- Optimize long operations:
-- Use parallel processing where appropriate
-- Optimize SQL statements
-- Implement proper indexing
-- Configure adequate resources

Proactive Management

-- Proactive operation management:
-- Schedule operations during off-peak hours
-- Monitor resource availability
-- Plan for operation dependencies
-- Implement progress checkpoints

Related Scripts

• gvsess.sql - Global session analysis
• vsession.sql - Single-instance session monitoring
• gvwait.sql - Wait event analysis
• systime.sql - System timing analysis

Operation Categories

Query Operations

-- Common query-related long operations:
-- Table Scan (full table scans)
-- Index Fast Full Scan
-- Hash Join (build and probe phases)
-- Sort Output (ORDER BY, GROUP BY)
-- Hash Group By

Maintenance Operations

-- Maintenance-related operations:
-- Gather Table Statistics
-- Create Index
-- Rebuild Index
-- Alter Table (add column, etc.)
-- Partition Maintenance

Backup Operations

-- Backup and recovery operations:
-- RMAN Backup
-- Export/Import
-- Data Pump operations
-- Recovery operations
-- Flashback operations

Automation Opportunities

Automated Reporting

-- Generate automated reports:
-- Daily long operations summary
-- Weekly performance trends
-- Monthly capacity analysis
-- Exception reporting

Integration with Schedulers

-- Scheduler integration:
-- Job progress monitoring
-- Dependency management
-- Resource allocation
-- Error handling

Summary

This script is essential for:

1. Real-Time Monitoring - Tracking currently running long operations across RAC instances
2. Performance Management - Identifying bottlenecks and optimization opportunities
3. Capacity Planning - Understanding operation patterns and resource requirements
4. Troubleshooting - Diagnosing stuck or slow operations
5. Operational Visibility - Providing transparency into database processing activities