Re: intermittent Oracle session explosion

oaksong_at_hotmail.com wrote:
> I've got a situation where a user (the application) is randomly maxing
> the available sessions for no apparent reason. Someone will start the
> application or be doing something in the application and all of a
> sudden the number of sessions assigned to the individual will start to
> multiply at the rate of two to three per second until the max sessions
> is reached. These sessions will remain open until they are timed out.
> They never show as active. And of course, with no sessions available,
> everyone else is blocked from using the application.
>
> The application is Visual Basic against an Oracle release 10 server. We
> have tried to make this happen, but it's totally random as to when it
> occurs. The way the application is constructed it does not maintain an
> open connection to the server. That is, when it needs data it opens a
> connection, gets the data and closes the connection. The app traps any
> Oracle errors and attempts to write them to the database. The only
> error we've gotten that might be related to the event is a TNSListener
> error. "Listener failed to start a dedicated process."
>
> I should also mention that this is a recent development for an
> application and server that have been behaving just fine for months.
>
> Any ideas would be greatly appreciated.
> Chris

Do you have source code for the application? If so, change it so that it is not constantly connecting and disconnecting. It could be that the attempt to log Oracle errors to the server is the cause of the problem - error connecting, so open another connection to report the error - error connecting to report the error, so open another connection to report the error...

You can try tracing at the Oracle client side by adding TRACE_LEVEL_CLIENT = 16 to the client's SQLNET.ORA file. This will slow down the client computer, but it may give a hint as to the source of the initial error message.

The error indicated in the listener log could be caused by the maximum number of sessions being hit, the Oracle process on Windows hitting the 2GB per process limit, or something else (can't archive redo logs). The alert log should show which is the cause of the problem.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Sep 26 2006 - 12:12:26 CDT