Re: Memory Usage in Oracle

jeffchirco_at_gmail.com wrote:
> Of those 400 connections 20 of them are active at one time. And 18 of
> those oracle's own connections. So really only 2 connections are
> active at one time.

Well 400 may not be too much, but I've run some pretty big systems, millions of users, and I usually don't have 400 concurrent connections.  50 or 60 seems like a lot to me. The reason, the application usually pools connections. Say we have 25 pooled connections and each connections handles 4 calls for every few seconds. That means I'm really getting 100 users worth of connection for every 25 processes. Less work on cpu to generate connection ports, memory allocation, etc. MTS was invented for this, but was replaced with more efficient application and hardware connection pooling.

So in your situation it seems like oracle is eating up memory not because each process must be allocated pga, which is upper bounded by by sga_max_size, but because you have open cursors hanging out there in unused sessions, and I believe that there is no upper bounds on the cursor size unless it is constrained by code, i.e not selecting more than is required or usable by a user in one screen... reports are another issue...

I'd explore the views that will list if you run the following: select * from dictionary where table_name like '%CURSOR%'; select * from dictionary where table_name like '%WORK%';

Basically you are looking for the memory used by each session. Grid has all this too.

My bet is that if you can get rid of the unused, dead, or old connections, your memory "issue" will go away. Received on Fri Jan 12 2007 - 20:50:40 CST