From time to time Oracle comes out with a new implementation that results in shadow processes (particularly parallel query slaves) running up a massive memory demand. From time to time I have shadow processes running up a memory demand of 400Mb in extreme cases (mixing partition views with bitmap indexes) and commonly demanding in excess of 30 Mb above base memory requirements.
To help one DBA keep track of memory intesive processes, so that he could kill them, or at least call their owners, I produced the following little utility for Unix boxes with the correct version of ps.
If you execute ps -efl on a typical Unix box the output will look something like this:
10 S oracle 6840 1 1 40 10 0 17154 804c601c 08:29:24 ? 1:36 ora_p002_PROD 10 S oracle 9698 1 0 74 10 0 17574 804c607c 10:01:59 ? 5:00 ora_p005_PROD 10 S oracle 9696 1 0 68 10 0 17146 804c6070 10:01:58 ? 3:40 ora_p004_PROD 10 S oracle 12259 1 0 50 15 0 16933 804c5fec Jan 27 ? 0:09 ora_snp0_PROD 10 S oracle 6842 1 0 65 10 0 16955 804c6028 08:29:28 ? 0:01 ora_p003_PROD
The following script takes advantage of this, using ps, grep, and awk to produce a report of the form shown at the bottom of the page which lists:- process id, total memory demand, memory over a given limit, process name.
This is a very temperamental script, however; I find that awk and ps are two of the most platform-specific Unix tools around, and they tend to differ in various minor ways on different machines. You may find that you need to use nawk instead of awk, and you may find that the ps command produces a different set of columns in a different order, and the memory size is reported in page sizes other than the 4K assumed in this script.
#!/bin/ksh # # Script: check_mem.sh # Author: J.P.Lewis # Last Update: 7-Jun-1998 # Purpose: Spot memory intensive Oracle PQ slaves # # With options efl, # column 4 is the process id (usually) # column 10 is the memory in 4K blocks (probably) # the last column (column NF to awk) is the process name # # Typically I was seeing 66MB taken up by a shadow as it started. # (This changes as the init.ora changes the SGA) so for convenience # the program restricts itself to reporting the processes with more than # this level of usage and prints the number of MB over 66 that a process held. # # To change this, alter the values 16000 (4K pages) and 66 (Megabytes) to # reflect your typical Oracle usage. #
ps -efl |
grep "ora_p[01]" |
awk '{ if ($10 > 16000) {
printf ("%6i\t%8.2f\t%8.2f\t%-28s\n", \
$4, 4*$10/1024, 4*$10/1024 - 66 , $NF \
)
}
}' |
sort
ps -efl |
grep "oracle${ORACLE_SID}" |
awk '{ if ($10 > 16000) {
printf ("%6i\t%8.2f\t%8.2f\t%-28s\n", \
$4, 4*$10/1024, 4*$10/1024 - 66, $NF \
)
}
}' |
sort
18634 66.97 0.97 ora_p000_PROD 18416 69.59 3.59 ora_p001_PROD 18509 66.73 0.73 ora_p002_PROD 18636 68.73 2.73 ora_p003_PROD 18446 79.44 13.44 ora_p008_PROD 18448 66.72 0.72 ora_p009_PROD 18461 80.74 14.74 (LOCAL=NO) 18603 67.65 1.65 (LOCAL=NO) 18672 66.37 0.37 (LOCAL=NO)