How to find out used memory by Oracle

[David_246]

Hi,

We have Oracle running on a HP9000/800 L2000.
Currently four instances has been configured, we want to add a new instance, but how can I find out if we have enough memory available on the system.
When using swapinfo, I see the following result :

# swapinfo
Kb Kb Kb PCT START/ Kb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 3145728 165296 2980432 5% 0 - 1 /dev/vg00/lvol2
reserve - 541408 -541408
memory 763012 269684 493328 35%

Please help me out.

Regs David

[Stefan Farrelly]

If the USED column from swapinfo is >0 then youve run out of memory, which you have in your case. The figure is 165296, which means your currently using 165Megabytes of RAM you dont have (processes using 165Mb of RAM have been swapped out to disk due to insufficient memory) so I wouldnt add in any more oracle instances until you either free up a lot of memory or add some more RAM.

[David_246]

Dear Mister Olympian, :)

Is it always Oracle that is swapping out memory to disk? There are running more programm's on this machine. Isn't it that when idle time reaches a specified value (kernel param) memory gets swapped to disk automaticly ??

I am actualy looking for a way to see how much memory each oracle instance is claming, and how much there is available to clame.

So, in my opinion it is not always bad to have some swap in "dev", is this right ?


Regs David

[David_246]

Hhhm, knew there was something strange :

clame --> claim

Regs David

[Steve Steel]

Hi


swapinfo -at is more informative.

Oracle will confirm that oracle does its own buffer caching so you can put the kernel parameter swapmem_on to 0 to use all memory for processes or at least reduce dbc_max_pct to 20 from 50.

It is a lot more than memoy usage involved since things like kernel tuning affect whether more processes will run.

see
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x9811a24d9abcd4118fef0090279cd0f9,00.html

Steve Steel

[Alexander M. Ermes]

Hi there.
You can also check the usage of memory
with the ipcs command.
Look at the manpages for the different usages.
That refers to memory, seemaphores etc.
Rgds
Alexander M. Ermes

[Khalid A. Al-Tayaran]

Hi,

try:

swapinfo -tam (in megs) and look at the USED col. like Stefan mentioned.

[Stefan Farrelly]

No, over time swap device usage should never get >0 if memory is free. Processes are not swapped to disk over time - only IF memory pressure forces them to be swapped.

eg. all our HP servers run lots of oracle instances and I never allow swap device usage to get >0 or else performance is affected adversely.

ipcs -ma (and look for the oracle entries and the size column)
shows the amount of shared memory the oracle instances are using themselves, but you need to add in user processes also.

[David_246]

Hi You all,

You're helping me a lot !!
Looking at the hints you give I come up with the following :

dbc_max_pct already was set to 10 --> is it important to have it at 20 ?? Might this be the reason why swapinfo -tam reports me a 161Mb swap on disk ??

When using the ipcs command :

ipcs -ma | awk 'NR<=3||/ora/{print $1,$2,$3,$4,$5,$6,$10,$11,$11}'
IPC status from /dev/kmem as of 12:00:15 2003 2003
T ID KEY MODE OWNER GROUP SEGSZ CPID CPID
Shared Memory:
m 3145 0x95e56888 --rw-r----- oracle dba 48558080 1832 1832
m 3146 0x26056810 --rw-r----- oracle dba 40169472 1871 1871
m 3147 0x399446fc --rw-r----- oracle dba 48558080 1906 1906
m 4172 0xc6c6bad0 --rw-r----- oracle dba 40169472 1943 1943
m 8269 0x6a618be8 --rw-r----- oracle dba 31780864 1978 1978

You can see each instance claiming aprox 40 Mb. We have 1Gb of internal memory (soon 2Gb).

Is it wise to increase dbc_max_pct ?? The system is mainly an Oracle server, but also some other programs running on it ?
The other programs claim in total about 40 Mb as well, including OS.

So, how do I get rid of my current swap on disk ? And how can I afterwards make sure a new instance can be added ?

1gb of memory and aprox 250 SHmem in use does not seem a bottleneck to me.

Thanks for all your help so far, please help me out to finish the last bit.


Regs David

[Stefan Farrelly · Accepted solution]

No, with your current memory shortage it is NOT recommended to increase dbc_max_pct to 20. Only if you add another GB of RAM should you increase it to 20.

The only way to get rid of your current swap on disk is to free up some memory - shutdown an oracle instance (or more), or some user processes, but I doubt very much you will be able to free up enough.

The only way to make swapinfo output look better is to reboot, but as soon as you start all your databases and all your users get on it will start to show device swap as USED again - until you add some more RAM (or dont startup as many databases).

Use this command to get a rough idea of which processes are using the most memory - maybe some are erroneous and can be shutdown;

UNIX95= ps -e -o vsz=Kbytes -o ruser -o pid,args=Command-Line | sort -rnk1 | more

It displays processes in order of which are using the most RAM.