Oracle8.1.6 on SPARC 2-4 CPU takes ages on COMMITs

[Wodisch]

Hi guys,

thanks for your responses so far, but the problem is:
- it is 8.16
- it runs on a SPARC
- the whole DB resides in "ufs" logging filesystem (on "MD" mirrors)
- Oracle *was* installed with all cpus active (and even relinked later)
- the size and amount of redologs is not the problem, as *they* (the cpus, or whoever else) slows down when accessing the *one and only current* redo-logfile
- a slower single-cpu SPARC is much faster (not for anything else, only for the "commit" statements) in total
- the customer cannot/will not change the patch level, as some other software depends on what is already there...

so, to ask in other words: does anybody know any problem of multi-cpu SPARCs running oracle getting into some
- race-condition
- deadly embrace (well, not deadly, but dog-slow)
- file I/O concurrency
- filesystem concurrency

what I cannot pinpoint is wether the delay is caused by oracle (internally) or by the filesystem (Solaris, then). From application point of view only the SQL-statement "commit" is slow, and the Oracle performance tools tell me that the most time is spent on "syncing" the data to the current redolog...

any hints?

TIA,
Wodisch

[Volker Borowski]

Hmm,

which Solaris version is it 7 or 8 ?

Is Solaris Async-IO enabled ?
If yes, there had been quite a few op-sys patches in Solaris for this feature (up to date ?).
If not, consider to set up
Oracle-LGWR_IO_SLAVES to parallelwrite the redo info (Oracles own implementation of application-async-io). DO NOT USE BOTH!

Logwriter writes, if log_buffer in one third full or if a commit is issued. How big is your log_buffer ? How many Commits do you get per minute ?

Metalink has some stuff for tuning redo_log_sync, mostly sugesting to reduce the amount of commits on application side *smile*.

What do you compare ? A single commit on both platforms ? A bunch of 1000 in parallel ?

I guess, this one will be hard to track down ....
Good hunting
Volker

[Jean-Luc Oudart]

Wodish,

seen on Metalink some entries with same type of pb (SPARC) wher one cpu SPARC is better that multiple cpu box !

Check doc id 134172.999 on Metalink
This was 1st reported as a bug but then it was not a big ?!

Not sure this is xactly your pb , but this is close to it.
other docs, seacrh on 929709 (the bug number , but not a bug ?!)

Rgds,
Jean-Luc

[Stan_17]

Hi Wodisch,

what's the top 5 waits events seen on the statspack reports ?

Is too many waits spent on log file sync, redo log file parallel write, log buffer space ?

how many txns are you commiting / sec (should be seen in statspack)

Is your FS using direct io, whats the value set for disk_async_io

-
Stan

[Wodisch]

Hi again,

it is Solaris8, and I never use async-IO ;-)
The redologs are 100MB each, and logswitches happen only every couple of minutes, that does not seem to be the problem.
Reducing the amount of commits is NOT an option ;-)
The most simple way to reproduce the bottleneck was to do some inserts and a few selects then commit. Repeated a 1000 times.
So actually no parallelism inside the program itself...

The statspack's snapshot output shows:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
log file sync 1,002 2,468 41.29
log file parallel write 1,004 2,456 41.09
control file parallel write 116 1,051 17.58
file open 32 1 .02
db file sequential read 4 1 .02
-------------------------------------------------------------

The values for a much slower single cpu system is 1/10th for the first two events!

Oh, and the disks on the multi-cpu system are much faster, too, as are the controllers...

I am pretty lost on that. My guess that Solaris8 is doing "spinlocks" on the multi-cpu system and waisting a hell-of-a-lot-of-time on that.
We do experience that behaviour on two pretty different systems, 2-cpu-system, and a 4-cpu-system, both with different hardware - so it looks pretty much like a software issue.

Any other ideas?

Thanks a lot!

[Stan_17]

comments interspersed.

it is Solaris8, and I never use async-IO ;-)
>
yes, solaris by default uses threaded async/io to simulate kernalized async/io. though its not as efficient as kaio, but still its okay as oppose to nothing :)

The redologs are 100MB each, and logswitches happen only every couple of minutes, that does not seem to be the problem.
>
yes, thats not your problem. i don't see any wait events alluding to checkpointing.

Reducing the amount of commits is NOT an option ;-)
>
absolutely not. many suggest these options, but ideally this will not be the solution to most of 3 party vendor applications ;) you know what i mean.

The most simple way to reproduce the bottleneck was to do some inserts and a few selects then commit. Repeated a 1000 times.
So actually no parallelism inside the program itself...

The statspack's snapshot output shows:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
log file sync 1,002 2,468 41.29
log file parallel write 1,004 2,456 41.09
control file parallel write 116 1,051 17.58
file open 32 1 .02
db file sequential read 4 1 .02
-------------------------------------------------------------

>
seems like your fs is not coping with lgwr. do you multiplex redologs. if yes, then try not multiplexing. since your are mirrioring the logs, you should be okay. other thing is, use direct/io on the fs that has redologs on it. That will improve the lgwr performance.

The values for a much slower single cpu system is 1/10th for the first two events!

>
any chance the tests are carried out bit different as oppose to multiple cpus for e.g. test were done via pl/sql procedure, This can make lot of difference.

Oh, and the disks on the multi-cpu system are much faster, too, as are the controllers...

I am pretty lost on that. My guess that Solaris8 is doing "spinlocks" on the multi-cpu system and waisting a hell-of-a-lot-of-time on that.

>
If thats the case you should see cpu cycles being use a lot. do you see them? i don't think so.

We do experience that behaviour on two pretty different systems, 2-cpu-system, and a 4-cpu-system, both with different hardware - so it looks pretty much like a software issue.

Any other ideas?

Thanks a lot!

[Alzhy]

Interesting... it seems you are comparing two different boxes - one with a single CPU and the other multiple... How sure are you that there OS rev and configuration are the same? On the multiple-CPU system, you can test by dropping it down to 1 CPU via the "psradm" command.
.
Also, if your're using cooked UFS for your datafiles -- make sure you are using at least Solaris 8 07/01 (cat /etc/release) and at least a -13 Kernel Rev.

[Wodisch]

Thanks so far,

we are still working on that :-(
To clarify some points:
- only single-member redologs are used
- the problem happens on different HW and even on Oracle8.1.7

Regards,
Wodisch

[Stan_17]

Hi Wodisch,

I'm very much intrested on the outcome of the issue and how you fixed it. Keep us updated.

-
Thanks,
Stan

[Wodisch]

Stan,

if possible I'll do that.
But it may be one of those "unresolved" questions of mine :-(

Regards,
Wodisch