Long Operations Monitor (qlop.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 Oracle script monitors long-running operations for a specific session ID by querying the v$session_longops view. It displays detailed progress information including elapsed time, completion status, and operation messages with formatted output for easy analysis.

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 132

col sid format 9999
col active format a1 head "A|c|t"
col opname format a14 head "Operation Name"
col message format a78 wrap head "Message"
col lut format a14 head "Last|Update"
col elapsed_seconds head "Elapsed|Seconds"

clear breaks
break on sid nodup

select slop.sid,
       decode(slop.serial#,s.serial#,'*',' ') active,
       -- slop.opname,
       slop.message,
       to_char(slop.last_update_time,'mm/dd hh24:mi:ss') lut,
       slop.elapsed_seconds
  from v$session_longops slop,
       v$session s
 where slop.sid = &sid
   -- and s.sid(+) = slop.sid
   and s.sid  = slop.sid
   and s.logon_time <= slop.last_update_time
 order by slop.sid, slop.last_update_time;

Usage

-- Run the script and enter a session ID when prompted
@qlop.sql
-- Enter value for sid: 123

The script will prompt you to enter a session ID (SID) to monitor.

Required Privileges

• SELECT privilege on v$session_longops
• SELECT privilege on v$session

Sample Output

  SID A|c|t Message                                                             Last           Elapsed
      |  |                                                                       Update         Seconds
----- --- -------------------------------------------------------------------------- -------------- -------
  123  *  Table Scan: SCOTT.EMP: 50000 out of 100000 Blocks done                 12/15 14:30:25     245
  123     Index Build: SCOTT.IDX_EMP: 25% complete                               12/15 14:25:10     180
  123     RMAN Backup: 75% complete                                              12/15 14:35:00     420

Key Output Columns

• SID: Session identifier being monitored
• Act: Active indicator (* = currently active session, space = inactive)
• Message: Detailed description of the long operation including progress
• Last Update: Timestamp of the last progress update (MM/DD HH24:MI:SS)
• Elapsed Seconds: Total time elapsed since operation started

Understanding the Results

Active Session Indicator

• * (asterisk): Session is currently active and connected
• Space: Session may be disconnected but operation is still tracked

Common Operation Types

• Table Scan: Full table scans on large tables
• Index Build: Index creation or rebuild operations
• RMAN Backup: Recovery Manager backup operations
• Data Pump: Export/import operations
• Partition Operations: Partition maintenance tasks
• Sort Operations: Large sort operations using temp space

Progress Tracking

The message field shows:

• Percentage complete for operations with known endpoints
• Blocks/rows processed for scan operations
• Current phase for multi-step operations

Common Use Cases

1. Monitor Export/Import Operations

-- Check progress of Data Pump operations
@qlop.sql
-- Look for messages like "DATAPUMP: 45% complete"

2. Track Index Rebuilds

-- Monitor index rebuild progress
@qlop.sql
-- Look for messages like "Index Build: 75% complete"

3. Monitor RMAN Backups

-- Check backup progress
@qlop.sql
-- Look for messages like "RMAN Backup: 60% complete"

4. Track Large Query Progress

-- Monitor long-running queries
@qlop.sql
-- Look for table scan progress messages

Performance Analysis

When Operations Take Longer Than Expected

1. Check for Resource Contention

   • Monitor I/O wait events
   • Check for lock contention
   • Verify adequate temp space

2. Analyze Progress Rate

   • Compare elapsed time to expected completion
   • Check if progress rate is consistent
   • Monitor for performance degradation

3. Resource Usage

   • Check CPU and memory usage
   • Monitor disk I/O patterns
   • Verify network performance for remote operations

Troubleshooting

No Results Returned

• Verify SID exists: Check v$session for valid session IDs
• Check timing: Operation may have completed or not started
• Privileges: Ensure access to v$session_longops view

Stalled Operations

• Check locks: Use lock analysis scripts to identify blocking
• Monitor waits: Check v$session_wait for wait events
• Resource limits: Verify temp space and memory allocation

Historical Data

• Retention: v$session_longops retains completed operations for a limited time
• Archive: Consider querying AWR for historical long operations data

Related Scripts

• Active Session Analysis (gvsess.sql) - Overall session monitoring
• Enhanced Long Operations (vlop.sql) - RAC-aware version
• Session Wait Analysis (gvsessw.sql) - Session wait events
• Lock Analysis (gvlockb.sql) - Lock contention analysis

Notes

• Script prompts for session ID input via substitution variable
• Joins with v$session to determine if session is currently active
• Output is formatted for easy reading with column breaks
• Historical operations remain visible even after completion
• Useful for tracking progress of operations that don’t provide other progress indicators