Huge Volume of Redo generation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Huge Volume of Redo generation

  1. #1
    Join Date
    Dec 2006
    Posts
    5

    Huge Volume of Redo generation

    Hi Frnds,
    I have an interesting issue. One of my prod database archive log frequency had reduced from 2Hrs to 3 min step by step avg since nov. Redo size is 100MB. I am sure my database is not that much hot. I observed the amount of changed blocks for 1hr (as per a metalink doc). It totals to around 50MB only. No direct load etc. Any guesses whatz going into redo log? My last attempt is about to explore log using Logminer. Before going into that, what are the likely causes?
    No new index created/rebuilded since nov.

    Thanks for you support.

  2. #2
    Join Date
    Oct 2006
    Location
    Mumbai
    Posts
    184
    Have you set archive_lag_target to some value (in your case 2 minutes) ?

    Please check.

  3. #3
    Join Date
    Dec 2006
    Posts
    5
    Hi,
    archive_lag_target=0. I removed auditing and AQ

    AQ_TM_Processes =0
    Transaction_Auditing=FALSE

    It helped me little bit. CUrrent Redo frequency is avg 20mins. Still exploring other possible cause.

    Thanks

  4. #4
    Join Date
    Dec 2006
    Posts
    5
    Oracle version is 9.2.0.6

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    do u do lots of index rebuilds?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Dec 2006
    Posts
    5
    Hi,
    There was no new index creation/rebuild since nov ( I mentioned in the top of the thread).

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    try this?..

    select ss.sid, sn.name, ss.value
    from v$sesstat ss,
    v$statname sn
    where sn.statistic# = ss.statistic#
    and sn.name in ('redo entries','redo size') ... see for fast increasing values..

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    u say 9.2.0.6.. but anyways look at this.. Bug 2186174 - Excessive REDO generation from parallel rollback
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Dec 2006
    Posts
    5

    Thumbs up

    Hi All,
    Abhaysk, Thanks for the support. Finally i got the hint from dba_waiters joining v$session and v$sql. It was series of update query updating same table, which was blocking all the sessions in application flow. It has been re-written and now the redo generation back to normal. 3 Hrs/switch average. Logminer too shown the same statement which helped me to narrow down to the issue.

    Thanks

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