Swapping is killing production Oracle database

[Stephen Riddle]

Please help me. I just inherited this system, and it is, for all intensive purposes, down. Attached are the kernel parameters, vmstat output, swapinfo output and top output. They were all taken at the same time. Please help.

[Stephen Riddle]

Sorry, I've been at work for over 60 hours. The system is an N-class with 4 440 Mhz processors, 4 GB RAM and 4 GB of swap. Thank you in advance.

[Curtis Larson_1]

well with vxfsd, syncer, swapper, lvmkd, vhand, etc all at < 1% swapping isn't your problem. I doubt if any I/O is being done at all.

with the 3 processes oraclefinprd running at 99.9% user cpu. I'd guess they are stuck in a very tight loop.

[Sanjay_6]

Hi Stephen,

Take a look at the threads below for more info on system performance,

http://us-support2.external.hp.com/cki/bin/doc.pl/sid=c130b1ab119c8c7f96/screen=ckiSearchResults?mode=id&searchString=A4460661

http://us-support2.external.hp.com/cki/bin/doc.pl/sid=c130b1ab119c8c7f96/screen=ckiSearchResults?mode=id&searchString=KNC061098001

http://us-support2.external.hp.com/cki/bin/doc.pl/sid=bdf044e912cd57e96c/screen=ckiDisplayDocument?docId=200000053279589

http://us-support2.external.hp.com/cki/bin/doc.pl/sid=bdf044e912cd57e96c/screen=ckiDisplayDocument?docId=200000049436468

Hope this helps.

Regds

[Bill Hassell]

AS mentioned, it doesn't look like a memory pressure problem at all. The oracle processes are consuming 100% of the cpu which is most likely what they were told to do. It's rather easy to design SQL queries that consume huge amounts of CPU time. Whether that is what was intended is something for the DBA to decide.

Assuming the system is reasonably patched (swlist -l bundle) with patch bundles from 2001. Selective patching can introduce some idiosyncrasies. But I think knowledgeable Oracle changes will do far more to solve the problem than any kernel tweaking.

[Roger Baptiste]

hi,

The stats you showed do not indicate a swap load on the system. for eg: swapinfo shows enough free swap space. It seems there are three oracle processes hogging CPU load and that can slow down the system. These processes could be Valid ones or they could be poorly written sql scripts. Check with the DBA on what those three processes (sitting on top of top) doing.

Use glance tool to gain more info on the usage of the system. glance is more reliable and exhaustive than top .

HTH
raj

[Dennis J Robinson]

Looks like you where showing large amount of CPU utilization out of Oracle.

Oracle dba's can reduce CPU utilization even out of processes which do not do physical I/O's by minimizing the amount of logical I/O's they do.

Queries need to be optized to minimize cpu resources.