Showing results for 
Search instead for 
Did you mean: 

Tuning a Monster

Go to solution
Ross Zubritski
Trusted Contributor

Tuning a Monster

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.

H.Merijn Brand (procura
Honored Contributor

Re: Tuning a Monster

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
Enjoy, Have FUN! H.Merijn
Jeff Schussele
Honored Contributor

Re: Tuning a Monster

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,
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
James R. Ferguson
Acclaimed Contributor

Re: Tuning a Monster

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.


Sridhar Bhaskarla
Honored Contributor

Re: Tuning a Monster

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.

You may be disappointed if you fail, but you are doomed if you don't try
Ross Zubritski
Trusted Contributor

Re: Tuning a Monster

Another tidbit:

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

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

SQL> connect /as sysdba
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
A. Clay Stephenson
Acclaimed Contributor

Re: Tuning a Monster

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.

If it ain't broke, I can fix that.
Ross Zubritski
Trusted Contributor

Re: Tuning a Monster


There are 4 metas involved.


Could you please expound a bit on mount options?

I have attached current fstab.


Honored Contributor

Re: Tuning a Monster

hi rz,

what about a statspack report?

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

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ross Zubritski
Trusted Contributor

Re: Tuning a Monster

Not familiar with statspack

Honored Contributor

Re: Tuning a Monster

hi again,

You should be using statspack on a constant basis.

Every morning, you should take a snapshot, every afternoon another, every evening, yet another.

Now you have a history. You can compare a statspack from today (bad performance) with last weeks at the same time (good performance) and look for major differences.

Also, people must "quantify" things. Eg: Screen 1 typically takes less then 1 second, today it is taking 60 seconds. -- Ah ha, maybe we lost an index on some of the tables surrounding screen 1, lets look at that. Are there specific components "going slow" or is the entire thing going slow.

Statspack will help you identify the top sql, the big wait events, contention points, bad performance metric (eg: the soft parse ratio is my personal favorite).

Also, attack this from two points - get the SA's looking at the machine, network, disks, etc.

As it is now, if you don't have a history of what "good" looks like - it is REALLY REALLY hard to figure out "badness". You need to gather more information, isolate the issue if possible and go from there.

If you need any further help, let us know.

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Steven E. Protter
Exalted Contributor

Re: Tuning a Monster

Let me chime in and say dbc_max_pct is a big possible problem.

I'm attaching a data collection script for you to measure performance over time.

Steven E Protter
Owner of ISN Corporation