Re: Awakening PMON to do its job
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: cnewtonne@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 15 Feb 2007 08:49:42 -0800 (PST)
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: "cnewtonne@xxxxxxxxx" <cnewtonne@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
