sql – How can I find out the remaining running time of a query in Oracle?

Question:

There is a request that has been successfully launched and I would like to know how much more it will work.

It seems like there are some standard Oracle tables in the database, by which you can track the statistics of the query, including finding out how much time is left until the finish of the query.

Or maybe someone knows how with the help of tools such as Toad or PL/SQL Developer , you can see how long the query still has to work.

I would be very grateful for your help in providing information.

Answer:

Use v $ session_longops or gv$session_longops (if you have RAC – Real Application Cluster):

select  /*opname, target,*/ 
        sid||','||serial#||',@'||inst_id as kill_sess,
        elapsed_seconds as "elaps/s", 
        time_remaining as "remain/s", 
        round((sofar*100)/totalwork) "%done", 
        to_char(start_time,'hh24:mi:ss') as started,
        sql_id||' ('||sql_plan_hash_value||')'as "sql_id (plan_hash)", 
        message  
from    v$session_longops
where   time_remaining > 0
order   by start_time desc;

V$SESSION_LONGOPS displays the status of various operations that run
for longer than 6 seconds (in absolute time). These operations
currently include many backup and recovery functions, statistics
gathering, and query execution, and more operations are added for
every Oracle release. To monitor query execution progress, you must be
using the cost-based optimizer and you must:

• Set the TIMED_STATISTICS or SQL_TRACE parameters to true
• Gather statistics for your objects with the DBMS_STATS package

---

Если вам известен SQL_ID:

undef sql_id
set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000

select 
   DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'&&sqlid',report_level=>'ALL') as report 
from dual;