Re: intermittent Oracle session explosion

Is this a case where the application "closes the connection", but a background library is actually keeping a pool of connections open, and dynamically increasing it in response to the incoming load ? (i.e. typicaly web application scenario).

If so, you may be experiencing the problem that occurs where the a real new connection needs a large chunk of contiguous memory from the shared pool and starts to flush the shared pool to get it. This can take time if the system has become busy, and the resulting lag in response causes the front end to start firing up more new sessions. These then hammed the shared pool and library cache latches as they try to demand their large contiguous chunks of memory. This locks up the system for everyone, causes the front-end panic and send in lots more new connection requests.

Eventually no more sessions can connect, the shared pool has been cleaned out, and the incoming connections get in - and have nothing to do.

Check, immediately after a freeze, if the shared pool has a very large free memory component. If so, modify the parameters for the connection library you are using so that it is more cautious about the number and rate of new connections it tries to make; and consider increasing the size of your shared_pool_reserve to allow for about 26KB * sessions plus a few dozen MB - which should help to protect your shared pool from a logon storm.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


<oaksong_at_hotmail.com> wrote in message 
news:1159288666.136260.9090_at_i42g2000cwa.googlegroups.com...


> 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

>