Oracle instance memory usage .

[chapaya_1]

Hello ,
I have L2000,11i with 3 instances ,
How can i know how much memory each instance is taking ?

Bye .

[Nicolas Dumeige]

Hello,

For the SGA,
In SQL*Plus :
SQL> show SGA

Look at the alerte.log of each instance. If you don't know where there are :
SELECT p.value||'/alert_'||d.name||'.log'
FROM V$PARAMETER p, V$DATABASE d
WHERE p.name='background_dump_dest'
/

You can have a look on the shared segment with :
icps -a

For the PGA, the management depend on the version of Oracle that your're using.

In anycase, do NOT trust standard tool to give you the amout of memory used, Oracle use shared memory, shared libraries and the text segment of various executable is also shared.

Read metalink.oracle.com Note:174555.1

Cheers

Nicolas

[chapaya_1]

Hi ,
Is it possible to know the usage of instance by glance ,perfiew ?

Bye.

[Fred Ruffet]

If you want to see memory usage from the system, the easiest way is to issue a
ipcs -m
It will show you shared memory segments owned by Oracle.

[Arturo Galbiati]

Hi,
on Hp-UX server logged as oracle you can use:

/usr/ccs/bin/size $ORACLE_HOME/bin/oracle|awk '{print $1,$3,$5}'|read Mtext Mdata Mbss
#echo $Mtext $Mdata $Mbss
echo "==============================="
echo " $ORACLE_SID"
echo "==============================="
$ORACLE_HOME/bin/sqlplus -s internal<

[twang]

A shell script I currently use to estimate memory usage for a given Oracle instance, you can tailor it to your needs.

#!/bin/ksh
#
##########################################################################
#
# USAGE: estOraMemory.sh INSTANCE
# PARAMETER: Oracle Instance SID
# AUTHOR: Satya Sridhar @ HMI : 05/14/2002
#
# DESCRIPTION: Estimating Memory Usage.
# Use this script to estimate the memory usage for a given Oracle instance
#
# TODO:
#
##########################################################################

#
# Check usage
#
if [ ! $# -eq 1 ]; then
echo "`date '+%Y%m%d.%H%M%S'` :: Error: Usage = estOraMemory.sh INSTANCE"
exit
fi


echo $1 | . oraenv

if [ `uname` = AIX ]; then
/usr/ccs/bin/size $ORACLE_HOME/bin/oracle | awk '{print $2,$4,$6}' | read Mtext Mdata Mbss
fi

if [ `uname` = SunOS ]; then
/usr/ccs/bin/size $ORACLE_HOME/bin/oracle | awk '{print $1,$3,$5}' | read Mtext Mdata Mbss
fi

if [ `uname` = HP-UX ]; then
/usr/ccs/bin/size $ORACLE_HOME/bin/oracle | awk '{print $1,$3,$5}' | read Mtext Mdata Mbss
fi

if [ `uname` = Linux ]; then
/usr/bin/size $ORACLE_HOME/bin/oracle | tail -1 | awk '{print $1,$2,$3}' | read Mtext Mdata Mbss
fi

echo ""
echo ""
echo Oracle Text section size: $Mtext
echo ""
echo Oracle Data section size: $Mdata
echo ""
echo "Oracle bss (uninitialized data) section size:" $Mbss
echo ""


$ORACLE_HOME/bin/sqlplus -s internal/ <

[Nicolas Dumeige]

Chapaya

You have receive some valuable answers. Please take the time to assign points.

Twang, neat script.