v$session_longops is a dynamic performance view in which Oracle reports on the status of operations that run for more than 6 (elapsed) seconds. Various operations, including queries are automatically reported in this view. Applications can also mark operations as long running with DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS.
I recently spent a day on a site (not a PeopleSoft system) watching a single update statement that eventually ran for 27 hours. I used this view to predict when the SQL statement would complete.
You can see incomplete long running operations by querying long operations where the work done so far is less than the total amount of work.
The operations remain in this view long after they have completed, so you can use it to get a feel for what long operations occur on a sytem. But there are a fixed number of slots for v$session_longops, determined by ksulomux_. The entry for a completed operation remains in the view until the slots is reused. They are not reused in order of allocation, instead the array element to be reused is "probably determined by a MOD function" (My thanks to Tanel Poder for his clarification). So v$session_longops contains both current incomplete long operations and an arbitrary sample of completed long operations.
You can use the SQL_ADDRESS and SQL_HASH_VALUE to get the SQL statement from the library cache.
Aggregate total amount of time spent on SQL statements. But there is a problem, SQL statements can produce multiple long operations that may or may not occur concurrently.
But what if the statement isn’t in the library cache we can try and get it out of statspack! This SQL function looks up the SQL statement by hash value and assembles the pieces up to 4000 characters
This version gets the SQL statement from library cache, and if it isn't there it looks it up from the statspack data.
This is some sample output. This particular statement has been submitted by the publication servers 25 times with a cumulative execution time of 878 seconds.