Re: Calculating Physical memory for Oracle Sessions

From: Tim Gorman <tim_at_evdbt.com>
Date: Wed, 29 Nov 2006 21:21:27 -0700
Message-ID: <456E5C47.3010006@evdbt.com>

Anurag,

I have this shell script named "oramem.sh" posted online at http://www.EvDBT.com/tools.htm. It was originally written for Solaris using the excellent "pmap" utility. It can work with Linux if the SYSSTAT rpms are installed, which include "pmap". I have an alternate version for HPUX that uses some custom-built "C" programs. I don't have a version for AIX at all, as I haven't found anything similar to "pmap" on AIX.

Hope this helps...

--
Tim Gorman
consultant - Evergreen Database Technologies, Inc.

website = http://www.evdbt.com
email   = tim@evdbt.com
mobile  = +1-303-885-4526
fax     = +1-303-484-3608

Anurag Verma wrote:

Hi All,

My 9.2.0.7 database has an sga size of 1.3 GB and the RAM size is 4096 MB.

We were facing a problem of memory contention yesterday due to the increased number of sessions/connections to the database.

96% of the Physical memory and 94-95% of virtual memory was used, when checked the processes in the IBM AIX server.

So when the number of sessions increased, listener stopped receiving newer connections.

The ever increasing number of sessions were taking up the memory.

Latch free contention was there and found library cache latch stats were showing large number of sleeps.

If we need to increase shared pool, memory was not available.

One option we are planning is to increase the physical memory.

So my question is how do we determine the size of the memory used for Oracle sessions.

Say for adding another 100 sessions, how much memory will it take, so that it will help us in adding more RAM in to the server.

Thanks for your thoughts on this.

Anurag

#!/bin/ksh
#============================================================================


# File:		oramem.sh

# Type:		UNIX korn-shell script

# Author:	Tim Gorman (Evergreen Database Technologies Inc.)

# Date:		28jun02

#

# Description:

#
#	This shell script utilizes the "pmap -x" command to total up the
#	total amount of virtual memory used by all of the Oracle server
#	processes (both "background" and "foreground") belonging to a
#	database instance.
#


# Modifications:

#	TGorman 28jun02	written for Solaris 2.8
#	THaeber 02apr03	added logic to handle more than one shared segment
#	TGorman 19mar04	calculated "private" and "shared" totals differently,
#			by summarizing "heap" and "stack" totals into
#			"private", subtracting that from total to obtain
#			"shared" total
#============================================================================
#
#----------------------------------------------------------------------------

# Validate command-line parameters...

#----------------------------------------------------------------------------

_Prog=oramem.sh
if (( $# > 1 ))
then

	echo "\nUsage: \"${_Prog} [ verbose ]\"; aborting...\n"
	exit 1

#
#----------------------------------------------------------------------------

# If a command-line parameter is specified, then put the script into
# "verbose" mode...

#----------------------------------------------------------------------------

if (( $# == 1 ))
then

_VerboseFlag=TRUE
else

_VerboseFlag=FALSE
fi

#
#----------------------------------------------------------------------------

# Verify that the ORACLE_SID environment variable is set...

#----------------------------------------------------------------------------

if [[ "${ORACLE_SID}" = "" ]]
then

	echo "ORACLE_SID not set; aborting..."
	exit 1

#
#----------------------------------------------------------------------------

# Create a name for a "temporary" scratch file...

#----------------------------------------------------------------------------

_TmpFile=/tmp/${_Prog}_$$.tmp
_TmpFile2=/tmp/${_Prog}2_$$.tmp

#
#----------------------------------------------------------------------------

# Using the UNIX "ps", "sed", and "awk" commands, retrieve VM and RSS
# information for this database instance's processes, saving the information
# to the "scratch" file...

#----------------------------------------------------------------------------
ps -eo fname,pid,args | \
	sed '1d' | \
	awk '{ \
		if ($1 == "oracle") \
		{ \
			if (substr($3,1,6)=="oracle") \
			{ \
				printf("%s %s\n", substr($3,7,10), $0);
			} \
			else
			{ \
				printf("%s %s\n", substr($3,10,10), $0);
			} \
		} \
	     }' > ${_TmpFile}

#
integer _SwapUsedKb=`swap -s | awk '{print $9}' | sed 's/k//'` _SwapUsedMb=`echo ${_SwapUsedKb} | awk '{printf("%0.02f\n", $1 / 1024)}'` integer _SwapFreeKb=`swap -s | awk '{print $11}' | sed 's/k//'` _SwapFreeMb=`echo ${_SwapFreeKb} | awk '{printf("%0.02f\n", $1 / 1024)}'` integer _TotRAM=`prtconf | grep "Mem" | awk '{print $3}` echo "\nTotal RAM = ${_TotRAM}Mb, Swap = ${_SwapUsedMb}Mb used, ${_SwapFreeMb}Mb free" echo "\nTotal memory consumption by Oracle instance \"${ORACLE_SID}\":"

#
#----------------------------------------------------------------------------

# Display header if printing in "verbose mode"...

#----------------------------------------------------------------------------

if [[ "${_VerboseFlag}" = "TRUE" ]]
then

	echo "PID\tCommand\t\t\tShm\t\tPriv"
	echo "===\t=======\t\t\t===\t\t===="

#
#----------------------------------------------------------------------------

# ...retrieve the process information from the "scratch" file and save it
# into "korn-shell" arrays...

#----------------------------------------------------------------------------
integer _MaxSHR=0
integer _TotPRV=0

integer _BG=0
integer _FG=0
while read _SID _EXE _PID _ARGV0 _ARGVn
do

	#
	if [[ "${ORACLE_SID}" = "${_SID}" ]]
	then
		if [[ "`echo ${_ARGV0} | grep oracle${ORACLE_SID}`" = "" ]]
		then
			integer _BG=${_BG}+1
		else
			integer _FG=${_FG}+1
		fi
		#
		pmap -x ${_PID} > ${_TmpFile2} 2>&1
		if (( $? != 0 ))
		then
			echo "warning: \"pmap -x ${_PID}\" failed..."
		fi
		#
		# 02-APR-2003  Ty Haeber
		#
		#  Some systems may have more than one shared segment; therefore, I had to
		#  add some logic to sum this field.
		#
		###integer _SGA=`grep shmid ${_TmpFile2} | awk '{print $5}'`
		###integer _SGA=`grep shmid ${_TmpFile2} | awk '{ s += $5 } END {print s}'`
		###integer _SHR=`grep 'total Kb' ${_TmpFile2} | awk '{print $5}'`
		###integer _PRV=`grep 'total Kb' ${_TmpFile2} | awk '{print $6}'`
		###integer _SHR=${_SHR}+${_SGA}
		###integer _PRV=${_PRV}-${_SGA}
		#
		# 19-MAR-2004	Tim Gorman
		#
		# Calculated a different way, by isolating heap and stack and subtracting that from the total...
		#
		integer _HEAP=`grep -i heap ${_TmpFile2} | awk '{i+=$2}END{print i}'`
###echo "_HEAP=${_HEAP}"
		integer _STACK=`grep -i stack ${_TmpFile2} | awk '{i+=$2}END{print i}'`
###echo "_STACK=${_STACK}"
		integer _PRV=${_HEAP}+${_STACK}
		integer _ALL=`grep 'total Kb' ${_TmpFile2} | awk '{print $3}'`
		integer _SHR=${_ALL}-${_PRV}
		#
		if (( ${_SHR} > ${_MaxSHR} ))
		then
			integer _MaxSHR=${_SHR}
		fi
		integer _TotPRV=${_TotPRV}+${_PRV}
###echo "_ALL=${_ALL}, _SHR=${_SHR}, _PRV=${_PRV}, _TotPRV=${_TotPRV}"
		if [[ "${_VerboseFlag}" = "TRUE" ]]
		then
			echo "${_PID}\t${_ARGV0}\t\t${_SHR}\t\t${_PRV}"
		fi
	fi
	#

done < ${_TmpFile}
rm -f ${_TmpFile} ${_TmpFile2}

#
#----------------------------------------------------------------------------

# Display totals...

#----------------------------------------------------------------------------

integer _TotMEM=${_MaxSHR}+${_TotPRV}
echo

echo "# Procs\t\t# Procs\t\tMax\t\tSum"
echo "Foregrnd\tBackgrnd\tShm Kb\t\tPriv Kb\t\tTotal Kb"
echo "========\t========\t======\t\t=======\t\t========"
echo "${_FG}\t\t${_BG}\t\t${_MaxSHR}\t\t${_TotPRV}\t\t${_TotMEM}"

echo

#
#----------------------------------------------------------------------------

# Done!

#----------------------------------------------------------------------------

exit 0

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 29 2006 - 22:21:27 CST