database taking memory

[Michael Murphy_2]

I admin a machine that only has 32gig of memory - that has oracle processes that take up 2.5 terabytes of virtual memory during the day (1000+ processes each with 2.5 gig). I have kernel params set very high but was curious if this can realistically be supported (without buying hardware) Also - is it normal for every oracle connection to start a 2.5 gig process? - that seems high to me...Thanks

[Bill Hassell]

Oracle can be configured to take a few hundred megs to several gigs -- there is no 'normal', it is up to the DBA. In general, using more RAM can reduce the I/O and up to a certain point, improve overall performance. BUT ONLY when you have enough RAM. When the system begins swapping (page out in the vmstat program), then *ALL* of the advantages of using large amounts of RAM are totally wiped out and the result is much, much slower than changing the Oracle parameters to use a smaller amount of RAM.

Oracle can run on a 4Gb system but performance may be totally unacceptable. So your management has to decide: run lots instances of Oracle very slowly, or buy more RAM or buy more computers to spread out the load. Many years ago, it was quite popular to stuff a Volkswagen with 10 or 20 people. Yeah, it could be done but wasn't very practical. The DBA cannot configure Oracle without understanding swap penalties due to massive memory consumption. Processes stop, spinlocks occur, everything slows down when paging takes place. Put in 128 Gb of RAM and see if performance improves. If so, change your machine to a SuperDome with 128 processors and 2 Tb of RAM and your swap penalties will disappear.

[Sandman!]

A per process size of 2.5 gig is rather large even if linked statically. Could you post output of the command below to troubleshoot further:

# UNIX95= ps -C oracle -o comm,vsz

[Hein van den Heuvel]

I must be missing something here.
Please educate me.

A 1000 * 2.5 gig virtual memory process on a 32GB physical machine seems TOO SMALL to me, not too big.
That suggests less than 2.5G SGA out of 32GB, which would be small for a single instance, or just a few instances.

Why would there be too little physical memory on this system? What do the other tools indicate? What basis is there to suggest a jump to 128Gb?

Most Oracle systems I have worked with have the SGA's of all active DB's combined set higher than 50% of physical memory.

To turn the math around... give every Oracle user 1.5 - 10.0 MB of physical, private memory, time 1000 is 1.5G - 10G.
Subtract that from 32GB physical, subtract an other 2GB for 'stuff' and the system is probably happy wih 16GB or more of SGA, giving each oracle process 16GB of virtual memory memory, but more than 99% of that is all sharing the same physical memory!

Michael, are you seeing a performance issues? Best i can tell from your topic you just not like the looks of the 'ps' numbers based on performing inappropriate math. Correct?

btw... Since when did 32Gb become 'only 32gig'? Is that what I get for taking a long weekend off? (and yes, I have personally worked with 256GB memory systems :-)


Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Hein van den Heuvel]

Michael,

Any updates, insights?

Cheers,
Hein.