SQL Long Operations Analysis (gvslop.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

Monitors long-running operations across RAC instances showing progress completion percentages, estimated time remaining, and detailed operation information. This script is essential for tracking the progress of large batch operations, index builds, table scans, and other time-intensive database activities.

The Script

rem  gvslop.sql
rem
ttitle 'SQL Long Operations Progress'
rem
set lines 132
rem
col inst_id format 999 heading 'INST'
col sid format 9999 heading 'SID'
col username format a15 heading 'USERNAME'
col osuser format a10 heading 'OS|USERNAME'
col Percent format 99999.99 head "Percent|Complete"
col time_remaining format 999999999 head "Seconds|Remaining"
col opname format a14 head "Operation Name"
col message format a100 wrap head "Message"
col elapsed_seconds format 999999999 head "Elapsed|Seconds"
col Start_Time format a20 head "Start Time"
rem
clear breaks
break on sid nodup
rem
SELECT
  sl.inst_id
, sl.SID
, sl.username
, s.osuser
, sl.sql_id
, to_char(sl.start_time,'mm-dd-yyyy HH24:MI:ss') Start_Time
, sl.elapsed_seconds
, sl.time_remaining
, decode(sl.totalwork, 0, 0, round(100 * sl.sofar/sl.totalwork, 2)) "Percent"
, sl.message
from gv$session_longops sl
    ,gv$session s
where sl.inst_id = s.inst_id
  and sl.sid = s.sid
  and sl.sofar < sl.totalwork
  and nvl(sl.inst_id, 0) like nvl('&inst_id', '%')
  and (sl.sid like nvl('&sid', '%') and sl.serial# like nvl('&serial', '%'))
  and nvl(sl.username,'null') like nvl(upper('&username'),'%')
ORDER BY to_char(sl.start_time,'mm-dd-yyyy HH24:MI:ss')
/
rem
clear breaks

Usage

-- Basic usage - show all current long operations
@gvslop.sql
-- Enter: [press enter for all instances]
-- Enter: [press enter for all sessions]
-- Enter: [press enter for all serials]
-- Enter: [press enter for all users]

-- Filter by specific RAC instance
@gvslop.sql
-- Enter: 1 (for instance 1)

-- Filter by specific session
@gvslop.sql
-- Enter: [press enter for all instances]
-- Enter: 1234 (for specific SID)
-- Enter: 5678 (for specific serial number)

-- Filter by username
@gvslop.sql
-- Enter: [press enter for all instances]
-- Enter: [press enter for all sessions]
-- Enter: [press enter for all serials]
-- Enter: SCOTT (for specific user)

Parameters

The script prompts for:

• inst_id: RAC instance ID (% for all instances)
• sid: Session ID (% for all sessions)
• serial: Serial number (% for all serials)
• username: Username (% for all users)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT on GV$SESSION_LONGOPS
SELECT on GV$SESSION

Sample Output

                                SQL Long Operations Progress

INST  SID USERNAME        OS         SQL_ID        Start Time           Elapsed     Seconds    Percent Message
                          USERNAME                                      Seconds   Remaining   Complete
---- ---- --------------- ---------- ------------- -------------------- --------- --------- --------- ------------------------
   1 1234 SCOTT          oracle     abc123def456  06-01-2025 14:30:22      1800      2400     42.50 Table Scan: EMPLOYEES: 425 out of 1000 Blocks done
   2 2345 DBA_USER       dba        def456ghi789  06-01-2025 14:25:15      2100       900     70.00 Index Build: IDX_SALES_DATE: 700 out of 1000 Keys done
   1 3456 BATCH_USER     batch      ghi789jkl012  06-01-2025 14:20:10      2700       300     90.00 Sort Output: 900 out of 1000 Rows done

Key Output Columns

• INST: RAC instance number
• SID: Session identifier
• USERNAME: Database username
• OS USERNAME: Operating system username
• SQL_ID: Current SQL statement identifier
• Start Time: When the operation began
• Elapsed Seconds: Time already spent on operation
• Seconds Remaining: Estimated time remaining
• Percent Complete: Progress percentage (sofar/totalwork * 100)
• Message: Descriptive message about the operation

Operation Types Commonly Tracked

Index Operations

• Index builds and rebuilds
• Index creation with large datasets
• Bitmap index maintenance

Table Operations

• Full table scans on large tables
• Table reorganizations
• Partition operations

Sort Operations

• Large sort operations
• Hash joins with large datasets
• Group by operations

Backup/Recovery Operations

• RMAN backup operations
• Recovery operations
• Export/Import operations

Common Use Cases

Long Operation Monitoring

-- Monitor progress of current long operations
@gvslop.sql
-- Track completion percentages and time estimates

Performance Planning

-- Estimate completion times for batch jobs
@gvslop.sql
-- Use time remaining estimates for scheduling

Resource Usage Analysis

-- Identify resource-intensive operations
@gvslop.sql
-- Analyze sessions consuming significant time

RAC Load Balancing

-- Check distribution of long operations across instances
@gvslop.sql
-- Ensure balanced workload across RAC nodes

Performance Impact

• Minimal Impact: Reads system views with no locking
• Real-time Data: Provides current progress information
• RAC Aware: Consolidates information across all instances

Troubleshooting Tips

No Results Displayed

• Check if any long operations are currently running
• Verify that operations meet the filtering criteria (sofar < totalwork)

Inaccurate Time Estimates

• Oracle’s time estimates improve as operations progress
• Early estimates may be less accurate than later ones

Related Scripts

• gvsess - Active session analysis
• gvtrans - Transaction analysis
• sqltext - SQL text retrieval by SQL_ID
• gvio - I/O statistics analysis