RE: relation shared pool elements phys mem

From: <J.Velikanovs_at_alise.lv>
Date: Fri, 6 Aug 2004 16:59:44 +0300
Message-ID: <OF2910BF32.58D79444-ONC2256EE8.004B14EC-C2256EE8.004DA3BB@alise.lv>

>The large pool is an optional memory structure used for Shared Server
(MTS),
>RMAN and PQ operations. It has to be big enough to hold everything it may
>need as it has no LRU list.
>
>You should try to discover what sessions are running out of memory and
why.
>(Metalink ID:1070975.6 may help).

Hi,

I have related question to the list,
.
Possible we have detected the session.
Question is: How to detect what is the reason of leaking memory? .
I have some indirect ideas, may be some of you better to offer: 1. Check session for opened cursors:
select

        n.name, 
        s.value 
from 
        v$sesstat s, 
        v$statname n

where s.statistic#=n.statistic#
and n.name = 'opened cursors current'
and s.sid=<SID number>;

select HASH_VALUE, count(*) from V$OPEN_CURSOR where sid=<SID number>
group by HASH_VALUE;
.
2. Check UGA/PGA sizes for particular session select

        n.name,
        s.value
from
        v$sesstat s,
        v$statname n

where s.statistic#=n.statistic#
and n.name in (

'session pga memory',
'session pga memory max'  ,
'session uga memory'      ,
'session uga memory max'

)
and s.sid = <SID number>
/

3. ????

But still not enough for telling that is the root cause of leaking. Can any body add some hints to the list?

Thanks in advance,
Jurijs
+371 9268222 (+2 GMT)

Thank you for teaching me.
http://otn.oracle.com/ocm/jvelikanovs.html

On 06.08.2004 12:54:59 oracle-l-bounce wrote:

>Jeroen
>
>The large pool is an optional memory structure used for Shared Server
(MTS),
>RMAN and PQ operations. It has to be big enough to hold everything it may
>need as it has no LRU list.
>
>You should try to discover what sessions are running out of memory and
why.
>(Metalink ID:1070975.6 may help).
>
>Your SGA should definitely fit into your physical RAM, although Oracle
will
>still run (slowly) even if it doesn't.
>
>You could also post: Version, platform, Physical RAM, SGA size and
whether
>you use MTS.
>
>Ray

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Aug 06 2004 - 09:04:06 CDT