Executing DDL - When session is hung...

Here is one situation I encountered when trying to load data into database using parallel dml.

the load was hung and it was WAITING !!

Identify the process at the session level and see what it is waiting on..


uhg_801142427@IRDST01> select sid,serial#,status,event,wait_class,sql_id
  2  from gv$session
  3  where (inst_id,sid,serial#) in
  4    ( select inst_id,sid,serial#
  5      from gv$px_session
  6      where qcsid = 373 )
  7  order by sid ;
       SID    SERIAL# STATUS   EVENT                                              WAIT_CLASS           SQL_ID
---------- ---------- -------- -------------------------------------------------- -------------------- --------------------
       298      32948 INACTIVE PX Deq: Execution Msg                              Idle                 9593s3p3ftvk1
       304      38750 ACTIVE   statement suspended, wait error to be cleared      Configuration        9593s3p3ftvk1
       318      20599 INACTIVE PX Deq: Execution Msg                              Idle                 9593s3p3ftvk1
       327      35082 ACTIVE   statement suspended, wait error to be cleared      Configuration        9593s3p3ftvk1
       336      22952 INACTIVE PX Deq: Execution Msg                              Idle                 9593s3p3ftvk1
       356      47430 INACTIVE PX Deq: Execution Msg                              Idle                 9593s3p3ftvk1
       359      46791 ACTIVE   statement suspended, wait error to be cleared      Configuration        9593s3p3ftvk1
       364      26433 ACTIVE   statement suspended, wait error to be cleared      Configuration        9593s3p3ftvk1
       373      53151 ACTIVE   PX Deq: Execute Reply                              Idle                 9593s3p3ftvk1
9 rows selected.

looking at the wait event I don't have any clue what's wrong in the database. why the "statement suspended" what "wait error" should be cleared?

peeking down to the session wait statistics I still see the same.

uhg_801142427@IRDST01> select inst_id,sid,event,state,p1text,p1,p2text,p2
  2  from gv$session_wait
  3  where (inst_id,sid) in
  4  ( select inst_id,sid
  5      from gv$px_session
  6      where qcsid = 373 ) ;
   INST_ID        SID EVENT                                              STATE               P1TEXT       P1 P2TEXT               P2
---------- ---------- -------------------------------------------------- ------------------- ---------- ------------ ---------- ------------
         1        298 PX Deq: Execution Msg                              WAITING             sleeptime/    268566527 passes              388
                                                                                             senderid
         1        304 statement suspended, wait error to be cleared      WAITING                               0                           0
         1        318 PX Deq: Execution Msg                              WAITING             sleeptime/    268566527 passes              573
                                                                                             senderid
         1        327 statement suspended, wait error to be cleared      WAITING                               0                           0
         1        336 PX Deq: Execution Msg                              WAITING             sleeptime/    268566527 passes              569
                                                                                             senderid
         1        356 PX Deq: Execution Msg                              WAITING             sleeptime/    268566527 passes              574
                                                                                             senderid
         1        359 statement suspended, wait error to be cleared      WAITING                               0                           0
         1        364 statement suspended, wait error to be cleared      WAITING                               0                           0
         1        373 PX Deq: Execute Reply                              WAITING             sleeptime/      200 passes          478
                                                                                             senderid

9 rows selected.

But the real answer to the problem lies in the DBA_RESUMABLE a very handy view to display information's about resumable statements.

uhg_801142427@IRDST01> select instance_id,session_id,status,suspend_time,
  2    error_msg
  3  from dba_resumable
  4  /
INSTANCE_ID SESSION_ID STATUS    SUSPEND_TIME         ERROR_MSG
----------- ---------- --------- -------------------- --------------------------------------------------
          1        364 SUSPENDED 07/04/13 11:17:43    ORA-01652: unable to extend temp segment by 128 in
                                                       tablespace TS_IRADS_DEFAULT
          1        327 SUSPENDED 07/04/13 11:17:43    ORA-01652: unable to extend temp segment by 128 in
                                                       tablespace TS_IRADS_DEFAULT
          1        304 SUSPENDED 07/04/13 11:17:43    ORA-01652: unable to extend temp segment by 128 in
                                                       tablespace TS_IRADS_DEFAULT
          1        359 SUSPENDED 07/04/13 11:17:43    ORA-01652: unable to extend temp segment by 128 in
                                                       tablespace TS_IRADS_DEFAULT

Now looking at this information in dba_resumable, I am very clear that I was running out of the space in Tablespace and added more space to succeed the data loads.