Quick Tips

resumable_timeout

Today, one of our test guys said that their process was hang up and waited for a log time. The process is was very simple: parse the file, do some calculation and insert the Oracle database.

when we look up with top command on unix side

Resources PID:      2034, rlh          PPID:     29202 euid:   119 User:xxxx00

——————————————————————————–

CPU Usage (util):       0.0 Log Reads :      na Wait Reason    :     JOBCL

User/Nice/RT CPU:    0.0 Log Writes:      na Total RSS/VSS  :  3.34gb/ 3.49gb

System CPU      :        0.0 Phy Reads :       0 Traps / Vfaults:       0/      0

we look explanation of wait reason JOBCL  HP-UX guide says:

Definition of JOBCL as you know is waiting for tracing resume, debug resume, or job control start.
A background process incurs this block when attempting to write to a terminal set with “stty tostop”.

[[email protected]:DB]tusc -p 8723
( Attached to process 8723 (“rlh”) [32-bit] )
[8723] read(0, 0x7fff6948, 1) ………………………. [sleeping]
process is sleeping

it seems that the process was stopped and waiting for resume command. We looked at what happened on oracle side.

SQL>select s.sid
from v$process p, v$session s
where p.spid= 2034
and   s.paddr=p.addr;
–1555
SQL>select event from v$session_wait sid=1555;

EVENT
—————————————————————-
statement suspended wait error to be cleared

“Statement Suspended, Wait Error To Be Cleared” Wait Event (Doc ID 761848.1)

So yes RESUMABLE_TIMEOUT initialization parameter is set to non zero value and when we looked at ASM diskgroup we see it was exhausted and no space on it.

After dropping some files, the process could continue without a problem.