10G Issues with writing to Redo Logs - performance degradation

Hi,

Firstly i will describe problem symptoms and after that i will give software and hardware informations.

I think that we have problems with writing to online redo logs.
We have such messages in trace files (SID_lgwr_2120.trc):

*** 2008-08-30 07:00:14.285
Warning: log write time 820ms, size 2KB
*** 2008-08-31 10:21:08.646
Warning: log write time 550ms, size 9KB
*** 2008-09-01 03:15:11.876
Warning: log write time 860ms, size 0KB
*** 2008-09-01 14:51:05.904

---------------------------------------------------------------
I know what oracle says about this kind of messages.
I've even created SR on metalink, they said me the same.
They don't give solutions for free and i think that this is still problem.
So i'm looking for advice on this forum.

Next
In statspack we have sth like this (i can't analize that) :


Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----
LGWR wait for redo copy 19 100.0
Log archive I/O 78 1.3 1.3 62.8 34.6

-------------


We have application which uses this database and two of processes of this application fail with message in event viewer. I think that this is caused by problems with writing to online redo logs (or generally some IO problems maybe ?)

---------

NOW SOME FACTS ABOUT ENVIRONMENT :

We have 10g SE (10.2.0.4) on windows 2003 Server Standard.
Computer hardware is :
-DISC : RAID PERC 3/Di configured as RAID-1
-Bus speed (processor - memory) : 133MHz
-Two processors : 993Mhz
-2GB RAM.

I think that there is no many transactions on this database :
If transactions number may be measured as time between switching redo logs, it occurs at most every 20 minutes (redo logs size : 80 MB and log buffer size : 8 MB). Another way, sql trace file on main process has 40MB after 5 hours (maybe this is "stupid" way of measuring transactions factor, Sorry)
and additionally RAID-1 is efficient as i have heard.

All database files are located on the same Disc configuration (RAID-1).

--------

So, i need some advice what can i do with this kind of issue ?
What should i check ?
Do You have some experiences in this area ?

Best Regards Arek Masny

Hi

How big are your online redo-logfiles and how many of them do you have.
Are all the redolog files on the same disk ?

regards
Hrishy

Hi Hrishy,

80MB each of 3 of them.
On the same disk.

Best Regards Arek Masny

Hi

Can you upload your whole statspack report please

regards
Hrishy

Quote:

---

Originally Posted by hrishy

Hi

Can you upload your whole statspack report please

regards
Hrishy

---

================================

HERE YOU HAVE IT AS ATTACHMENT

Hi

Your elasped time is simply too long
Elapsed: 411.00 (mins)

Please take a 15min snapshot using statspack and upload the statspack report here.

P.S I dont think so you performance problem is due to LGWR wait for redo copy.

As you top 5 waite events are

Code:

---

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                  wait  Call
Event                                            Waits    Time (s)  (ms)  Time
----------------------------------------- ------------ ----------- ------ ------
CPU time                                                      888          42.4
db file sequential read                        109,828        465      4  22.2
RMAN backup & recovery I/O                      12,167        266    22  12.7
db file scattered read                          15,667        243    16  11.6
control file sequential read                    84,972        110      1    5.3

---