General Memory Allocation Question

[Jeanine Kone]

I have an HP9000, L class server that we are using strictly as an Oracle databse server. It has 3Gig of memory. What I want to know is, how much of that memory should I leave as Free Memory? I have several database instances on the machine, and I would like to give each as much memory as practical, but I do not want to overallocate. Right now the allocation is as follows:

Tot Phys Mem: 3.00 gb
Sys Mem: 172.5 mb
Buf Cache: 215.0 mb
User Mem: 1.6 gb

Thanks in advance for your suggestions!

[A. Clay Stephenson · Accepted solution]

At first glance, I would say that you are probably not running enough buffer cache. If you are using raw/io or the OnlineJFS equivalent then you may be fine.

If you are running several large instances of Oracle then 3GB is not much memory.

I would think nothing of using > 90% of memory; that's why you bought it. If you start seeing significant pageouts then it's time to reduce your SGA's or better still buy more memory.

[Jeanine Kone]

Thanks. I'll use 10% as my guideline and allocate a bit more of that free memory. I am not using raw io, I do have onlineJFS - but couldn't say what it is actually doing regarding io (HP installed them for us).

given what I have free, how much more memory would you suggest allocating to the buffer cache?

[Christopher McCray_1]

Hello,

This is nothing more than to echo A. Clay; no points please.

set your dbc_min_pct to 5 and your dbc_max_pct to 10 or 15 ( maybe 20; check performance after the change).

Your system will generally need 10%, and the rest will be used by buffer cache and user procs.

As for the number (sizes?) of your db instances, 3Gb may not be enough. I have on several machines a single instance that will gladly take over upwards of 4GB all by itself.

Hope this helps

Chris

[Jeanine Kone]

You can get a couple points just for taking the time to respond. I agree that 3Gig is not enough - unfortunatly - I don't have much choice. I am lucky I got that much! I'll push for more when the lease is up (2 more years and counting).

[A. Clay Stephenson]

You ought to examine /etc/fstab and note the mount options used for the Oracle filesystems. If you see convosync=direct,mincache=direct then you are bypassing the UNIX buffer cache.
If this is an 11.0 box then somewhere around 300-400 MB of cache performs well. If this is an 11.11 box, 800MB-1GB would be about right. However; these numbers apply to boxes with lots of available memory which ain't you.

As a general rule 10.20 and 11.0 boxes performs best when Oracle bypasses the buffer cache (for datafiles and indices; redo and archive logs should always use buffer cache) and, in general, I have fould that 11.11 performs best when everything uses buffer cache.

I think as a good first step, I would set buffer cache at about 300MB. If this is an 11.11 box, I would consider using dynamic buffer cache as the algorithms have been improved but if it is an 11.0 box, I would hard set the buffer cache.

[Bill Hassell]

There are a lot of questions to ask in terms of performance. You can certainy allocate 5-8 Gbytes of RAM for all you Oracle instances but due to the lack of real RAM, paging will destroy performance. On the other hand, if you tell the DBAs to reduce their SGA requirements so that all the instances will comfortably fit into the available memory, Oracle will attack your disks with a vengeance (unless you have just a few users that use the DB once or twice a day).

So it isn't a question of whether to run 80% RAM or even 300% RAM, it is a question of what level of performance is desired. It is amazing to me that companies will pay hug $$$ for Oracle and then cripple it's capabilities with small RAM. On the other hand, if you don't need quick response times, then small RAM is probably OK.

And all of these questions revolve around whether you are using a 64bit version of Oracle. If not, then there are even more restrictions on memory allocation, especially for Oracle's SGA (ie, shared memory).