Oracle Progress Bar

Oracle Progress Bar

I am looking for a good way to get progress on long running SQL queries. I
have been able to successfully do this with set_session_longops when this
is being instrumented in code; ergo when there are steps or looping to a
stored procedure, etc. But I also need this on individual long running
queries. Oracle does track any long running processes over 6 absolute
seconds automatically to the longops view; so it's possible to query these
out where the time_remaining is greater than 0. But I haven't found a way
to to get the specific row's serial number associated to the query. I am
concerned with just taking the max of the rowid as it's possible we may
have race conditions with other async processes running. I thought I would
put this out there and see if anyone else has been able to find a way to
achieve this; not using a marquee (indeterminate bar), etc. Oracle tracks
the process by the number of blocks so it is able to average out an
estimated time remaining based on the totalwork (total number of blocks)
divided by the sofar (blocks completed so far).