10G Issues with writing to Redo Logs - performance degradation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: 10G Issues with writing to Redo Logs - performance degradation

  1. #1
    Join Date
    Feb 2008
    Posts
    31

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    Feb 2008
    Posts
    31
    Hi Hrishy,

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

    Best Regards Arek Masny

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Can you upload your whole statspack report please

    regards
    Hrishy

  5. #5
    Join Date
    Feb 2008
    Posts
    31
    Quote Originally Posted by hrishy
    Hi

    Can you upload your whole statspack report please

    regards
    Hrishy
    ================================

    HERE YOU HAVE IT AS ATTACHMENT
    Attached Files Attached Files

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width