V$SESSION_LONGOPS will NOT (in general) tell you how much longer a particular session -- or even statement -- is going to run.
In fact, it can't. Stated this way, the question (applied to sessions, anyway) is equivalent to a classic problem in computing science, known as the HALTING PROBLEM -- one which is (fairly easily) proven to be unsolvable.
[Sticking to the "theory" things for just a moment more -- it should be provable that any read-consistent single SQL statement *will^ execute in a finite amount of time. Providing, at least, that it makes no calls to PL/SQL. But "finite" can still be very-very-very-large, as anybody who has written a cartesian join across 3 or 4 very large tables has quickly learned. In any case, though, once we start talking about sessions (or even PL/SQL calls), all bets are off -- there is no way to even say with certainty that an arbitrary job will ever complete.]
Forgetting esoteric "theory", and just sticking to the nuts an bolts, we have the following problems and limitations around LONGOPS:
(*) Only a few selected operations (for example, full scans) are considered "eligible" for consideration as "LONGOPS".
(*) Even when your plan has an eligible operation, LONGOPS are only tracked in this view once certain criteria (e.g., elapsed time, object size, etc.) have been met.
(*) A session can execute many statements. Once the LONGOP you are watching has completed and (presumably) that statement finishes, the session can execute MORE statements -- possibly with MORE LONGOPS. Or not.
(*) A single statement can have many LONGOPS. Once the LONGOP you are watching has completed, the same statement might -- or might not -- begin executing ANOTHER longop. How many will there be? Its really hard to know.
The best we can say about V$SESSION_LONGOPS is that it can give you a lower-bound estimate for runtime.
That is, when you are monitoring a LONGOP that is projected to take another 90 minutes, then it is a pretty good bet that the current statement will PROBABLY take AT LEAST 90 more minutes to complete. (That said, this is not a lot more reliable than watching the progress meter for file-copies on Windows. Sometimes the projected times are pretty accurate, and other times, they are nowhere near.)
dbms_application_info.set_session_longops
In Oracle 11g and beyond, v$sql_monitor serves as a replacement for the cumbersome v$session_longops view for monitor long-running SQL
The best news is that statistics collection is now made automatic in 11g with
v$sql_plan_monitor / v$sql_monitor
----------------------------------------------------------------------------------------------------------------------
Also:
_sqlmon_max_plan: Default = 20 per CPU. This hidden parameter establishes a maximum number of plan entries that can be monitored.
_sqlmon_max_planlines: Default = 300. This hidden parameter establishes the number of plan lines beyond which a plan cannot be monitored.
----------------------------------------------------------------------------------------------------------------------
Also (for Linear operations):
Hidden parameter _SQLEXEC_PROGRESSION_COST
This parameter controls the population of V$SESSION_LONGOPS view by long running queries. This view is used to monitor the progress of queries that are running for long duration. Queries that cost more than the value that has been set are identified for monitoring. Progression monitoring involves overhead and may affect the performance. The default value is 1000, which may prevent SQL statements from being shared! Setting it to 0 will turn off the monitoring
In fact, it can't. Stated this way, the question (applied to sessions, anyway) is equivalent to a classic problem in computing science, known as the HALTING PROBLEM -- one which is (fairly easily) proven to be unsolvable.
[Sticking to the "theory" things for just a moment more -- it should be provable that any read-consistent single SQL statement *will^ execute in a finite amount of time. Providing, at least, that it makes no calls to PL/SQL. But "finite" can still be very-very-very-large, as anybody who has written a cartesian join across 3 or 4 very large tables has quickly learned. In any case, though, once we start talking about sessions (or even PL/SQL calls), all bets are off -- there is no way to even say with certainty that an arbitrary job will ever complete.]
Forgetting esoteric "theory", and just sticking to the nuts an bolts, we have the following problems and limitations around LONGOPS:
(*) Only a few selected operations (for example, full scans) are considered "eligible" for consideration as "LONGOPS".
(*) Even when your plan has an eligible operation, LONGOPS are only tracked in this view once certain criteria (e.g., elapsed time, object size, etc.) have been met.
(*) A session can execute many statements. Once the LONGOP you are watching has completed and (presumably) that statement finishes, the session can execute MORE statements -- possibly with MORE LONGOPS. Or not.
(*) A single statement can have many LONGOPS. Once the LONGOP you are watching has completed, the same statement might -- or might not -- begin executing ANOTHER longop. How many will there be? Its really hard to know.
The best we can say about V$SESSION_LONGOPS is that it can give you a lower-bound estimate for runtime.
That is, when you are monitoring a LONGOP that is projected to take another 90 minutes, then it is a pretty good bet that the current statement will PROBABLY take AT LEAST 90 more minutes to complete. (That said, this is not a lot more reliable than watching the progress meter for file-copies on Windows. Sometimes the projected times are pretty accurate, and other times, they are nowhere near.)
dbms_application_info.set_session_longops
In Oracle 11g and beyond, v$sql_monitor serves as a replacement for the cumbersome v$session_longops view for monitor long-running SQL
The best news is that statistics collection is now made automatic in 11g with
v$sql_plan_monitor / v$sql_monitor
----------------------------------------------------------------------------------------------------------------------
Also:
_sqlmon_max_plan: Default = 20 per CPU. This hidden parameter establishes a maximum number of plan entries that can be monitored.
_sqlmon_max_planlines: Default = 300. This hidden parameter establishes the number of plan lines beyond which a plan cannot be monitored.
----------------------------------------------------------------------------------------------------------------------
Also (for Linear operations):
Hidden parameter _SQLEXEC_PROGRESSION_COST
This parameter controls the population of V$SESSION_LONGOPS view by long running queries. This view is used to monitor the progress of queries that are running for long duration. Queries that cost more than the value that has been set are identified for monitoring. Progression monitoring involves overhead and may affect the performance. The default value is 1000, which may prevent SQL statements from being shared! Setting it to 0 will turn off the monitoring
