Oracle Processes Consuming All Virtual Swap

[xwin_1]

I have two, identical rp4440's. One houses production databases and the other houses development.

Here are some memory stats on Oracle 9i processes on the prod server:

ora_pmon_prod - vm: 96.2mb - rss: 41.0mb
ora_smon_prod - vm: 97.6mb - res: 29.6mb
ora_arc0_prod - vm: 163.3mb - res 32.8mb

swapinfo -tam from the prod server reports:
Mb Mb Mb PCT
TYPE AVAIL USED FREE USED
dev 4096 78 4018 2%
reserve - 4014 -4014
memory 5901 2896 3005 49%
total 9997 6988 3009 70%

Now, these servers are the same, right down to identical kernel parameters (which follow Oracle recommendations), hardware, and storage. Moreover, the dev database was created as a datafile clone of prod, and uses the same init.ora config file, also running Oracle 9i at the same patch-level.

Take a look at the memory stats of Oracle processes from the dev machine:

ora_pmon_dev - vm: 873.9mb - res: 123.6mb
ora_smon_dev - vm: 273.1mb - res: 123.2mb
ora_arc0_dev - vm: 303.1mb - res: 128.9mb

Here's swapinfo -tam from this box:

Mb Mb Mb PCT
TYPE AVAIL USED FREE USED
dev 4096 0 4096 0%
reserve - 4096 -4096
memory 6241 4616 2625 74%
total 10337 8712 2625 84%

Across the board, Oracle processes on the dev box take up much more virtual memory and rss than on prod, leading to a lack of virtual swap. This is causing "Could not fork: Not enough space" errors.

Does anyone have an idea as to how memory usage can be so drastically different between these two databases? Any ideas how to reduce memory usage on my dev box?

Thank you!

[Rita C Workman]

Your boxes and Oracle may very well be the same - identical, but that does not mean what is running on it is the same.

You mention it's the development box that is showing issues - extensive memory utilization and fork error. If your kernel parms are same, and are sufficient for a "development" environment, than I'd guess somebody has spawned a nasty test program.

Other thing........I'd add some additional device swap to both those boxes.

Just a thought,
Rita

[whiteknight]

Both of your server swapinfo -tam

the total memory used is > 70%, meaning your system is extremely busy, you may have memory bottleneck.I ncreasing more swap space may help.

What is your current dbc_max_pct and dbc_min_pct?

WK

[xwin_1]

Thanks for the quick response, guys.

@Rita: The prod box undergoes much heavier usage, in terms of client connections, active processes, total processes, etc. than dev. So, if anything, I would expect swap utilization and memory pressure to be worse there. In fact, the opposite is true. Also, when I received the process fork error, swapinfo reported dev usage to be 0%, meaning that no paging to disk had occurred. I don't understand why my 4096mb of true swap on disk wasn't touched _BEFORE_ the error occurred. This makes me believe that adding additional disk swap, while technically the "right" thing to do, will not alleviate the situation.

@whiteknight: I've got a static 800mb of buffer cache on each box.

Any other ideas?

[Rita C Workman]

nfork I'm sure you already know is telling you that you are unable to fork another process - this can be caused by a kernel parm set to low (nproc & associated parms) or your out of swap.

As I mentioned, you don't have any additional swap disk set up. You don't say what your parms are, so it is hard for anyone to comment further on that point.

I appreciate your view that the production box is getting far more activity, however, it's been my experience that one very poorly written little query can bring a box to it's knee's - that or a new developer with lots of poor code. Jobs that die in development don't always release their resources, leaving less and less for the next process.

Hope you get it resolved,
Rgrds,
Rita

[Steven E. Protter]

Shalom,

For oracle servers, 11.11 drop dbc_max_pct to between 7-10 and dbc_min_pct two or three below it.

This decreases buffer cache memory use and prevents major changes which kill performance when they happen on the fly.

Also, take a look at the oracle sga, there may be massive memory savings to be had from there.

If neither can be done, buy some memory.

SEP

[xwin_1]

While I appreciate everyone's answer, I still don't have an explanation as to why rss memory usage is 3x higher and vm usage is 8x higher on the dev box.

[Dennis Handly]

>vm usage is 8x higher on the dev box.

Where do you get 8X? I see under total/USED 6988 vs 8712. Any other numbers are illusions for the bookkeepers.

It appears you may be talking about "Here are some memory stats" instead of swapinfo, where did you get them?