Monitor Memory Usage
In Unix, you have many tools to monitor the overall memory usage, such as vmstat, sar, top and free.
Using vmstat Command
you can monitor the field free to see how much memory left now, for example:
$ vmstat 2 5
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr m1 m1 m1 m2 in sy cs us sy id
0 0 0 14999888 3498634 1171 170 2982 2 2 0 0 22 1 0 22 2109 4241 5857 13 8 79
0 0 0 15157608 4000778 4541 3 0 0 0 0 0 0 0 0 0 500 37892 1085 8 6 86
...
The memory unit is KB.
Using top Command
top, you can monitor the 4th line, there is the memory usage.
Memory: 16.0G real, 3.8G free, 9.6G swap in use, 9.8G swap free
Also, you can sort by res to see which users occupy resident memory most. In this case, we got 3.8GB free memory.
Using sar Command
sar, a useful tool that supports almost all Unix platform.
$ sar -r 2 5
...
16:35:33 freemem freeswap
16:35:35 484238 24765460
16:35:37 486605 24886024
...
Average 484732 24789242
The memory unit is in page. On RISC machines, 1 page = 8KB, so the free memory is around 3.7GB.
Using ps -eo Command
So far, you might still have no idea about the memory usage distribution on every process. You can try ps -eo to identify top 10 memory usage processes like this:
$ date; ps -eo pid,pmem,pcpu,rss,vsz,stime,args | sort -k2nr | head
Fri Nov 18 10:59:23 CST 2011
9977 5.5 0.0 548832 517088 Oct_10 ora_arc1_PRIMDB1
18274 5.5 0.0 548480 517088 Oct_10 oraclePRIMDB1 (LOCAL=NO)
9960 5.5 0.0 548200 518976 Oct_10 ora_lgwr_PRIMDB1
9954 5.5 0.0 546952 523680 Oct_10 ora_reco_PRIMDB1
9964 5.5 0.0 545528 509848 Oct_10 ora_dbw0_PRIMDB1
9948 5.4 0.1 541435 509450 Oct_10 ora_smon_PRIMDB1
20843 5.4 0.0 543664 509784 Oct_10 oraclePRIMDB1 (LOCAL=NO)
...Assuming that we have memory pressure currently, and we want kick some processes out of the memory. So let's go further.
KILL SESSION
In our case, there were two processes named oraclePRIMDB1 (LOCAL=NO) which were Oracle connections from outside machines, and are idled in that moment. The rest were Oracle background processes and they cannot be killed.
The two idled connections are dedicated server processes, we should try to ask those users to exit the connections gracefully to release the memory. But the question is: who are the owners of the two connections (processes)?
We can use the following statement to find out whose connections are those:
SQL> SELECT S.INST_ID, S.USERNAME, S.SID, S.SERIAL#, S.LOGON_TIME, S.STATUS, S.TYPE, S.OSUSER FROM GV$SESSION S, GV$PROCESS P WHERE S.PADDR=P.ADDR AND P.SPID IN ('18274','20843');
If the owners are not convenient to exit their connections, we can kill the sessions with owner's permission.
SQL> ALTER SYSTEM KILL SESSION 'SID,SERIAL#,@INST_ID' IMMEDIATE; The above statement is for RAC sessions, if you are running with a standalone database, you can remove the third argument (i.e. "@INST_ID").