topic Re: Tuning a Monster in Operating System - HP-UX

Tuning a Monster

Ross Zubritski — Fri, 28 Mar 2003 17:04:05 GMT

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

Re: Tuning a Monster

H.Merijn Brand (procura — Fri, 28 Mar 2003 17:09:56 GMT

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

Re: Tuning a Monster

Jeff Schussele — Fri, 28 Mar 2003 17:12:40 GMT

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

Re: Tuning a Monster

James R. Ferguson — Fri, 28 Mar 2003 17:14:18 GMT

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...

Re: Tuning a Monster

Sridhar Bhaskarla — Fri, 28 Mar 2003 17:15:20 GMT

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

Re: Tuning a Monster

Ross Zubritski — Fri, 28 Mar 2003 17:18:25 GMT

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>

Re: Tuning a Monster

A. Clay Stephenson — Fri, 28 Mar 2003 17:19:25 GMT

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.

Re: Tuning a Monster

Ross Zubritski — Fri, 28 Mar 2003 17:49:40 GMT

Jeff,

There are 4 metas involved.

JRF,

Could you please expound a bit on mount options?

I have attached current fstab.

Regards,

RZ

Re: Tuning a Monster

Yogeeraj_1 — Fri, 28 Mar 2003 17:57:42 GMT

hi rz,

what about a statspack report?

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

regards
Yogeeraj

Re: Tuning a Monster

Ross Zubritski — Fri, 28 Mar 2003 18:00:26 GMT

Not familiar with statspack

Thanks

Re: Tuning a Monster

Yogeeraj_1 — Sat, 29 Mar 2003 05:01:32 GMT

hi again,

STATSPACK
=========
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.

Regards
Yogeeraj

Re: Tuning a Monster

Steven E. Protter — Sun, 30 Mar 2003 03:07:14 GMT

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.

SEP