RE: how much ram per oracle process?

From: Brian MacLean <bmaclean_at_vcommerce.com>
Date: Fri, 01 Jun 2001 09:38:14 -0700
Message-ID: <F001.00317EB6.20010601083636@fatcity.com>

I have included a bit of text from the Oracle8i Administrator's Reference Release 3 (8.1.7) for Sun SPARC Solaris Part Number A85349-01. Note the script that is about 40 lines down.

Oracle8i Memory Requirements and Usage

Calculate memory usage requirements to determine the number of users that can be on the system. This will also help in determining the physical memory and swap space requirements. To calculate the memory requirements, use the following formula:

+ <size of the SGA>
+ n * ( <size of tool executables private data section>
+ <size of oracle executables uninitialized data section>
+ <8192 bytes for the stack> 
+ <2048 bytes for the processes user area>)

where n = number of background processes.

For each client-server connection, use the following formula to estimate virtual memory requirements:

+ <size of oracle executables uninitialized data section>
+ <8192 bytes for the stack>
+ <2048 bytes for processes user area> 
+ <cursor area needed for the application>

Use the size command to estimate an executable's text size, private data section size, and uninitialized data section size (or DSS). Program text is only counted once, no matter how many times the program is invoked, because Oracle executable text is shared.

To calculate the Oracle physical memory (background and shadow processes) usage while the database is up and users are connected to it, use the pmap command. Sum the shared sections (indicated by read/write/exec/shared and read/exec) for the pmon process. Sum the private section (indicated by read/write/exec) for each shadow and background process, including pmon. Background process names begin with ora_, and end with the SID. Shadow process names begin with oracleSID.

Calculating acutal memory usage
Use the following script to show the actual memory usage.

#!/usr/bin/sh

# Copyright 2000 Oracle Corporation
#
# modification history:
# date by comments
# ---------- -------- ----------------
# 07/15/2000 rgulledg original program
#

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
if [ $1 = "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 $1|grep [SBP] > /dev/null
ParmFound=$?
if [ $ParmFound != "0" ];then
usage;exit 1
fi
echo $1|grep P > /dev/null
ParmFound=$?
if [ $ParmFound = "0" ];then

  if [ $1 != "P" ];then
    usage;exit 1
  fi
  if [ "X$2" = "X" ];then
    usage;exit 1
  fi
  echo $2|grep [0-9] > /dev/null
  ParmFound=$?
  if [ $ParmFound != "0" ];then
    usage;exit 1
  fi
  PidOwner=`ps -ef | grep -v grep | grep $2 | grep -v $0 | awk '{print \

$1}'`

  CurOwner=`/usr/xpg4/bin/id -un`
  if [ "X$PidOwner" != "X$CurOwner" ];then
    echo "Not owner of pid $2, or pid $2 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

#
# 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 $1|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

#
# non-shared portion of background processes
#

echo $1|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

#
# non-shared portion of pid
#

echo $1|grep P > /dev/null
ParmFound=$?
if [ $ParmFound = "0" ];then

  echo "" > $PidPUse
  $Pmap $2 | 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 $2 | grep "read/exec" | awk '{print $2}' | \
    awk -FK '{print $1}' >> $PidSUse
  $Pmap $2 | 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

#
# Display grand total
#

Gtotal="`expr $TotalShad + $TotalPriv + $TotalShar + $PidPriv + \ $PidShar`"

echo "                  -----"

echo "Grand Total (bytes) : $Gtotal" echo " "

Use the ps command to determine process size in pages.

System page size is architecture-dependent. Use the pagesize command to determine whether the size is 4096 or 8192 bytes.

Do not use the ps -elf command as the SZ column repeats the shared portion of memory for each process shown, and makes it appear that Oracle is using much more memory than it actually is.

See Also:
Refer to your Sun SPARC Solaris documentation for a list of available switches for the ps command.

For each process, multiply the SZ value by the page size.

Add the text size for the Oracle executable and every other Oracle tool executable running on the system to that subtotal. Remember to count executable sizes only once, regardless of how many times the executable is invoked.

-----Original Message-----
From: Rahul [mailto:rahul_at_ratelindo.co.id] Sent: Thursday, May 31, 2001 11:06 PM
To: Multiple recipients of list ORACLE-L Subject: RE: how much ram per oracle process?

i guess the SGA size is also included in the SZ

> ----------
> From:         Henry Chan[SMTP:henry_at_erogo.com]
> Reply To:     henry_at_erogo.com
> Sent:         Friday, June 01, 2001 5:59 AM
> To:   LazyDBA.com Discussion
> Subject:      how much ram per oracle process?
> 
> Environment: Sun 2.7, Oracle 8.1.6.3, 2Gig RAM
> 
> I issued ps -elf to see how much memory each oracle server process was
> using.
> The SZ column for each oracle process was approximately 47175. There are
> 112
> connections.
> This is over 5Gig of virtual memory used.
> 1) is this an accurate indication of how much memory each oracle process
> is
> using?
> 2) why it is so large? What determines how much memory is used?
> 
> HenryC
> 
> 
> 
> 
> 
> --------
> Oracle documentation is here:
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to oracledba-unsubscribe_at_LAZYDBA.com
> To subscribe:   send a blank email to oracledba-subscribe_at_LAZYDBA.com
> Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rahul
  INET: rahul_at_ratelindo.co.id


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Jun 01 2001 - 11:38:14 CDT