V$SESSION_LONGOPS | Find long running queries (long ops) in Oracle

I will explain V$SESSION_LONGOPS | Find long running queries (long ops) in Oracle in this post.

 

V$SESSION_LONGOPS in Oracle

V$SESSION_LONGOPS view is very important view for Oracle DBA that displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations are backup and recovery operations, statistics gathering, and query execution, and more operations are added for every Oracle release.

 

 

 

 

 

How to Find long running queries (long ops) in Oracle

If you have big database about more than 1 TB, then your backup and export operations are taking long times.

 

If you start long operations such as RMAN Backup and Datapump Export in Oracle database, probably you want to see progress of these operations.

 

You can see and monitor RMAN Backup progress with following script.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK order by 6 desc;

 

You can monitor Datapump Export ,Import and other long operations with following script.

 

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK order by 6 desc;



 

You can see and monitor RMAN Backup and Restore Status and progress with following script.

 

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set line 2222;
set pages 2222;
set long 6666;
select sl.sid, sl.opname,
to_char(100*(sofar/totalwork), '990.9')||'%' pct_done,
sysdate+(TIME_REMAINING/60/60/24) done_by
from v$session_longops sl, v$session s
where sl.sid = s.sid
and sl.serial# = s.serial#
and sl.sid in (select sid from v$session where module like 'backup%' or module like 'restore%' or module like 'rman%')
and sofar != totalwork
and totalwork > 0
/

 

Or You can use the following script to monitor Long running Backup & Restore

 

SET HEAD OFF
SELECT 'RMAN Throughput : '||
ROUND(SUM(v.value/(power(2,30))),1) || ' GB so far ---> Per Second Throughput = ' ||
ROUND(SUM(v.value /(power(2,30)))/NVL((SELECT MIN(elapsed_seconds)
FROM v$session_longops
WHERE opname LIKE 'RMAN: aggregate input'
AND sofar != TOTALWORK
AND elapsed_seconds IS NOT NULL
),SUM(v.value /(power(2,30)))),2) || ' GB'
FROM gv$sesstat v, v$statname n, gv$session s
WHERE v.statistic# = n.statistic#
AND n.name = 'physical write total bytes'
AND v.sid = s.sid
AND v.inst_id = s.inst_id
AND s.program LIKE '[email protected]%'
GROUP BY n.name
/

 

Example output is as follows.

 

SQL> SET HEAD OFF
SQL> SELECT 'RMAN Throughput : '||
2 ROUND(SUM(v.value/(power(2,30))),1) || ' GB so far ---> Per Second Throughput = ' ||
3 ROUND(SUM(v.value /(power(2,30)))/NVL((SELECT MIN(elapsed_seconds)
4 FROM v$session_longops
5 WHERE opname LIKE 'RMAN: aggregate input'
6 AND sofar != TOTALWORK
7 AND elapsed_seconds IS NOT NULL
8 ),SUM(v.value /(power(2,30)))),2) || ' GB'
9 FROM gv$sesstat v, v$statname n, gv$session s
10 WHERE v.statistic# = n.statistic#
11 AND n.name = 'physical write total bytes'
12 AND v.sid = s.sid
13 AND v.inst_id = s.inst_id
14 AND s.program LIKE '[email protected]%'
15 GROUP BY n.name
16 /

RMAN Throughput : 70952.9 GB so far ---> Per Second Throughput = 1.04 GB

SQL>

 

Or you can also use the following script to monitor RMAN.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM gV$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR != TOTALWORK order by 6 desc;

 

 

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 2,363 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply