[Oracle-l] High Memory Usage

Hi,

Yes, pga_aggregate_target is kind of a soft limit.
For certain memory requests it will have no other option to give memory to the process requesting it.

You can see the current allocation and how many times oracle had to exceed the pga_aggregate_target limit in v$pgastat.

Regards,

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

---
From: oracle-l-bounce_at_freelists.org On Behalf Of Stalin
Sent: vrijdag 26 augustus 2011 10:02
To: oracle-l
Subject: High Memory Usage

All,

I'm trying to figure out high memory usage on one of the DB servers we have and wondering if pga_aggregate_target could ever exceed from what's allocated.

We have sga_target set to 16G and pga_a_t set to 2G, however from statspack, i see pga usage to be close to 3G. Is that normal. I have posted relevant sections from statspack for reference.  Though i see only 59% of total memory being used, vmstat reports 29G being used. Any idea what could be causing it. This node is a dedicated DB server and only one DB runs on it.

Memory Statistics                       Begin          End
~~~~~~~~~~~~~~~~~                ------------ ------------
Host Mem (MB):     32,640.0     32,640.0
SGA use (MB):     16,315.7     16,315.7
PGA use (MB):      2,854.4      2,981.5
% Host Mem used for SGA+PGA:         58.7         59.1
-------------------------------------------------------------
Instance Activity Stats
workarea memory allocated                   0        6,043

Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
workarea executions - onepass                      0            0.0          0.0
workarea executions - optimal                  7,635            2.1          0.0

Thanks. Checking v$pgastat, maximum pga allocated has been around 3.5G,
still that doesn't add up to 29G.

Any other ideas?

any chance filecache/pagecache taken by os

what sort of filesystem are you using?

thanks

hi

since its solaris you could try run prstat -a, this will show you the memory
consumed by each OS user

thanks

That brings back some fun memories. :) I once had a SysAdmin complain that
Oracle was using 100% of the memory on a Sun box, which was equipped with 64
GB of physical memory and configured for 8 GB of swap space. The "prstat
-a" output, which he helpfully included in his email, clearly showed Oracle
consuming 1.5 TB of memory.

Needless to say, that command has absolutely no knowledge of shared memory.

what version of solaris?

this prstat -a output from our server which has 192 GB physical memory

NPROC USERNAME SWAP RSS MEMORY TIME CPU

1420 rac10gr4 84G 80G 42% 287:44:04 15%

ps -ef|grep rac10gr4|wc -l

1423

it takes into consideration shared memory

It's been awhile, so it might have been a Solaris 8 box. I didn't think
this behaviour had changed under Solaris 10, but could certainly be
mistaken.