topic Re: Kernel Tuning for Oracle in Operating System - HP-UX

Kernel Tuning for Oracle

James Richardson_1 — Mon, 09 Sep 2002 17:07:49 GMT

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

Re: Kernel Tuning for Oracle

A. Clay Stephenson — Mon, 09 Sep 2002 17:16:15 GMT

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.

Re: Kernel Tuning for Oracle

James Richardson_1 — Mon, 09 Sep 2002 17:25:48 GMT

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

Jay

Re: Kernel Tuning for Oracle

Tom Maloy — Mon, 09 Sep 2002 17:30:32 GMT

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

Re: Kernel Tuning for Oracle

Ray Brewer — Mon, 09 Sep 2002 17:32:35 GMT

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

Re: Kernel Tuning for Oracle

A. Clay Stephenson — Mon, 09 Sep 2002 19:49:57 GMT

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.

Re: Kernel Tuning for Oracle

James Richardson_1 — Mon, 09 Sep 2002 20:06:54 GMT

I'm attaching a sysdef.

Re: Kernel Tuning for Oracle

James Richardson_1 — Mon, 09 Sep 2002 20:07:35 GMT

Also a Kmtune.

Re: Kernel Tuning for Oracle

A. Clay Stephenson — Mon, 09 Sep 2002 20:24:28 GMT

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.

Re: Kernel Tuning for Oracle

Anonymous — Tue, 10 Sep 2002 05:05:35 GMT

Jay,

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

appreciate your effort,
thx, Tom.

Re: Kernel Tuning for Oracle

Christos Magdalinos — Tue, 10 Sep 2002 05:45:03 GMT

Hi Jay,

if you have a valid support contract with HP OR Oracle
ask them to suggest the proper
values.

Your question keeps coming up in the forum.

There is an HP / Oracle alliance called CTC their
website is
www.hporaclectc.com
Unfortunately this site is open only to HP & Oracle employees.

Ask your support contact to
access it and provide you with
their tuning guide .. It is the only complete tuning guide I 've seen so far.

Regards

Re: Kernel Tuning for Oracle

James Richardson_1 — Tue, 10 Sep 2002 11:38:00 GMT

When I look at maxdsize it is represented as 0x04000000. I know from other posts that this is 1 Gig but how do you derive that from 0x04000000.

Re: Kernel Tuning for Oracle

Dietmar Konermann — Tue, 10 Sep 2002 13:00:30 GMT

$ echo "0x04000000=U" | adb
67108864

So 0x04000000 is 64 MB. Not 1GB!

$ echo "0d1073741824=X" | adb
40000000

So 0x40000000 is 1 GB.

Regards...
Dietmar.

Re: Kernel Tuning for Oracle

Yogeeraj_1 — Wed, 11 Sep 2002 04:11:00 GMT

hi,

0x04000000 is in hexadecimal.
^
hex

You can even use your scientific calculator for this operation. ;)

i have attached a document from metalink about "HP-UX: SGA Sizing Issues on HP-UX "

Hope this helps!

Best Reagrds
Yogeeraj