memory utilization advice

[Elena Leontieva]

Hi,

We had 4GB of RAM and 6GB of swap configured on N class. I monitored the situation for some time and figured that we want to add memory in order to get rid of swapping. So, 2GB has been added, now we have 6GB RAM, 6GB swap, swapmem_on=1. But the swapping is still here because my DBA did changes to Oracle ( increased SGA?) and we are back to where we were before memory upgrade in terms of swapping.

Any comments/advice?

Thanks in advance.
Elena.

[Pete Randall]

Elena,

Approaching things from the simple side, can you get your DBA to change things back so you can at least see what effect your RAM increase had by itself and then tune from there?


Pete

[Elena Leontieva]

This is my swapinfo -tam output:
cocn03(root):/var/adm/perf> swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 1024 0 1024 0% 0 - 1 /dev/vg00/lvol2
dev 1024 194 830 19% 0 - 0 /dev/vg00/lvol10
dev 4096 193 3903 5% 0 - 0 /dev/vg03/lvol1
reserve - 4825 -4825
memory 5044 654 4390 13%
total 11188 5866 5322 52% - 0 -

DBA is saying that the reserved area for the OS ( 4825mb from a total of 11188mb ) is significantly higher than Oracle's utilization, so that we should consider reducing the reserved area. My understanding is that reserved is needed for processes (not OS) and I have no control on how much goes in reserve.
Pete, I do not think we can go back, SGA is 1.7GB.

Elena.

[doug mielke]

Assuming you're running 64 bit Oracle. (otherwise SGA limit is around 2 gig) you could consider lowering (very low) your disk buffers. If the SGA is large, you may not need them, since Oracle will keep them in memory anyway.

We've seen great improvements when raising SGA. (I wonder if the improvement is enough to tolorate the swapping)

[A. Clay Stephenson]

It is possible that you are running dynamic buffer cache and the default value of 50% is in play. 11.11 boxes typically perform best when buffer cache is limited to about 800-1200MB; 11.0 boxes typically optimize at somewhere between 400-800 MB -- and that is generous. Tune your kernel accordingly. My preference is to hard set the buffer cache by setting bufpages to a non-zero value but that's just my blind and simple approach. Silly me, I actually bother to measure these things.

If this were me, I would reduce shmmax so that the DBA can't pull this stunt again -- at least without letting you know beforehand.

I can absolutely assure you that any gains the DBA might have thought he achieved by increasing his cache are more than offset (by about 2 orders of magnitude) by the penalty of swapping.

[Elena Leontieva]

Hi,

It is 11.00. Buffer cache is hard set to 9.8MB (nbuf = 0, bufpages = 2400). I wish I can understand how he grabbed the memory.

Elena.

[Volker Borowski]

Elena,

is this a standalone database server or is there additional application server software on it ? If yes, what (i.e. SAP ?)

If not, how many clients connect to the database and are these connections either handed by dedicated or multithreaded server processing ?

Check the output of top for the number of oracleSID processes and allocated memory for each process. Also compare the number to

select count(*) form v$session;

If they are close and high, you run dedicated and may consider to change to multithreaded configuration, which will reduce the number of oracle processes and the related process/context switching on OS level. Check your application documentation, if this change is permitted (with SAP it is not !).

Hope this helps
Volker

[A. Clay Stephenson]

9.8MB of buffer cache is much too small. That doesn't allow much buffer for often used files. I would increase it to at least 200MB (bufpages=51200) and double that would not be bad at all.

The way he can "grab" all that memory is that he is allowed to. You as sysadmin can limit his per process grabs by restricting maxdsiz, maxdsiz_64bit, (and to a lesser extent) maxssiz and maxssiz_64bit, and shmmax.

You can restrict those values (it's probably just shmmax) and when he attempts to start the database he will fail and be forced to reduce his init.ora parameters.

[Elena Leontieva]

It is Oracle 8.1.7 and a purchasing application (KPMG) on top of it.

shmmax = 1700000000
maxdsiz=maxdsiz_64bit=1073741824

and were setup this way as per DBA request at the Oracle/application upgrade.

Elena.

[A. Clay Stephenson · Accepted solution]

Your shmmax is 1.7GB so you can't support many large instances without swapping. There is nothing wrong with even much larger shmmax settings as long as you don't have users which abuse memory usage. NEVER listen to DBA's about memory usage; his comment about the reserve swap area being for the OS is a prime example. Reserve is not reserved for the OS but rather memory that might be needed by currently running processes but has not yet been allocated from one of the swap areas.

The fundamental answer to your problem is:
1) add more memory
2) reduce the current SGA usage
3) reduce the number of currently running processes
4) tolerate the swapping.

Any combination of the above can apply but 4) would be my least favorite.

[Elena Leontieva]

Gentlemen,

DBA is going to reduce ora.init parameters, probably to pre-upgrade values.

I appreciate all your thoughts!
Elena.

[Tim Adamson_1]

Hi Elena,

I know I have come in on the end of this thread, but thought I would offer my 2 cents worth. If you dont want it, donate it to charity if you like :-)

As mentioned the buffer cache is WAY to small. By setting bufpages (or nbuf) to non-default values, you are not using dynamic bufer cache (ie, dbc_max_pct and dbc_min_pct are NOT used). The buffer cache is set to the bufpages value. I would recommend a buffer cache of at least 400Mb (and possibly higher). You would need to monitor things and increase it if necessary. I would also set bufpages back to the default (NBUF * 2) and use dynamic buffer cache but set dbc_max_pct to say 10 (this would mean a maximum buffer cache of 600Mb).

Secondly, I would add more swap. If you have lots of processes and they are reserving all your swap, then add more swap. The machine is there for people to use, not to be restricted.

The value of shmmax is quite large. Does it really need to be so big? It should be set to a value that is larger than the largest request the system should grant. By setting shmmax to a large value, you risk having a
process erroneously try to allocate a huge segment and consume all available swap space, only to be denied the request. Maybe this is what is happening in your case. Does the DBA really understand shmmax? Maybe he is confusing it with shared memory.

Anyway, just a few points. I'm sure I've added to the confusion and will keep a watch for the 2 cents when it is thrown back at me ;-)

[Carlos Fernandez Riera]

The amount of memory (reserved) show by swapinfo is only 4825, so in fact, it is enought memory. Maybe at any time, any process has used lots of memory.

I think you must change the point of view:

1-) Check the memory use: see how it grows and fall in a day basis. You will see any peaks, at online time or/and bacth time. How long are they?. At these times are you experienced performance issues?.

2-) There are some patches for memory leaks. One of them is mib2agt. It can consume lots of memory.

3-) I am surprised. Anybody has write about the use of :

UNIX95= ps -aef -o "pid,user,comm,sz,vsz" | sort -k 4n .

It is very useful to see the per process used memory. See man ps, or better, search UNIX95= in the forums.

Now you can see if your DBA is right or not.