Re: PGA Memory Leak - crashing Server
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: bnsarma@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Sep 2007 00:46:35 -0700 (PDT)
----- Original Message ----
From: BN bnsarma@xxxxxxxxx
Its a sort of connection pooling implemented by app.
App opens connections to the database, and keeps them open to service the
requests.
---
BN
It sounds like there may be a "problem" (design feature/fault) in your code.
When a particular piece of code is executed, it causes the PGA to grow. Because
you have a connection pool, that piece of code can be (more or less randomly)
executed by any of the open sessions. So eventually it will be executed by all
of them. From your description, it's a one off "leak" - ie the PGA inflates
just once for each session, then stays there, rather than continuing to grow.
First you need to identify what application operation is causing this to happen
(and obviously your recent dev change should point you in the right direction).
Use your application logging - it is instrumented, right? - to track down the
root cause. Do you have large operations on XML (as another poster suggested)?
Large PL/SQL collections (eg any kind of in-process caching)?
Once you've identified the problem, consider isolating that operation into a
separate pool of connections. Then instead of having 100 sessions which ALL go
up to 715Mb, you can have a pool of say 10 which do, and 90 which don't. You'll
need to identify which operations need access to the 'family size' connection
pool, and which can use the regular pool. So for example if you are building a
cache, anything which depends on the cache should use the new pool.
Regards Nigel
Other related posts:
