Thursday, October 06, 2011

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>

No comments: