cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle instance memory usage .

chapaya_1
Frequent Advisor

Oracle instance memory usage .

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

Bye .
6 REPLIES
Nicolas Dumeige
Esteemed Contributor

Re: Oracle instance memory usage .

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
All different, all Unix
chapaya_1
Frequent Advisor

Re: Oracle instance memory usage .

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

Bye.
Fred Ruffet
Honored Contributor

Re: Oracle instance memory usage .

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.
--

"Reality is just a point of view." (P. K. D.)
Arturo Galbiati
Esteemed Contributor

Re: Oracle instance memory usage .

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
Honored Contributor

Re: Oracle instance memory usage .

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
Esteemed Contributor

Re: Oracle instance memory usage .

Chapaya

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

Twang, neat script.



All different, all Unix