Enhanced Long Operations Monitor (vlop.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 enhanced Oracle script monitors currently active long-running operations across all RAC instances using global views (gv$session_longops and gv$session). It shows only operations that are still in progress (sofar < totalwork) with instance ID, SQL ID, and user information for comprehensive monitoring.

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
 order by s.inst_id,sid, last_update_time;

Usage

-- Run the script to see all active long operations across RAC
@vlop.sql

No parameters required - automatically shows all active operations.

Required Privileges

• SELECT privilege on gv$session_longops
• SELECT privilege on gv$session

Sample Output

INST_ID   SID SQL_ID        OS         ORACLE      Message                                                           Last     Elapsed
                           USERNAME   USERNAME                                                                      Update   Seconds
------- ----- ------------- ---------- ----------- ------------------------------------------------------------- -------- -------
      1   123 4zw7g3mn2k9pq oracle     SCOTT       Table Scan: SCOTT.LARGE_TABLE: 15000 out of 50000 Blocks done  14:30:25     245
      1   156 9b2x1df5kg7mn sysdba     SYS         Index Build: HR.IDX_EMPLOYEES: 35% complete                     14:28:10     380
      2   089 5c3n9wf2lx6vp backup     RMAN        RMAN Backup: DATABASE: 67% complete                             14:32:15     1205
      2   201 7h5m2qg8jk4xt oracle     APP_USER    Data Pump Export: SCHEMA=HR: 45% complete                       14:31:50     890

Key Output Columns

• INST_ID: RAC instance ID where the operation is running
• SID: Session identifier
• SQL_ID: SQL identifier for the operation (when available)
• OS USERNAME: Operating system user running the operation
• ORACLE USERNAME: Oracle database username
• Message: Detailed operation description with progress information
• Last Update: Time of last progress update (HH24:MI:SS)
• Elapsed Seconds: Total time elapsed since operation started

Understanding the Results

Instance Distribution

• Shows which RAC instance each operation is running on
• Helps identify workload distribution across cluster nodes
• Useful for performance balancing and troubleshooting

Operation Types in RAC Environments

1. Table Scans

   • Large table full scans across multiple nodes
   • Parallel query operations
   • Cross-instance data access patterns

2. Index Operations

   • Index builds/rebuilds
   • Partition maintenance
   • Online index operations

3. RMAN Operations

   • Database backups
   • Archive log backups
   • Recovery operations

4. Data Pump Operations

   • Export/import jobs
   • Schema transfers
   • Tablespace transport

Progress Tracking

• Percentage completion shown for operations with known endpoints
• Block/row counts for scan operations
• Current phase information for multi-step processes

Common Use Cases

1. RAC Workload Monitoring

-- Monitor operations across all RAC instances
@vlop.sql
-- Identify which instances are handling heavy workloads

2. Parallel Operation Tracking

-- Monitor parallel query operations
@vlop.sql
-- Look for operations with same SQL_ID across instances

3. Backup Progress Monitoring

-- Track RMAN backup progress cluster-wide
@vlop.sql
-- Monitor backup operations across all nodes

4. Data Movement Operations

-- Monitor large data operations
@vlop.sql
-- Track Data Pump, partition moves, and table reorganizations

Performance Analysis

RAC-Specific Considerations

1. Cross-Instance Communication

   • Monitor for excessive inter-instance messaging
   • Check for Global Cache Service (GCS) waits
   • Analyze Cluster File System (CFS) performance

2. Load Balancing

   • Identify instances with heavy long operation activity
   • Monitor for uneven workload distribution
   • Check connection load balancing effectiveness

3. Resource Contention

   • Look for operations competing for shared resources
   • Monitor global enqueue waits
   • Check for storage-level contention

Performance Optimization

1. Parallel Operations

   • Use SQL_ID to identify related parallel operations
   • Monitor degree of parallelism across instances
   • Check for optimal parallel execution distribution

2. Resource Allocation

   • Monitor memory usage patterns across instances
   • Check for adequate temp space on all nodes
   • Verify CPU utilization balance

Troubleshooting

No Operations Showing

• All operations completed: Check recently completed operations
• Filter condition: Script only shows active operations (sofar < totalwork)
• Timing: Operations may be too fast to capture

Performance Issues

• Slow progress: Check for resource contention or locking
• Uneven distribution: Investigate connection load balancing
• High elapsed time: Monitor wait events and system resources

RAC-Specific Issues

• Instance isolation: Operations stuck on one instance
• Interconnect problems: High cross-instance communication
• Storage bottlenecks: Shared storage performance issues

Related Scripts

• Single Session Long Operations (qlop.sql) - Session-specific monitoring
• Active Session Analysis (gvsess.sql) - Overall RAC session monitoring
• RAC Lock Analysis (gvlockb.sql) - Cluster lock contention
• RAC Instance Status (gvinst.sql) - RAC instance information

Notes

• Uses global views (gv$) for RAC cluster-wide monitoring
• Filters to show only active operations (sofar < totalwork)
• Includes SQL_ID for correlation with other performance data
• Shows both OS and Oracle usernames for security tracking
• Wide line format (500 characters) to accommodate detailed messages
• Ordered by instance and session for logical grouping

Advantages Over Single-Instance Scripts

1. Cluster Visibility: See operations across all RAC instances
2. No Manual Instance Switching: Single query covers entire cluster
3. Load Distribution: Understand workload patterns across nodes
4. Correlation: SQL_ID helps correlate with other performance tools
5. Comprehensive View: Complete picture of cluster activity