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

Oracle: how to turn an IN subquery into hash join

 The goal is to replace the nested loop, that is taking long, by a hash join.

Original query:

SELECT /*+ PARALLEL(M,8) */ 

my_tab_BAN,'CR019',(SELECT LOGICALY_DATE FROM

LOGICALY_DATE WHERE LOGICAL_DATE_TYPE='B'),SYSDATE,NULL,NULL,'CHHEXT','DL

019',NULL,'my_tab','my_tab_SYS_ALT_TXT','DL',my_tab_ID,NULL,NULL,my_tab_SYS_ALT_

TXT,'R'  

FROM my_tab M  WHERE my_tab_BAN IN 

(SELECT /*+ PARALLEL(HS,4) */

BAN FROM HSI_CLEANUP HS WHERE CATEGORY ='CR019' AND

CONDITION_DESC IS NOT NULL AND NVL(PURGE_STATUS,' ') <>'S')  AND

my_tab_TYPE='7770' AND my_tab_SYS_ALT_TXT IS NOT NULL;

Modified query:

SELECT /*+ PARALLEL(M,8) FULL(M) use_hash(M)  */ 

my_tab_BAN,'CR019',(SELECT LOGICALY_DATE FROM

LOGICALY_DATE WHERE LOGICAL_DATE_TYPE='B'),SYSDATE,NULL,NULL,'CHHEXT','DL

019',NULL,'my_tab','my_tab_SYS_ALT_TXT','DL',my_tab_ID,NULL,NULL,my_tab_SYS_ALT_

TXT,'R'  

FROM my_tab M  WHERE my_tab_BAN IN 

(SELECT /*+ PARALLEL(HS,4) */

BAN FROM HSI_CLEANUP HS WHERE CATEGORY ='CR019' AND

CONDITION_DESC IS NOT NULL AND NVL(PURGE_STATUS,' ') <>'S')  AND

my_tab_TYPE='7770' AND my_tab_SYS_ALT_TXT IS NOT NULL;