A question came up on the OTN forum a couple of days ago about a problem with library cache latches and a rapid increase in sessions connected.
The description allowed some room for interpretation, but I read it as saying there had been a one-off incident rather than a new, and ongoing, problem. So I asked for a little clarification and gave an example of a possible cause for a random spike like this.
The oddity is one that can happen quite easily with Web-based applications that haven’t been configured to behave reasonably under stress. I first wrote about this issue nearly two years ago – in a little case study I pointed the OP to as part of my reply.
Be careful when you configure any type of web application server that uses connection or session pooling. Creating an Oracle connection, and then creating a session on top of it can be very labour-intensive. A session needs a big block of memory from the shared pool for the “session parameters”, and if it can’t find it, the side effects can be nasty. If (say) ten web application servers simultaneosly decide that all want 5 new sessions each then the impact on the library cache and shared pool latches can be catastrophic.
A quick and dirty bit of damage limitation – check the setting of your shared_pool_reserved_size parameter, check the value for the sessions parameter, and increase the shared_pool_reserved_size by something like 26KB for every session. This might leave you enough memory in the “reserved” part of the shared pool for all the new sessions when the web application servers have a panic attack. [Ed 2nd Jan 2009: This strategy is no longer relevant if you are on versions 10.2 or later, see comment below from Tanel Poder].
The other thing to do is ensure that the configuration of the web application servers minimises the rate at which new sessions might be needed – so configure it to hold as many sessions as you might need at normal peak levels, and ensure that sessions can only be created or destroyed one at a time, rather than in batches.