Tuning a Monster

[Ross Zubritski]

Hi All!

I have attached a kmtune output from a soon to be production RP7400 8X750Mhz 16GB Memory. The I/O is dual channeled through a McData 1GB switch running powerpath. The disk attached is right at 1TB, hardware striped, 1MB stripe depth. The RDBMS is 9ias. OS block size 8K, 16K Database block size.

Any input would be appreciated.


My concern is dbc_max_pct. Can you give me any other hints?

Thanks in advance.

RZ

[H.Merijn Brand (procura · Accepted solution]

Not that I have anything to say about the values itself, but you might like to try the attached script to get a completely different view of those settings. It has helped me a lot in the past, especially when clients have replaced formulas with fixed values.

Enjoy, have FUN! H.Merijn

[Jeff Schussele]

Hi RZ,

Absolutely - you don't want dbc_max_pct at 50%. Don't think you'll EVER need 8GB of disk buffer. Depending on how the Oracle SGA is configured & what the mount options will be for the Oracle extents, you may be able to get away with
dbc_min_pct => 2
dbc_max_pct => 5
That still gives you between 320 & 800 MB for buffering.

Did they give you JUST one LUN for ALL of Oracle? I'd definitely not like that.

My $0.02,
Jeff

[James R. Ferguson]

Hi Ross:

With a 'dbc_max_pct' value of 50, and 16GB of memory, the Unix buffer cache could theoretically approach 8GB. I doubt you want that. The poor 'syncer' daemon is probably running like mad every 30-seconds! I'd suggest a much more conservative value like <2> for 'dbc_min_pct' and <5> for 'dbc_max_pct' assuming RDBMS is buffering and assuming VxFS mount options that bypass the Unix buffer cache.

Regards!

...JRF...

[Sridhar Bhaskarla]

Hi RZ,

dbc_max_pct. No doubt about it. Since you have both nbuf and bufpages set to 0, dbc will play a role here.

Since you have 16GB, I would say start with dbc_max_pct=2 and dbc_min_pct=2.

-Sri

[Ross Zubritski]

Another tidbit:

SQL*Plus: Release 9.2.0.2.0 - Production on Fri Mar 28 11:15:20 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect /as sysdba
Connected.
SQL> show sga

Total System Global Area 1009561320 bytes
Fixed Size 737000 bytes
Variable Size 167772160 bytes
Database Buffers 838860800 bytes
Redo Buffers 2191360 bytes
SQL>

[A. Clay Stephenson]

The first thing that I would do is greatly reduce dbs_max_pct to no more than about 10%. Actually, I would set bufpages to 262144 (1GB - a good value for an 11.11 box with lots of memory). By fixing buffer cache, you make it easier to tune other things because of much less interaction; you also eliminate some kernel overhead.

I would also reduce massiz_64bit; any code that needs that much stack space requires very serious programmer adjustment (with a ball-peen hammer - 128MB should be a gracious plenty).

I would also reduce ninode to no more than 1000 or so - this paramter only applies to hfs filesystems and you almost certainly have one one - /stand.

Your shared memory could bprobably be bumped up as Oracle likes very large SGA's.
Some of your msgxxx and semxxx
values look small; check for Oracle suggested values.

Let nobody persuade you that setting timeslice to a small value (e.g. 1) is a good idea. Leave it at 10.

[Ross Zubritski]

Jeff,

There are 4 metas involved.

JRF,

Could you please expound a bit on mount options?

I have attached current fstab.

Regards,

RZ

[Yogeeraj_1]

hi rz,

what about a statspack report?

let's see what a 15 minutes interval, during high load, gives for this monster.

regards
Yogeeraj

[Ross Zubritski]

Not familiar with statspack

Thanks