Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

How to find out used memory by Oracle

SOLVED
Go to solution
David_246
Trusted Contributor

How to find out used memory by Oracle

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
@yourservice
12 REPLIES
Stefan Farrelly
Honored Contributor

Re: How to find out used memory by Oracle

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.

Im from Palmerston North, New Zealand, but somehow ended up in London...
David_246
Trusted Contributor

Re: How to find out used memory by Oracle

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
@yourservice
David_246
Trusted Contributor

Re: How to find out used memory by Oracle

Hhhm, knew there was something strange :

clame --> claim

Regs David
@yourservice
Steve Steel
Honored Contributor

Re: How to find out used memory by Oracle

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
If you want truly to understand something, try to change it. (Kurt Lewin)
Alexander M. Ermes
Honored Contributor

Re: How to find out used memory by Oracle

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
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Khalid A. Al-Tayaran
Valued Contributor

Re: How to find out used memory by Oracle


Hi,

try:

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

Re: How to find out used memory by Oracle

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.
Im from Palmerston North, New Zealand, but somehow ended up in London...
David_246
Trusted Contributor

Re: How to find out used memory by Oracle

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
@yourservice
Stefan Farrelly
Honored Contributor
Solution

Re: How to find out used memory by Oracle

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.
Im from Palmerston North, New Zealand, but somehow ended up in London...
David_246
Trusted Contributor

Re: How to find out used memory by Oracle

Alright, let's not deny anymore that you're good.
But I keep anoying to tell you that :

t=0
for i in `UNIX95= ps -e -o vsz=Kbytes -o ruser -o pid,args=Command-Line | sort -rnk1 | grep -v Kbytes | awk '{print $1}'`
do
t=`expr $t + $i`
print $t
done

Will give me a : 2651012 Kbytes
I would say that is a little to much difference when looking at :

swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 3072 161 2911 5% 0 - 1 /dev/vg00/lvol2
reserve - 582 -582
memory 745 263 482 35%
total 3817 1006 2811 26% - 0 -

But you already won! If you could clarify this it would help me in understanding, but the problem just got answered loud and clear!!!

Best Regs David
@yourservice
T G Manikandan
Honored Contributor

Re: How to find out used memory by Oracle

just check this attachment
Stefan Farrelly
Honored Contributor

Re: How to find out used memory by Oracle

The problem with the Kbytes total from ps is that it doesnt take into account shared code/memory. Remember, any executable loaded by hundreds of users will have shared instruction code (only user data will be different), which means a large memory saving, so its not very accurate - as you can see. You need something more sofisticated such as glance/gpm to work out individual process memory usage more accurately. The UNIX95 command is just a guide as to the largest memory hoggers - its not gospel.

Sometimes there are runaway processes, or operating system procesess which have gone mad and are hogging hundreds of MB of ram - this command is useful for spotting it, or you could use top, but not for adding up the total.
Im from Palmerston North, New Zealand, but somehow ended up in London...