Kernel Tuning for Oracle

[James Richardson_1]

I have an N4000-55 with 4 processors and 8192 MB of memory. Oracle is the only application running on this server. Currently there are 7 databases running and the DBA's want to increase the SGA but are unable to until I make some kernel changes. What values do I need to change to support these databases and allow for future growth? Databases sizes and current SGA sizes in attachment.

Thanks,

Jay

[A. Clay Stephenson]

You will need to increase maxdsiz and maxdsiz_64bit. You should probably also increase maxssiz and maxssiz_64bit but in general 64MB for these should be plenty. The other parameter (and probably the most significant) is shmmax. It must be large enough (in bytes) to accomodate the largest single shared memory segment. I suspect that this is the value that your DBA's are bumping into. If you applied the tuned parameter set for databases make sure that timeslice is set to 10. If the value is currently at 1 set it back to 10 and leave it there.

[James Richardson_1]

What values would you assign to the parameters? This box is in production so I get one chance to set it right.

Jay

[Tom Maloy]

Would be helpful if you could attach your current settings. For example, max_dbc_size defaults to 50 (% of RAM), and should usually be much lower.

Tom

[Ray Brewer]

We have set the following parameters to the following values and it seems to work fine with systems that have anywhere from 1 to 20+ databases

maxdsiz = 1GB
maxdsiz_64bit = 1.7GB
maxtsiz = 1GB
maxtsiz_64bit = 1GB
maxssiz = 200MB
maxssiz_64bit = 200MB
shmmax = 1GB
max_thread_proc

These are the main ones but there are others. Oracle has a document out that defines all of the changes. It can be found on there web site.

Ray

[A. Clay Stephenson]

You need to do a kmtune or a sysdef and post that. Also, an estimate of the number of users would be helpful and some sort of estimate of the transaction load. I have to tell you that 8GB is not a whole lot of memory for 7 Oracle instances.

[James Richardson_1]

I'm attaching a sysdef.

[James Richardson_1]

Also a Kmtune.

[A. Clay Stephenson · Accepted solution]

You really should attach a kmtune listing because sysdef does not display shmmax.

maxdsiz is only 64MB now; I would increase it to 512MB. Maxdsiz_64bit is now 1GB you probably want to increase that to about 1.5GB.

Maxssiz is only 8MB; I would bump it to 32MB and maxssiz_64bit is now 32MB; I suggest 64MB.

Now for your real killer. You have set bufpages
at 734003 pages or ~ 2.9GB; if this is a 11.0 box then I suggest that you reduce buffer cache
to no more than about 800MB and you will probably find that 400MB is better still. If you are running 11.11 then 1GB (but no more) will probably be a good value.

You could also choose to enable dynamic buffer cache by setting dbc_max_pct to about 5-10% and dbc_min_pct to about 3% (and setting bufpages to 0) but I find that on 11.0 boxes
static buffer caches tend to perform better but on 11.11 dynamic and well-tuned static buffer caches are comparable.

If you are running raw volumes or the OnlineJFS equivalent mount options (convosync=direct,mincache=direct) then you can really reduce your buffer cache because much of it is going unused.

You really need to work with your DBA's and tell them how much memory you have and let them tell you how large an SGA they are looking at.

[Anonymous]

Jay,

do mind
indicating to what extend the answers have been helpful
by
assigning points?

appreciate your effort,
thx, Tom.