asked on

Oracle 10G database server very slow TNS listeners

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.

Apurva Bhandari

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 Geerlings

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.

johnsone

Why three listeners? That seems odd to me.

Also, check the listener log files. If they get too large, I have seen slowness happen.

schwertner

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 G

how many connects are you getting per sec ?

Scott

ASKER

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?

johnsone

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.

Scott

ASKER

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

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

Scott

ASKER

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?

johnsone

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.

Scott

ASKER

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

Any idea on how to clear these inactive dead sessions?

johnsone

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 Geerlings

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.

Scott

ASKER

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.

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schwertner

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 Simmons

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