topic Re: Memory usage by Oracle Database in Operating System - HP-UX

Memory usage by Oracle Database

Soumyadip Pal — Thu, 01 Dec 2005 06:33:42 GMT

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.

Re: Memory usage by Oracle Database

Jean-Luc Oudart — Thu, 01 Dec 2005 06:54:58 GMT

Hi

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

Regards
Jean-Luc

Re: Memory usage by Oracle Database

Jean-Luc Oudart — Thu, 01 Dec 2005 06:56:24 GMT

see attachment

Regards
Jean-Luc

Re: Memory usage by Oracle Database

Soumyadip Pal — Thu, 01 Dec 2005 07:52:01 GMT

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.

Re: Memory usage by Oracle Database

Jean-Luc Oudart — Thu, 01 Dec 2005 08:29:50 GMT

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

Re: Memory usage by Oracle Database

Arturo Galbiati — Fri, 02 Dec 2005 04:13:07 GMT

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

Re: Memory usage by Oracle Database

Yogeeraj_1 — Fri, 02 Dec 2005 04:37:38 GMT

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

Re: Memory usage by Oracle Database

RAC_1 — Fri, 02 Dec 2005 05:01:35 GMT

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.