Memory usage by Oracle Database

[Soumyadip Pal]

Hi,

We have one HP 9000/800/N4000-44 machine having 12 Gb memory. There are several Oracle databases running in the machine. We want to calculate the memory usage of individual databases. How this can be achived ? We have tried ps -o command for this. Not sure whether this gives accurate result. Please advise.

[Jean-Luc Oudart]

Hi

for the database itself use the "show sga" command ( as sysdba).
This does not include the oracle (client) processes.

Regards
Jean-Luc

[Jean-Luc Oudart]

see attachment

Regards
Jean-Luc

[Soumyadip Pal]

Hi Jean,

Thanks for replying. What I feel the database does not uses the whole amount of SGA defined all the time. Also, I need to calculate the memory used by client process, which I belive uses a part of Shared memory (SGA) and some private memory.

[Jean-Luc Oudart]

Hi

the fixed will always be allocated and you can chexk it with the size of the associated shared memory segment(s).

The PGA for the oracle processes is made of stack and UGA. If you use MTS the UGA will be located in the SGA.

see attachment.

Regards
Jean-Luc

[Arturo Galbiati]

I use this UNIX script which includes also the size of the programs:

/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 "/ as sysdba"<

[Yogeeraj_1]

hi,
just one remark!

Measuring the size of process memory from the OS can be tricky. Because the processes all attach to the shared memory of the SGA, this is reported as part of the process size.

hope this helps!

kind regards
yogeeraj

[RAC_1]

First, there many ways to do it.
1. Through oracle
2. OS commands-not very accurate, but can give you required information.
3. glance-accurate.

Point 2 would require you distingush between different ORACLE SID processes. (even though they run under same use id)
Say process oracle xyzABC - is ABC oracle instance process and dseFRG is FRG oracle sid.
The processes memory, now can be calculated as follows.

UNIX95= ps -ef -o vsz,ruser,pid,args | grep -i '[a]bcXYZ' | more

UNIX95= ps -ef -o vsz,ruser,pid,args | grep -i '[d]esFRG' | more

3. Do you have glance?? If yes, fire up gpm and check it. If you have PV, then you can check old history also. In case you do not have PV, you will have to manage with mwa extract command. If you require extract details, please reply.