RE: 9i shared servers (dispatcher) hung

Jeffrey,
 
We get this occasionally, usually when the developers put in a new piece of 
code.  They would bounce that database and call it an oracle problem.  Sure.
 
What happens in our case is that something makes connections and doesn't 
release them, so new connections are impossible.  I have tried killing 
dispatchers, but have found that a better method (not elegant, but it works) is 
to kill some of the oldest shared server processes in order to get a session 
open for myself to do some checking.  Someone recently posted code to keep a 
session open constantly, will implement when I get time.
 
I then have a script to check v$circuit, v$dispatcher (% busy), 
v$queue,v$shared_server, and v$session; it usually shows dispatchers are 
completely busy, v$process is maxed out.   I have another script to dump the 
current sql statement for each connection so I can find the problem.  Then I 
start killing the processes that are causing the problem, usually something 
automated with no checking.  Haven't had to bounce the db since; but have 
increased some parameters to take care of legimate peaks in traffic.
 
HTH,
Mary
 
solaris 8, ora 9.2.0.6

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On 
Behalf Of Jeffrey Beckstrom
Sent: Wednesday, August 31, 2005 10:44 AM
To: oracle-l@xxxxxxxxxxxxx; oracle-db-l@xxxxxxxxxxxxxxxxxxxx; 
ORACLE-L@xxxxxxxxxxxxx; oracledba@xxxxxxxxxxx; oracle-rdbms@xxxxxxxxxxxxxxx
Subject: 9i shared servers (dispatcher) hung


People could not connect via shared server - hung. So tried stopping the 
dispatchers and restarting them. I did:
alter system shutdown immediate 'D000' ; 
alter system shutdown immediate 'D001' ; 

In alert log I see the following.
idle dispatcher 'D000' terminated, pid = (13, 1)

I do not see d001 stopping. If do a lsnrctl services it still shows d001.

If do:
ALTER SYSTEM SET DISPATCHERS=''(address=(host=10.20.7.11)(proto 
=tcp))(dispatchers=2)'';

Then lsnrctl services shows d000, d001 and d002. 

I ended up bouncing the database.  I had to do a shutdown abort since shutdown 
immediate hung - probably due to dispatcher d001 getting stuck.  Any ideas on 
why a dispatcher would get stuck?
 
 
 
Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113

Other related posts: