Home » SQL & PL/SQL » SQL & PL/SQL » More stale connections
More stale connections [message #277882] Thu, 01 November 2007 01:37 Go to next message
nonstopkaran
Messages: 9
Registered: September 2007
Location: chennai
Junior Member
Hi All,

My application hangs every 15 to 20 days..

The number of stale connection keep on incressing (i.e) CLOSE_WAIT.

Once it reaches a particular size i suppose it hangs..

Is there any way to kill a stale connection which is not active for more than 5 min.

And how can i rectify it. and why is it happening.

WIll it happen if a cursor is not properly closed.....
Re: More stale connections [message #277888 is a reply to message #277882] Thu, 01 November 2007 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use profile but this will not solve your application problem.
As this is not an Oracle problem but an application one, so investigate your code.

Regards
Michel
Re: More stale connections [message #277892 is a reply to message #277888] Thu, 01 November 2007 02:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What are you using to connect to Oracle? A colleague once had the same problem with a VB.Net app. From memory, there was a documented bug with a particular setup that he found a workaround for by searching the Web.

Ross Leishman
Re: More stale connections [message #277893 is a reply to message #277882] Thu, 01 November 2007 02:07 Go to previous messageGo to next message
sandeep.kushwaha
Messages: 10
Registered: October 2007
Location: India
Junior Member

You can schedule a job to identify the stale sessions and kill them. The below query can be used to identify those sessions which are inactive from last 5 minutes.

SELECT 
  sid,
  serial#,
  ROUND(LAST_CALL_ET/60) wait_mins
FROM 
  v$session a
WHERE 
  status='INACTIVE' AND
  TYPE='USER' AND
  LAST_CALL_ET>300;
Re: More stale connections [message #277896 is a reply to message #277893] Thu, 01 November 2007 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why using this workaround when there already is a built-in way to do it?

Regards
Michel
Re: More stale connections [message #277899 is a reply to message #277896] Thu, 01 November 2007 02:19 Go to previous messageGo to next message
sandeep.kushwaha
Messages: 10
Registered: October 2007
Location: India
Junior Member

Thanks for the information.
Can you please tell which built-in can be used?
Re: More stale connections [message #277900 is a reply to message #277882] Thu, 01 November 2007 02:22 Go to previous messageGo to next message
nonstopkaran
Messages: 9
Registered: September 2007
Location: chennai
Junior Member
thanks for all ur update.

front end is j2ee. 60% what i believe is that there is less possibility for it to be an issue in the front end application.

because there were no entry inthe logs.. logs stay cool.
i suppose it to be an issue with the DB coding may be.

there are many cursors which are not closed. Will it be an issue.

and what is the built in way to find the stale connections.

Re: More stale connections [message #277905 is a reply to message #277899] Thu, 01 November 2007 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sandeep.kushwaha wrote on Thu, 01 November 2007 08:19

Can you please tell which built-in can be used?

Profile -> idle time limit

nonstopkaran wrote on Thu, 01 November 2007 08:22

because there were no entry inthe logs.. logs stay cool.

If the application hangs it is normal there is nothing in the log.

Quote:

i suppose it to be an issue with the DB coding may be.

Activate trace on sessions and see what they are doing, I bet they are waiting for the application to send them work.

Regards
Michel

Re: More stale connections [message #277915 is a reply to message #277882] Thu, 01 November 2007 03:28 Go to previous messageGo to next message
nonstopkaran
Messages: 9
Registered: September 2007
Location: chennai
Junior Member
Hi CAdot,

Can you explain the first and third points in detail...

Is that idle time limit a parameter to be checked or a column in any table.

HOw to activate trace on sessions.

Thanks in advance
Re: More stale connections [message #277919 is a reply to message #277915] Thu, 01 November 2007 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Database Concepts, Chapter 20 Database Security, Subsection User Resource Limits and Profiles
SQL Reference, CREATE PROFILE

2/ Pete Finnigan, How to set trace for others sessions, for your own session and at instance level

Regards
Michel

[Updated on: Thu, 01 November 2007 03:58]

Report message to a moderator

Re: More stale connections [message #277929 is a reply to message #277919] Thu, 01 November 2007 04:35 Go to previous messageGo to next message
sandeep.kushwaha
Messages: 10
Registered: October 2007
Location: India
Junior Member

Thanks Michel
Re: More stale connections [message #277970 is a reply to message #277929] Thu, 01 November 2007 05:58 Go to previous messageGo to next message
nonstopkaran
Messages: 9
Registered: September 2007
Location: chennai
Junior Member
many thanks michel. let me work on the same and will come back with the status.
Re: More stale connections [message #279029 is a reply to message #277970] Tue, 06 November 2007 12:10 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
And something for the Java guys...
http://blogs.oracle.com/olaf/2007/10/24#a134
Previous Topic: Action done by client on same date of service as a specific procedure code
Next Topic: Overlap Issue
Goto Forum:
  


Current Time: Sat Dec 03 20:07:51 CST 2016

Total time taken to generate the page: 0.24211 seconds