How to find out on which object my query is "stuck" right now?

 So you have a query that seems to run forever..

v$session_wait shows "db file sequential read" , but since we have a multi join, the questions is on which object we are spending time.

Answer: P1, P2 and P3 reveal the object in case:

SQL>select wait_class,p1,p2,p3 from v$session where sid=425:

WAIT_CLASS                 P1         P2         P3

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

  db file sequential read       5    2046999          1

 SQL> SELECT owner , segment_name , segment_type 

            FROM dba_extents WHERE file_id = 5 AND 

            (select p2 from v$session_wait where sid=425) BETWEEN block_id AND block_id + blocks -1;

  

OWNER

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

SEGMENT_NAME

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

SEGMENT_TYPE

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

SP

SP_AUDIT

TABLE