Database Reference
In-Depth Information
At the time when the stack was dumped, the server process was waiting for a network
packet from the client in the routine
nttrd
. The wait event
SQL*Net message from client
in
V$SESSION
confirms this.
SQL> SELECT p.spid, s.event, s.state
FROM v$session s, v$process p
WHERE s.username='NDEBES'
AND s.paddr=p.addr;
SPID EVENT STATE
------------ --------------------------- -------
6524 SQL*Net message from client WAITING
The routine
nttrd
waited for the UNIX system call
read
to return. Using the system call
tracing utility
strace
reveals that the
read
system call was done against file descriptor 13, which
represents a socket connection between the traced dedicated server process and the client.
$ strace -p 6524
Process 6524 attached - interrupt to quit
read(
13
, <unfinished ...>
Process 6524 detached
$ ls -l /proc/6524/fd/13
lrwx------ 1 oracle oinstall 64 Sep 9 13:08 /proc/6524/fd/13 ->
socket
:[4514905]
HANGANALYZE
The command
HANGANALYZE
performs a hang analysis dump. Such dumps are taken to diagnose
database hanging issues. Please refer to the very extensive Metalink note 61552.1 for more
information on this topic. Following is an example of a level 1 hang analysis dump. The scenario
was as follows:
1.
Session 141 (
V$SESSION.SID=141
) executed
LOCK
TABLE
IN
EXCLUSIVE
MODE
on a table.
2.
Session 147 tried to insert into the same table.
The
INSERT
statement executed by session 147 had to wait for session 141 to release the
table lock.
3.
The resulting hang analysis dump is shown next. Chains list waiting sessions. Chains that
contain more than a single session indicate that the session at the head of the chain (on the left)
is blocking other sessions in the same chain.
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : <cnode/sid/sess_srno/ospid/wait_event> :
<0/141/47/3580/No Wait> -- <0/147/518/6064/enq: TM - contention>
Other chains found:
Chain 2 : <cnode/sid/sess_srno/ospid/wait_event> :
<0/145/117/5244/jobq slave wait>
