From: Brewer, Edward (BREWERE@OD.NIH.GOV)
Date: Wed Apr 17 2002 - 10:47:58 EDT
Admins,
Thanks for all of the great responses. Ultimately the listener was
bounced and this cleared up the problem. We found while running truss on
the listener process that there were several process that were eating away
at the listener. In particular we found several errors listed in the sqlnet
log file occurring on a regular basis. It appears that a process may have
been constantly failing to make a connection to the database and keeping the
listener busy. BTW the oracle tnsping was issued from the server where the
database was running...and what we would see is a listing of good ping times
...20-30 ms then 2000-5000 ms for a few then back to a string of good times.
Here are a few of the responses.
>From Udo be Boer:
The listener should not be using so much cpu. The listener only redirect
incoming oracle connection to other oracle processes. But do a "lsnrctl
services SERVICENAME" where SERVICENAME is the naam of the listener.
Most of the time this is "listener" but use "ps -aef |grep lsnr" to see
the name or ask your dba. This will show the connected users.
>From Joe Hatchel:
The two tns processes are the listener processes.
Having two processes means you have two listeners
running. Their purpose is strictly as a broker
between the client and the database. Upon recieveing
a request on the designated port, it spawns a user
process (the ones with the instance name in them
ora_PROD) then connects the user to the process. It
then goes back to listening for more connections.
Having alot of ora_PROD process is normal and should
correspond (loosely) to the number of connected users.
Of course there are a bunch of Oracle things that
could make this more complicated.
Back to the problem, your listener should not be cpu
intensive, even with several hundred connections. Is
this a web app with lots of connects/disconnects?
First place to look is the listerner log file. Its
found in ORACLE_HOME/network/log/listener.log , where
ORACLE_HOME is the base of where the oracle software
is installed. Look there first and if necessary have
the dba trace from the client and the server durring
connection. The ping response is good. Did you ping
using the same nomenclature as found in the
tnsnames.or file?
I know this is alot to through at you, but it seems
initially to point to an oracle issue. As I dba who
is also a sysadmin I can say this, don't let you dba
off the hook.
>From Alex Gorbachev:
Your normal troubleshooting route would be to first view the active
sessions within the instance (v$session), - you may have a lot of dead
connections, in which case you need to explore Dead Connection Detection
and Listener Timeout settings.
You should also turn listener and client tracing on and turn timing on
(assuming this is Oracle >8.1.7) and watch various steps a connection goes
through to see if you can find a problem gap.
Lastly thanks to Ing. Raul Sossa Sossa. for sending me a set of steps that
would gather information for analysis
I will leave them out so that you won't be flooded with tar balls from other
people.
To all, thanks again,
Rock on,
Lee
This archive was generated by hypermail 2.1.7 : Sat Apr 12 2008 - 10:48:38 EDT