We regularly work with database tables with hundreds of millions of entries. Some operations on these table can take a while. Not necessarily queries, but operations in preparation to make queries fast, for example the creation of materialized views or indexes.
The problem with most SQL tools is: once you run your SQL statement you have no indication of how long it will take to complete the operation. No progress bar and no display of the remaining time. Will it take minutes or hours?
Oracle databases have a nice feature I learned about recently that can answer these questions. Operations that take longer than 6 seconds to complete are considered “long operations” and get an entry in a special view called V$SESSION_LONGOPS.
This view does not only contain the currently running long operations but also the history of completed long operations. You can query the status of the current long operations like this:
SELECT * FROM V$SESSION_LONGOPS WHERE time_remaining > 0;
This view contains columns like
- TARGET (table or view on which the operation is carried out)
- SOFAR (units of work done so far)
- TOTALWORK (total units of work)
- ELAPSED_SECONDS (number of elapsed seconds from the start of the operation)
Based on these values the view offers another column, which contains the estimated remaining time in seconds: TIME_REMAINING.
This remaining time is really just an estimate, because it assumes long running operations to be linear, which is not necessarily true. Also some SQL statements can spawn multiple consecutive operations, e.g. first a “Table Scan” operation and then a “Sort Output” operation, which will only become visible after the first operation has finished. Nevertheless I found this feature quite helpful to get a rough idea of how long I will have to wait or to inform decisions such as whether I really want to perform an operation until completion or if I want to cancel it.
Schneide Blog 