检测SOLARIS上的ORACLE PROCESSES的内存使用量

检测SOLARIS上的ORACLE PROCESSES的内存使用量

这篇文章是翻译的，加入了一些我的例子和解释。看原文的到直接到文章最后

目的
-------------------------
检测SOLARIS上的ORACLE PROCESSES的内存使用量。

适用的范围
--------------------------
本篇文章介绍给系统管理员或数据库管理员，如何对ORACLE在SOLARIS系统上的内存使用进行监控。

对ORACLE进程类型的理解
----------------------------------------

首先需要了解的是ORACLE后台进程。
ORACLE后台进程同INSTANCE一起创建，在操作系统上的命名规则是“ora_ProcessName_SID”。SID是ORACLE_SID。例如：SID是ora817的PROCESS MONITOR后台进程的名称是“ora_pmon_ora817”。
另外需要注意的类型是用户或客户端的进程。它们的命名规则同启动程序的名称相同。例如SQLPLUS程序的名称就是“sqlplus”。
另一种进程类型是ORACLE的服务器进程（server processes） 或叫影子进程（shadow processes）。server processes 接受用户进程的请求，访问数据库实例完成请求。在dedicated模式和MTS模式下，分别对应一个或多个用户进程。命名规则是“oracleSID”，例如“oracleORA817”是ORACLE_SID为ora817的服务器进程名。

复制内容到剪贴板

代码:


# ps -ef|grep ora
oracle6372 10 Aug 06 ? 0:00 ora_arc1_ora817
………….
oracle 365 10 Jul 25 ? 0:00 ora_pmon_ora817
oracle 367 10 Jul 25 ? 0:01 ora_dbw0_ora817
oracle 369 10 Jul 25 ? 0:01 ora_lgwr_ora817
oracle 371 10 Jul 25 ? 0:01 ora_ckpt_ora817
oracle 373 10 Jul 25 ? 0:01 ora_smon_ora817
oracle 375 10 Jul 25 ? 0:00 ora_reco_ora817
oracle 377 10 Jul 25 ? 0:12 ora_snp0_ora817
oracle 379 10 Jul 25 ? 0:01 ora_arc0_ora817
oracle 470 4630 Jul 25 ? 0:01 dtwm
oracle 481 10 Jul 25 ? 0:00 /bin/ksh /usr/dt/bin/sdtvolcheck -d -z 5 cdrom,zip,jaz,dvdrom,rmdisk
oracle 500 4990 Jul 25 ?? 0:00 /usr/dt/bin/dtterm -C -ls
oracle 498 4810 Jul 25 ? 0:00 /bin/cat /tmp/.removable/notify481
oracle 502 5000 Jul 25 pts/5 0:00 -sh
oracle715171500 10:07:03 ? 0:00 oracleora817 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle715071430 10:07:02 pts/6 0:00 sqlplus system/manager
oracle715271500 10:07:05 pts/6 0:00 /bin/sh
oracle714371390 10:06:54 pts/6 0:00 -sh
oracle6346 10 Aug 06 ? 0:00 /export/oracle/app/oracle/product/8.1.7/bin/tnslsnr LISTENER -inherit
root715471530 10:07:20 pts/6 0:00 grep ora



理解ORACLE的内存使用

ORACLE的内存使用有两种基本的使用方式：私有或共享（shared或privated）。私有类型同时只能被单一进程所使用，而共享内存则可以同时被多个进程所使用（存在争用）。
最大的共享内存段是SGA。SGA被映射到一块虚拟地址空间供所有后台进程和服务器进程使用。 大多数显示内存使用的程序，如“top”或“ps -lf”都不能区别共享和私有内存，也不能显示出每个后台进程和服务器进程的SGA使用。它显示的数值可能是ORACLE使用了多次的数量，其总和可能已经大于实际访问的物理内存量。
为了正确检测ORACLE的内存使用，我们可以利用“/usr/proc/bin/pmap”。同它相关的一个脚本是“omemuse”，即Oracle Memory Usage的意思。这个脚本调用pmap来检测ORACLE的内存使用。我们可以打“omemuse h”来看详细的介绍。脚本跟踪指定的ORACLE进程并报告相应的私有和共享内存的使用大小。
后台进程使用私有内存的大小基本相同，因为所有的后台进程实际上只是在对“$ORACLE_HOME/bin/oracle”的不同调用请求而已。
检测服务器进程的内存使用复杂了些，因为不同时间上的内存使用可能大不相同。为了得到近似的数值，我们可以在数据库运行高峰期进行平均采样后取均值。
ORACLE后台进程和服务器进程使用内存的块照值可以通过“omemuse SB”来获得。此命令将显示ORACLE所有的服务器进程使用的私有内存总大小，ORACLE所有后台进程使用私有内存的总大小，ORACLE后台进程和服务器进程所使用的共享内存的总和。最后，给出INSTANCE使用内存的总大小。

复制内容到剪贴板

代码:



$ sqlplus system/manager
SQL*Plus: Release 8.1.7.0.0 - Production on Tue Jul 30 13:04:09 2002
(c) Copyright 2000 Oracle Corporation.All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 – Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL> select spid,username,terminal from v$process;
SPID USERNAME TERMINAL
--------- --------------- ------------------------------
365 oracle UNKNOWN
…(details about background processes)
4141 oracle pts/2
10 rows selected.
# su - oracle
Sun Microsystems Inc. SunOS 5.8 Generic February 2000
$ cd /
$ ./memcheck S
Total Shadow(bytes) : 958464
-----
Grand Total (bytes) : 958464
$ ./memcheck B
Total Private (bytes) : 13332480
Total Shared(bytes) : 145223680
-----
Grand Total (bytes) : 158556160
$ ./memcheck SB
Total Shadow(bytes) : 958464
Total Private(bytes) : 13332480
Total Shared(bytes) : 145223680
-----
Grand Total (bytes) : 159514624
$ ./memcheck P 4141
Total Private(bytes) : 1028096
Total Shared(bytes) : 145223680
-----
Grand Total (bytes) : 146251776


关于使用pmap和检测进程内存使用的更详细信息请参阅：

http://www.itworld.com/Comp/2402/UIR980301perf
http://www.sun.com/sun-on-net/performance/vmsizing.pdf

附录：对应脚本

复制内容到剪贴板

代码:



#!/usr/bin/sh
#
# Copyright 2001 Oracle Corporation
#
# program: omemuse (Oracle MEMory USagE)
# by Richard Gulledge
#
# modification history:
# date by comments
# ----------------------------------
# 11/15/1999rgulledgoriginal program
# 04/16/2001rgulledgminor usage check mods
#

usage()
{
echo "Usage: $0 [ SB ]"
echo "Usage: $0 [ P <pid> ]"
echo "Usage: $0 [ h ]"
echo " "
echo "specify 'S' for Oracle shadow processes"
echo "specify 'B' for Oracle background processes (includes shared memory SGA)"
echo "specify 'h' for help"
echo " "
}

echo " "

#
# check usage
#
if [ $# = "0" ];then
usage;exit 1
fi
Parm1=$1
if [ $Parm1 = "h" ];then
echo "This script uses the Sun Solaris pmap command to determine memory usage"
echo "for Oracle server [B]ackground processes and/or [S]hadow processes."
echo "An individual [P]rocess can also be specified."
echo " "
echo "Although the Oracle server background processes memory usage should"
echo "remain fairly constant, the memory used by any given shadow process"
echo "can vary greatly.This script shows only a snapshot of the current"
echo "memory usage for the processes specified."
echo " "
echo "The 'B' option shows the sum of memory usage for all Oracle server"
echo "background processes, including shared memory like the SGA."
echo " "
echo "The 'S' option shows the sum of private memory usage by all"
echo "shadow processes.It does not include any shared memory like the"
echo "SGA since these are part of the Oracle server background processes."
echo " "
echo "The 'P' option shows memory usage for a specified process, broken"
echo "into two categories, private and shared.If the same executable"
echo "for this process was invoked again, only the private memory"
echo "would be allocated, the rest is shared with the currently running"
echo "process."
echo " "
usage;exit 1
fi
echo $Parm1|grep '[SBP]' > /dev/null
ParmFound=$?
if [ $ParmFound != "0" ];then
usage;exit 1
fi
echo $Parm1|grep P > /dev/null
ParmFound=$?
if [ $ParmFound = "0" ];then
if [ $Parm1 != "P" ];then
usage;exit 1
fi
if [ "X$2" = "X" ];then
usage;exit 1
fi
Parm2=$2
echo $Parm2|grep '[^0-9]' > /dev/null
ParmFound=$?
if [ $ParmFound = "0" ];then
usage;exit 1
fi
PidOwner=`ps -ef | grep -v grep | grep $Parm2 | grep -v $0 | awk '{print $1}'`
CurOwner=`/usr/xpg4/bin/id -un`
if [ "X$PidOwner" != "X$CurOwner" ];then
echo "Not owner of pid $Parm2, or pid $Parm2 does not exist"
echo " "
usage;exit 1
fi
else
if [ "X${ORACLE_SID}" = "X" ];then
echo "You must set ORACLE_SID first"
usage;exit1
fi
fi

#
# initialize variables
#
Pmap="/usr/proc/bin/pmap"
SharUse="/tmp/omemuseS$$"
PrivUse="/tmp/omemuseP$$"
ShadUse="/tmp/omemuseD$$"
PidPUse="/tmp/omemusePP$$"
PidSUse="/tmp/omemusePS$$"
TotalShad=0
TotalShar=0
TotalPriv=0
PidPriv=0
PidShar=0

#
# shadow processes
#
echo $Parm1|grep S > /dev/null
ParmFound=$?
if [ $ParmFound = "0" ];then
ShadPrc="`ps -ef|grep -v grep|grep oracle$ORACLE_SID|awk '{print $2}'`"
echo "" > $ShadUse
for i in $ShadPrc;do
$Pmap $i | grep "read/write" | grep -v shared | \
awk '{print $2}' | awk -FK '{print $1}' >> $ShadUse
done
for i in `cat $ShadUse`;do
TotalShad=`expr $TotalShad + $i`
done
TotalShad=`expr $TotalShad "*" 1024`
echo "Total Shadow(bytes) :$TotalShad"
/bin/rm $ShadUse
fi

#
# non-shared portion of background processes
#
echo $Parm1|grep B > /dev/null
ParmFound=$?
if [ $ParmFound = "0" ];then
OrclPrc="`ps -ef|grep -v grep|grep ora_|grep $ORACLE_SID|awk '{print $2}'`"
BkgdPrc="`echo $OrclPrc|awk '{print $1}'`"
echo "" > $PrivUse
for i in $OrclPrc;do
$Pmap $i | grep "read/write" | grep -v shared | \
awk '{print $2}' | awk -FK '{print $1}' >> $PrivUse
done
for i in `cat $PrivUse`;do
TotalPriv=`expr $TotalPriv + $i`
done
TotalPriv=`expr $TotalPriv "*" 1024`
echo "Total Private (bytes) :$TotalPriv"

#
# shared portion of background processes
#
echo "" > $SharUse
$Pmap $BkgdPrc | grep "read/exec" | \
awk '{print $2}' | awk -FK '{print $1}' >> $SharUse
$Pmap $BkgdPrc | grep "shared" | \
awk '{print $2}' | awk -FK '{print $1}' >> $SharUse
for i in `cat $SharUse`;do
TotalShar=`expr $TotalShar + $i`
done
TotalShar=`expr $TotalShar "*" 1024`
echo "Total Shared(bytes) :$TotalShar"
/bin/rm $SharUse $PrivUse
fi

#
# non-shared portion of pid
#
echo $Parm1|grep P > /dev/null
ParmFound=$?
if [ $ParmFound = "0" ];then
echo "" > $PidPUse
$Pmap $Parm2 | grep "read/write" | grep -v shared | \
awk '{print $2}' | awk -FK '{print $1}' >> $PidPUse
for i in `cat $PidPUse`;do
PidPriv=`expr $PidPriv + $i`
done
PidPriv=`expr $PidPriv "*" 1024`
echo "Total Private (bytes) :$PidPriv"

#
# shared portion of pid
#
echo "" > $PidSUse
$Pmap $Parm2 | grep "read/exec" | awk '{print $2}' | \
awk -FK '{print $1}' >> $PidSUse
$Pmap $Parm2 | grep "shared" | awk '{print $2}' | \
awk -FK '{print $1}' >> $PidSUse
for i in `cat $PidSUse`;do
PidShar=`expr $PidShar + $i`
done
PidShar=`expr $PidShar "*" 1024`
echo "Total Shared(bytes) :$PidShar"
/bin/rm $PidPUse $PidSUse
fi

#
# Display grand total
#
Gtotal="`expr $TotalShad + $TotalPriv + $TotalShar + $PidPriv + $PidShar`"
echo " -----"
echo "Grand Total (bytes) :$Gtotal"
echo " "



----------------------
btw：我是ITPUB上的TOPXU。很高兴看到另外一个ORACLE技术人员组成的论坛。CHAO_PING斑竹可能是吸引我来这里的一个原因。翻出来以前翻译的一篇文章，看原文请到这里：

HTTP://METALINK.ORACLE.COM/ 文章编号：Note:153655.1