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

[Wodisch]

Hi all,

I've got a strange behaviour in terms of the "commit" statements on a multi-cpu SPARC-based Oracle8.1.6 server:
- with 1 cpu "commit" is pretty fast
- with 2 (or more) even faster cpus it takes up to 10 times as long

My tracing/testing identified the access of the current online-redo-logfile as the point of lost time.

Does anybody know any cure?

TIA,
Wodisch
(back again, and happy with the new features of the ITRC)

[Elmar P. Kolkman]

Could it be that a lot of transactions are run simultaneously ? With multiple CPU's they can run really simultaneously, with this result, whereas with 1 CPU they are more or less run sequentially. At least no other session is using the redo-log while the commit is running.

[Massimo Bianchi]

Hi,
nedd some data:

- redo log: are on FS or on raw device ? think of putting them on raw devices, it could be ddone online.

- redo log size: how many and how big they are ? Usually, for performance, best is many of little size, arounf 40/80Mb each. On one of my customer, they have 90*80M redo log, it's a 2Tb DB

- redo log switch rate: checking from alter_sid, every how many minutes does a switch occur? best should be one every 15/20 minutes... change with suggestions above

- log buffer: how may Mb? It can help in such situation of high concurrency, up to 5Mb. It really depends on the typical use of the DB.

Massimo

[Steven E. Protter]

At install time Oracle compiles its binaries based on how the system is set up at the time. If it detects two cpu's it may use different compile options.

Do you still have the install logs?

Based on whats going on, it was installed when there was one cpu and none of the smp features were turned on.

On my dual processor HP boxes, I've seen horrendous performance when one of the cpus went down, much worse than can be explained merely by the cpu taking the permanent retirement plan.

Welcome back.

SEP

[Steven E. Protter]

What I was trying to tell you in my vague and fuzzy style was to relink oracle with all cpu's enabled and see if it helps. To early in the morning for me.

SEP

[Jean-Luc Oudart]

This may help

http://www.ixora.com.au/tips/use_raw_log_files.htm

Rgds,
Jean-Luc

[T G Manikandan]

Hi Wodisch,

Welcome back!Nice to see you back here!!

[Hein van den Heuvel]

Wodish,

You seem to have identified the problem already judging by the subject line:

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

It's those sparcs! Come on over to HPUX and live happiply ever ever?
:-)
Slightly more seriously... why not ask SUN for support? Are they not helping you?

How is your archiving set up? You are not logging and archiving to the same disks are you?


Massimo writes....

- redo log size: how many and how big they are ? Usually, for performance, best is many of little size, arounf 40/80Mb each. On one of my customer, they have 90*80M redo log, it's a 2Tb DB

Please. That just has to be silly / wrong.
Just 2 or 3 reasonably sized logfiles will do fine. Any more is pointless. The data should/will be in the archive logs! You indicate this yourself...

- redo log switch rate: checking from alter_sid, every how many minutes does a switch occur? best should be one every 15/20 minutes... change with suggestions above

This I agree with... largerly. Make the log files big enough to hold 10 - 60 minutes of
production redo log. With < 100MB and _any_ load worth worrying about, you are going to see checkpoints every minute or faster. All the dirty block will have to be flushed out. The odd to re-dirty a block and save an IO are dramatically reduced.

Make those redo logs large and put them on alternating disk/controllers. This will ensure that the archive read IOs do not interfere with the linear redo log writes.
(btw... I recently ran into a 60GB yes, GB, redolog file. That seemed over the top, but hey this was for a benchmark. All is fair game in love and war no?)

Actually, I kinda agree with Oracle line of thinking that the redo size / time baces checkpointing is not so important. What really really count is the tiem it would take to recover should you have a problem.
This is a business problem, not a technical problem. The technical solution they offer is the new(ish) MTTR = Mean Time To Recover parameter. Using this ORacle will no longer do the harsh identifyable checkpoints, but settle for a (likely) slower constant buffer flush algoritme making sur eit will not have too much to recover should it ever need too.

Oooops, I'm straying a bit far from the original question.

So what kind of speeds and feeds were we talking about in the first place mb/sec? gb/redo file?...

Cheers,
Hein.

[Massimo Bianchi]

40/80Mb each. On one of my customer, they have 90*80M redo log, it's a 2Tb DB

I really meant that. It a very OLTP system, for telecommunication/billing/customer support purpose.
really really stressed.

They need many redolog for two main reason:
- very fast/online recover
- they generate many archive log, and speed is a must. many small redo achieve a better performance that bigger ones, and are fater to archive, even on 2Gb FC direct connection. this was the decision of their dba....

Massimo

[Volker Borowski]

Hi Wodisch,

which patchlevel are you on ?
How did you measure the bottleneck and with esp. what values ?

I never watched this behavior on Solaris, but I have no more 8.1.6ers to support. Sounds to be the type of problem to be patchable.

Best regards
Volker