I have compiled a few useful queries to estimate the elapsed and remaining time for Long Running operations using using v$session_longops and gv$session_longops.
A description of v$session_longops and gv$session_longops:
SQL> 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
TIMESTAMP DATE
TIME_REMAINING NUMBER
ELAPSED_SECONDS NUMBER
CONTEXT NUMBER
MESSAGE VARCHAR2(512)
USERNAME VARCHAR2(30)
SQL_ADDRESS RAW(8)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_PLAN_HASH_VALUE NUMBER
SQL_EXEC_START DATE
SQL_EXEC_ID NUMBER
SQL_PLAN_LINE_ID NUMBER
SQL_PLAN_OPERATION VARCHAR2(30) SQL_PLAN_OPTIONS VARCHAR2(30)
QCSID NUMBER
SQL> desc gv$session_longops;
Name Null? Type
----------------------------------------- -------- ----------------------------
INST_ID NUMBER
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
TIMESTAMP DATE
TIME_REMAINING NUMBER
ELAPSED_SECONDS NUMBER
CONTEXT NUMBER
MESSAGE VARCHAR2(512)
USERNAME VARCHAR2(30)
SQL_ADDRESS RAW(8)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_PLAN_HASH_VALUE NUMBER
SQL_EXEC_START DATE
SQL_EXEC_ID NUMBER
SQL_PLAN_LINE_ID NUMBER
SQL_PLAN_OPERATION VARCHAR2(30)
SQL_PLAN_OPTIONS VARCHAR2(30)
QCSID NUMBER
How to estimate the elapsed and remaining time for a long-running operation in Oracle?
Note the only difference in the two queries is v$session_longops VS gv$session_longops and the addition of the inst_id column to indicate the ID of the RAC instance.
RAC environments:
SELECT
serial#,
inst_id,
sid,
opname,
sofar,
(totalwork) - (sofar) remaining_work,
start_time,
round(elapsed_seconds/60) elapsed_time_in_mins,
round(time_remaining/60) remaining_time_in_mins
FROM gv$session_longops
WHERE time_remaining > 0
and SOFAR <> TOTALWORK;
order by 6 desc;
Non-RAC environments:
SELECT
serial#,
sid,
opname,
sofar,
(totalwork) - (sofar) remaining_work,
start_time,
round(elapsed_seconds/60) elapsed_time_in_mins,
round(time_remaining/60) remaining_time_in_mins
FROM v$session_longops
WHERE time_remaining > 0
and SOFAR <> TOTALWORK;
order by 6 desc;
Very similarly, how to estimate the elapsed and remaining time for a long-running RMAN operation in Oracle?
RAC environments:
SELECT
serial#,
inst_id,
sid,
opname,
sofar,
(totalwork) - (sofar) remaining_work,
start_time,
round(elapsed_seconds/60) elapsed_time_in_mins,
round(time_remaining/60) remaining_time_in_mins
FROM gv$session_longops
WHERE time_remaining > 0
and opname like '%RMAN%'
order by 6 desc;
Non-RAC environments:
SELECT
serial#,
inst_id,
sid,
opname,
sofar,
(totalwork) - (sofar) remaining_work,
start_time,
round(elapsed_seconds/60) elapsed_time_in_mins,
round(time_remaining/60) remaining_time_in_mins
FROM gv$session_longops
WHERE time_remaining > 0
and opname like '%RMAN%'
and SOFAR <> TOTALWORK;
order by 6 desc;
How to estimate the estimated remaining time for a long-running SQL query?
RAC:
select sql_id, totalwork, sofar, elapsed_seconds,time_remaining from gv$session_longops where sql_id in ('INSERT_SQL_ID_IN_HERE');
Non-RAC:
select sql_id, totalwork, sofar, elapsed_seconds,time_remaining from v$session_longops where sql_id in ('INSERT_SQL_ID_IN_HERE');
