[wesguez]

I am having a issue of OS memory crunch.The sever has 16Gb RAM, and hosts 2 db.The total sga+pga combined is ~10.5GB.For one of the database, i see in TOP command, RES column values like 1100M,1245M , though these sessions are inactive and the logon_time is of 9th Feb.The sessions are also having open_cursor.Can this is a bottleneck?Can snipping these sessions be useful?

How to find why the cursor is still open for a session.I have a session with a logon date on 29-JAN-2010, which is INACTIVE and has 1 open_cursor.This session is having the maximum cpu usage,though inactive.How do i go ahead for analyzing it.

waiting for your response.

[Amit Bansal]

Hi,

As per your statement, you are observing memory statistics from TOP command. Please note that TOP output contains component of shared+private memory, so you cannot rely on that figure ( to confirm if you add memory for all processes it will exceed the total memory on system). There are some Metalink/MOS docs regarding same. You can use pmap (solaris and linux) or svmon to know the exact memory usage of process.

I would suggest digging into Statspack/AWR report to find the bottleneck.

w.r.t to a process consuming high CPU, check if it is a Background process. You can look at program column in V$process

select s.sid,p.spid,s.username “SUSER”,p.username “PUSER”,s.osuser,p.program,s.status from v$session s ,v$process p where s.paddr=p.addr

[upratap]

Check the memory usage, as seen from oracle. Check if any process is consuming high memory. then we can check what that session is doing.

col name format a30

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;

One thing regarding the inactive sessions is, it is better to set IDLE_TIME in PROFILE or set up DCD(Dead Connection Detection) if it is the problem of client being disconnected and sessions still there.. This helps in cleanup of such sessions..

-Pratap

[Author]

Posts