5

I have a time consuming query which populates a materialized view. It has been running for 18 hours - which was not expected.

SELECT STATUS
FROM V$SESSION
WHERE SID=194;

The above query shows the session as 'ACTIVE'. I was wondering if that is sufficient proof that the query is still running and whether there is any other way to double check that the session is not dead.

UPDATE

The session was not dead; but there was a constant wait:

 SQL*Net more data from dblink 

I used sql trace as suggested by Phil to reach this conclusion.

  • 1
    What does v$session_longops say? you can use sofar/totalwork to see what percentage done it is. If that draws a blank, trace the session and see if anything appears in the trace file – Philᵀᴹ Mar 18 '15 at 10:59
  • 'v$session_longops' gives blank. Thanks for the tip; I will have a look into tracing the session. – Stelios Mar 18 '15 at 11:00
  • I will have to rerun the query to run the trace on it; which is a bit inconvenient in this case. – Stelios Mar 18 '15 at 12:33
1

You should query V$SESSION_WAIT to see what your statement is doing. A short description of the wait events can be found in "Part IV Appendixes", "C Oracle Wait Events" of the "Database Reference"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.