Long-running queries might be a hard nut to crack. Whether you are curious about performance tuning or just want to be aware of what is going on in your database, Oracle offers v$session_longops for the management of long-running queries (over 6s).
This is a sample query – feel free to adjust 😉
SELECT -- process info sn.sid , sn.serial# , lo.sql_id , lo.opname as operation_name -- server/user info , sn.machine , sn.username , sn.osuser , sn.module -- runtime info , lo.start_time as started_at , sysdate as current_time , round(lo.elapsed_seconds/60) || ':' || mod(lo.elapsed_seconds,60) as elapsed_time , round(lo.time_remaining/60) || ':' || mod(lo.time_remaining,60) as remaining_time , round(lo.sofar/lo.totalwork*100, 2) as processed_in_perc FROM v$session sn -- LEFT JOIN v$session_longops lo ON sn.sid = lo.sid AND sn.serial# = lo.serial# -- sorry about this one, it's NOT commented out :) -- WHERE 1=1 AND sn.username = 'ORACLE_WORLD'
Note: You must have SQL_TRUE set to TRUE and use ANALYZE during stats gathering.