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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jeffrey Beckstrom Sent: Wednesday, August 31, 2005 10:44 AM To: oracle-l_at_freelists.org; oracle-db-l_at_Groups.ITtoolbox.com; ORACLE-L_at_IC.SUNYSB.EDU; oracledba_at_LazyDBA.com; oracle-rdbms_at_yahoogroups.com 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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Aug 31 2005 - 13:09:37 CDT