JL Computer Consultancy

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.

Raw Output

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
复制

Sample Output

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)
复制