Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Awakening PMON to do its job

Re: Awakening PMON to do its job

From: Nigel Thomas <>
Date: Thu, 15 Feb 2007 08:49:42 -0800 (PST)
Message-ID: <>

Hi You may be solving the wrong problem (or rather, now you've put out the fire, you need to find the cause and stop it happening again). "Mid tier went crazy spawning processes" is often a symptom of session pool madness: In such an application, X number of users share a smaller Y number of Oracle sessions. Everything tootles along happily; Users (midtier threads if you like) loop around: - get a session from the pool - issue one or two SQL - commit/rollback - give the session back As long as the users spend less time in Oracle than they do in the rest of the application (and waiting for user input etc), no problem. Then something goes wrong; maybe a session sits on a lock that everyone needs; maybe a sequence cache isn't big enough and/or you forgot that We Don't Use RAC; maybe you had an SGA problem like ORA-4031 What happens next: - all the Oracle sessions in the pool are busy - next midtier thread asks for an Oracle session - midtier pool manager says "no problem", launches a new Oracle session and adds it to the pool - that session becomes busy - and the next thread, and the next thread, and the next thread... Soon instead of sharing say 100 Oracle sessions across 1000 processing threads, your mid tier has responded to the blockage by adding 900 new sessions to the load. That's probably made the problem worse, not better - kind of like slamming your foot on the accelerator when you see brakelights ahead in the fog. I had exactly this problem performance last year, testing a J2EE app, using OC4J. We hit a 4031 problem (no bind variables in one part of the system) and then fairly immediately the application server did its lemming impersonation as described above. Things to consider: 1) reduce the upper limit on the session pool size (definitely to below your Oracle processes level!) 2) if possible, slow down the rate of session starts (eg set a delay in the mid-tier session manager) 3) find out what caused the problem in the first case. The good news is that if you dampen down this suicidal behaviour, you probably have a better chance of diagnosing the root cause next time. HTH Regards Nigel ----- Original Message ---- From: "" <> To: Sent: Thursday, February 15, 2007 2:49:34 PM Subject: Awakening PMON to do its job We had an issue affecting one of our production DBs. The middle tier application for some reason, went crazy spawning processes chewing up the DBs process parameter. DB started throwing errors indicating max process exceeded. When I got to the database, I retrieved some of the SPIDs and all did not have client PIDs. At this point, we only option was to bounce the DB which ended up resolving the issue of cleaning up all these process. My question is could've we awakened the PMON process to do its cleaning and how? I know there is away using oradebug but I have not tried it first on any of my play databases to do in this production DB? --

Received on Thu Feb 15 2007 - 10:49:42 CST

Original text of this message