Hi Tom,
I use v$session_longops for estimating the time remaining on a long running sql. I have noticed recently a sql that was running for more than 2 hours was not showing up in v$session_longops and the only wait event i see was DB File Sequential Read. I tried to look around in v$session to see if there is a column in a view that can give me the estimated time remaining but i only saw elapsed_time on there (unless i missed something). Is there a sql/formula that i could use to compute the time remaining for a query not in longops but running for more than an hour or so?
Here is my longops script:
SELECT s.inst_id,
s.sid,
s.serial#,
sl.qcsid,
s.username,
s.module,
sl.opname,
sl.time_remaining/60 time_remaining
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#
and sl.totalwork<>sl.sofar;I appreciate your insight.
Thanks
v$session_longops only shows information about a specific step in the plan, e.g. a full table scan. So if you have a plan like:
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | CHANNELS |
| 4 | PARTITION RANGE ALL| |
| 5 | TABLE ACCESS FULL | SALES |
| 6 | TABLE ACCESS FULL | TIMES |
------------------------------------------
And the database is currently reading channels, the longops will only tell you how long it expects to take to read that table. It says nothing about the overall execution time.
You still need to read sales and times (which may appear as separate longops). Then join everything together to complete the query.
How long will that take?
¯\_(ツ)_/¯
Seriously. It's an unknowable thing. Even if you know exactly how many rows there are in each table and how many the join will return, it's impossible to know for certain how long is left. There's too many other variables which impact the run time, such as:
- Do you have to read the data from disk or memory?
- How fast is your hardware?
- What else is going on in the system?
If you're licensed for Diagnostics and Tuning, you could look at a SQL monitor report. This will show you which part of the plan is currently executing. This can help give you some idea of
work remaining. But not how
long this will take.
https://oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1