DB – How to monitor Oracle database long ops ?

As DBAs, some questions we are often asked and to be fair we ask ourselfs when we are doing massive operations, are :
Is the database doing something?
How long the database takes to complete the task?

There a data dictionary view, GV$SESSION_LONGOPS whom is really usefull for monitoring and to provide estimated time to get the task done.

Starting with a first example, a query for general propose .


set lines 240
col message form a100
col opname form a50
 
select * from
(
 select
 round(l.sofar/l.totalwork*100,2) as complete,
 --l.*
 l.sid,
 --l.opname,
 l.message,
 l.start_time,
 l.time_remaining/60 "minuts remaining",
 l.elapsed_seconds
 from
 gv$session_longops l
 where totalwork !=0
)
where complete < 100;

The view is also usefull on specific operations such as Oracle Recovery Manager (RMAN) jobs monitoring, this query returns information about backup,restore and recovery.


 
select sid, serial#,opname, context, sofar, totalwork, round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where
  lower(opname) like 'rman%'
  and opname not like '%aggregate%'
  and totalwork != 0
  and sofar <> totalwork;
 
select
   to_char(start_time,'dd-mon-yy hh24:mi') "backup started",
   opname,
   sofar,
   totalwork,
   elapsed_seconds/60 "elapse (min)",
   round(sofar/totalwork*100,2) "complete%"
from v$session_longops
where
  lower(opname) like 'rman%'
  and opname not like '%aggregate%'
  and totalwork != 0
  and sofar <> totalwork;

Another great example happens when connected the V$SESSION_LONGOPS and V$SESSION, as it is possible to bring more information about the session.



set lines 200
col username form a30
col opname form a30
col DONE form a5

-- full
select b.username, a.sid, b.opname,
to_char(b.start_time,'yyyy/mm/dd hh24:mi:ss') START_TIME,
round(b.sofar*100/b.totalwork,0) || '%' DONE,
to_char(to_date( b.time_remaining,'sssss'),'hh24:mi:ss') ETA 
from v$session_longops b, v$session a
where a.sid = b.sid order by 6;

-- in progress
select b.username, a.sid, b.opname,
to_char(b.start_time,'yyyy/mm/dd hh24:mi:ss') START_TIME,
round(b.sofar*100/b.totalwork,0) || '%' DONE,
to_char(to_date( b.time_remaining,'sssss'),'hh24:mi:ss') ETA 
from v$session_longops b, v$session a
where a.sid = b.sid and 
round(b.sofar*100/b.totalwork,0) <> 100
order by 6;

Thank you for reading, hope this post was helpful.
Rogerio

More about it: V$SESSION_LONGOPS | V$SESSION

Rogerio Antonio

DB – How to monitor Oracle database long ops ?

Comments