Re: Memory Usage in Oracle

EscVector wrote:
> 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
I> 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.

I think you'll see high uga if cursors are the issue:

select sid,name,value

   from v$statname n,v$sesstat s
where n.STATISTIC# = s.STATISTIC#
and name like 'session%memory%'
order by 3 asc; Received on Fri Jan 12 2007 - 21:18:17 CST