V$SESSION_LONGOPS

You can query long running SQL in V$SESSION_LONGOPS.

 

In the example shown, the SQL is a simple delete statement so I have managed to shorten the output by using a col sql_text format a20. Normally it is much longer.

 

Each time you run the SQL, the figures are recalculated. The elapsed_seconds column, which I have renamed as time_taken, should increase every time the SQL is rerun.

 

The time_remaining column, which I have renamed as time_left, is only an estimate. Normally it goes down each time the SQL is rerun but sometimes it goes up.

 

Once the SQL is finished, the executions column changes to 1 and the time_remaining column goes to 0:

 

  1  SELECT SQL_TEXT, EXECUTIONS,

  2  ELAPSED_SECONDS TIME_TAKEN,

  3  TIME_REMAINING TIME_LEFT

  4  FROM V$SESSION SES, V$SQL SQL,

  5  V$SESSION_LONGOPS LONGOPS

  6  WHERE SES.USERNAME       = 'BRAID'

  7  AND   SES.SQL_ADDRESS    = SQL.ADDRESS

  8  AND   SES.SQL_HASH_VALUE = SQL.HASH_VALUE

  9  AND   SQL.ADDRESS        = LONGOPS.SQL_ADDRESS

 10* AND   SQL.HASH_VALUE     = LONGOPS.SQL_HASH_VALUE

SQL> /

 

SQL_TEXT             EXECUTIONS TIME_TAKEN  TIME_LEFT

-------------------- ---------- ---------- ----------

delete b_alp                  0        204       1010

 

SQL> /

 

SQL_TEXT             EXECUTIONS TIME_TAKEN  TIME_LEFT

-------------------- ---------- ---------- ----------

delete b_alp                  0        710        507

 

SQL> /

 

SQL_TEXT             EXECUTIONS TIME_TAKEN  TIME_LEFT

-------------------- ---------- ---------- ----------

delete b_alp                  1       1220          0

 

SQL>