We help IT Professionals succeed at work.

Oracle 10G database server very slow TNS listeners

429 Views
Last Modified: 2020-09-17
We run an oracle 10g database server running on a windows 2003 server for a web application and we are noticing when our reporting application tries to access the database or we try to access the database through sql developer there are connection wait times up to 6 minutes.

We have 3 TNS listeners on ports 1521, 1522 and 1526 whats strange is our web application running on tomcats uses listener 1526 and thankfully queries are run immediately and never encounter a problem but outside of tomcat we getting a lot of "ORA-12518: TNS: listener could not hand off client connection tips" errors

Since the issue started we rebooted the database server to resolve it and until  it occurred again but it's becoming more frequent and we can't keep rebooting everytime as a solution.

I tried restarting TNS listener service in the services panel but it does nothing to resolve the issue.

Would anyone know how we determine what is happening and how we can resolve it.
Comment
Watch Question

Apurva BhandariLinux/Cloud/DB/DevOps

Commented:
please check the RAM utilisation during issue time.
addition to this need to check thread size cpu load. please check application level any thread is not able to disconnect after transaction will over or not.
also check in application server and DB server that any ipv6 service running or not or any ipv6 configured or not if it is then remove and disable permanently.

i am facing same kind of issue on Linux server but after disabling ipv6 service and ipv6 tables now it is working fine.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
I agree that server business at the time may be the cause.  I would expect that to be CPU utilization near 100% (not RAM utilization).  Also, make sure that you don't have any full (or nearly full) log volumes, or log volumes with a very large number of (possibly small) log files.

Do you have auditing of session connections turned on?  If you do, and if you have your audit_trail parameter set to "OS", that could be creating a very large number of small log files in your: "audit_file_dest" directory.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Why three listeners?  That seems odd to me.

Also, check the listener log files.  If they get too large, I have seen slowness happen.
CERTIFIED EXPERT
Top Expert 2008

Commented:
Check the number of the sessions (v$session view).

Kill the dead session adding the following entry to

 sqlnet.ora:

   SQLNET.EXPIRE_TIME=10
   USE_NS_PROBES_FOR_DCD=YES

Restart the listener(s).
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
how many connects are you getting per sec ?
showisonManger/it

Author

Commented:
The server was set up by someone who didn't really know what they were doing, i inherited management of it about a year ago, how would i check connections or number of session and where would i find the listener logs if it is even creating any?
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Been a while since I worked with 10g, but I believe the default location for the listener logs is in the ORACLE_HOME/network/log directory.  You should be able to stop the listener, rename the log file, then restart the listener.  There is a way to do it without shutting down the listener (not super complicated, but easier if you can just shut it down, if you need the steps let me know).

Alternately, you should be able to use this command:

lsnrctl show log_file

That should give you the current location of the logs.  Not sure exactly what that returned in 10g though.
showisonManger/it

Author

Commented:
Checked the number of sessions

select seq#, event, wait_time, seconds_in_wait, state from v$session where type='USER';

I attached a screenshot of the results returned a lot of the seconds_to_wait were way up in the 120,000 im assuming that many dead sessions slows down the listener

Screen-Shot-2018-07-18-at-12.40.58-p.png
i added the following to my sqlnet.ora and restarted
  SQLNET.EXPIRE_TIME=10
   USE_NS_PROBES_FOR_DCD=YES

I'll wait to see if this shows any results to stop the build up of dead sessions
showisonManger/it

Author

Commented:
Well it took a week but i'm no longer getting slow TNS listeners but i'm getting ORA-12518 immediately, i'm still getting a lot of dead listeners clogging up the listener has anyone got any suggestions?
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
When I have seen that error, it is usually a resource issue.  Number of processes, memory exhaustion, no semaphores, etc.

What is a dead listener?  When the listener dies, it doesn't restart (to my knowledge) and all connection attempts would get a no listener error.
showisonManger/it

Author

Commented:
My apologies i meant dead sessions, i have lot of inactive sessions with seconds_in_wait value above the 400,000 mark, i've checked monitor sessions in SQL developer and ran the query i used to check inactive sessions as the image i've attached shows.

Adding these two lines to sqlnet.ora only means i get ora-12518 straight away instead of waiting 6 minutes
   SQLNET.EXPIRE_TIME=10
   USE_NS_PROBES_FOR_DCD=YES

Screen-Shot-2018-07-25-at-11.27.53-a.png
Any idea on how to clear these inactive dead sessions?
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Try this query:
SELECT sid, 
       serial#, 
       machine, 
       status, 
       event, 
       seconds_in_wait 
FROM   v$session 
WHERE  username = 'VENUE_DBA' 
       AND seconds_in_wait > 400000 
       AND event = 'SQL*Net message from client'; 

Open in new window

If that gives you the list you want to kill, then use this query:
SELECT 'alter system kill session ''' 
       || sid 
       ||',' 
       || serial# 
       || ''';' 
FROM   v$session 
WHERE  username = 'VENUE_DBA' 
       AND seconds_in_wait > 400000 
       AND event = 'SQL*Net message from client' 
       AND status != 'KILLED'; 

Open in new window

That will give you the list of commands to kill them.  However, dead connection detection that you set up already should have killed them.  You need to look at the first query in the STATUS column.  If it shows as KILLED, then it is already dead.  They will still show up in V$SESSION until the session tries to do something and it gets a killed message.

Then figure out why that people are logging in with an apparent elevated privilege account and walking away.  400,000 seconds is over 4 days.  Hopefully, the MACHINE column in the first query will help determine who is doing it.  Be really sure before you start killing those sesions.  I have seen these are normal connections in applications that use connection pooling.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
I agree with Johnson: just because an Oracle database session is inactive, doesn’t mean it is dead.  Most sessions are inactive most of the time - that is normal.
showisonManger/it

Author

Commented:
i've worked out it's a tomcat connection pool issue, JDBC thin client isn't releasing connections which explains why tomcat isn't having any problems while locking other programs out.

How would i go about increasing the connection/session limit if possible?

It may not fix the problem and only allow tomcat to keep open more connections but it may still be handy to know.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2008

Commented:
I have experienced your problems and have collected many docs in 2015.
Your problem isn't Data Base problem. The DB is only the victim.
The 'gunmen" are Tomcat and the application.
Tomcat: has to use connection pooling or to close the connections regularly. Obviously it doesn't do this.
Application: should avoid to initiate new connection for every SQL statement (I am sure this is the case you experience, I have also experienced it). Many applications are designed to close the connection after every SQl statement. the application should be parametrized so that it will be possible to use connection pooling.
So the real answer of your question is - 'address the problem to the application team and Tomcat administrators".
Seth SimmonsLead Systems Administrator
CERTIFIED EXPERT

Commented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- schwertner (https:#a42636380)
-- schwertner (https:#a42636387)
-- johnsone (https:#a42634377)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

seth2740
Experts-Exchange Cleanup Volunteer
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.