Oracle8.1.6 32bit DB slow down with SDE CPU PA8700 * 20, HPux 11i

[Henry_3]

Hello there,

I have the problem of oracle 8.1.6(32bit) about DB performance on HP-UX 11.i.

I show you my environment.

My server is Superdome 32way PA8700 * 20, memory 20GB.

Disk is HDS L9900, Cache 32GB.

DB is 8.1.6 32bit, SGA 1.6GB with Raw Device
DB Size is 1.3 TB

After changing SDE from v2600(12CPU,Mem12GB), Our DB is very frequently slowing down.

I checked Oracle Dump & Syslog etc.
But I could not find anyting.

I only found buffer busy wait. and then

I change db_block_lru_latchs 40 => 80
DBWR 6 process => /dev/async in max_async_ports is 50.

But still the problem isn't clear.

When oracle is working on online & batch program, for instance,

my transactions are 4 million a day,
DML batch program running cuncuruntly.

When problem occuerd CPU usage is 5%(normal 15%), I/O workload is low(normal 90 ~ 100%).

I can not guess what causes problem.

As a comment by DBA, it takes too much time for oracle db-writer to write on disk and it causes buffer busy waits.

Where do I have to start?
patche? kernel parameter? Confused.

I'd be really appreciate if you give me any hints or thoughts.

[Volker Borowski]

Hi Henry,

it would be of help, if you could attach the init.ora to this thread and tell us on what Oracle patch level you are on.

Do you have a protocol.ora file in .../network/admin with "tcp.nodelay=true" in it ? Requires to restart the listener.

May be you have fragmented or degenerated indexes. Check out, if you have indexes, that are significantly bigger than the tables they belong to (query dba_segments on this). For this situation, it would even make sense to see buffer busy waits, as too many blocks are read in this case. Has to bee solved with an Index reorg or ALTER INDEX REBUILD / ALTER INDEX COALESCE.

Even if you have 20G RAM, you will have trouble to utilize it with 32-Bit SW.

Volker

[Krishna Prasad]

There could be several reasons for the poor response on the new box (env).

It would help if you can upgrade Oracle to 64bit. In your current situation you can only give Oracle SGA max of 1.75 GB. If you can upgrade Oracle you can increase the SGA much larger.

Is the disk layout as solid as it was on the previous box? I.E. datafiles not on same physical drive as indexes.

Are the kernal parms the same as the old box?

Are the patches the same on the superdome then on the v box? Where both operating systems 11i?

What is your Oracle hit/ratio?

What does glance show?

[A. Clay Stephenson]

Hi:

Whenever I hear of DB performance problems especially on new boxes, I immediately want to know the timeslice setting. There are a number of tuned parameter sets that very stupidly set timeslice to 1 rather than 10 and that can cause all sorts of problems.

I would post your kmtune output and your init.ora.

[Dave Chamberlin]

It sounds like a kernel parameter problem - but it certainly helps if you identify a problem before you try to fix it. Glance is quite useful in helping to to identify your primary bottleneck. Also you should run the oracle utlb/le stat scripts during the time of poor performance. You can then see what is generating the most wait time. Together these should point you to the resource problem.

[Dennis J Robinson]

Henry,

We would love to help you. However, going from the V to the SD should provide you gains, even if you don't make use of the memory you have.

First of all if your client application needs 32 bit, then you should use a "dual mode" where the client connects through a 32bit Oracle to a 64bit Oracle database side.

How many connections to the database do you have? max_async_ports should be at least greater than this number.

Do an 'lsdev', you should see a line similar to:

101 -1 asyncdsk pseudo

The /dev/async driver should exist and an ls -l on it should appear as:

crw------- 1 oracle dba 101 0x000000 Jan 6 03:33 /dev/async

I would like to assist you further. Please paste your init.ora settings into a reply to this email.

Thanks,

Dennis J Robinson