Redo logs
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Redo logs

  1. #1
    Join Date
    Apr 2001
    Posts
    112
    Hi
    I am having very poor performance of redo log in my database.

    I normally run long running batch processes on our database.
    I am seeing the following:
    redo log space requests ===1198
    redo log space wait time ===68814


    I have my log_buffer set to 5242880
    we have only 2 mirrored redo log files of size 200MB.

    Should I add more redo log files ? What size?

    Any other suggestions appreciated.


  2. #2
    Join Date
    Aug 2001
    Posts
    111
    With only the information provided...

    Increase the no. of redo logs. (+2 perhaps)
    The same size.

    I would test making the log_buffer smaller too
    perhaps 512K instead of 5M.
    (Just maybe this will help...)

    Have Fun


    Here is what the doco (ver8 says)

    redo log space requests:

    The active log file is full and Oracle is waiting for disk space to be allocated for the redo log entries. Space is created by performing a log switch.

    Small Log files in relation to the size of the SGA or the commit rate of the work load can cause problems. When the log switch occurs, Oracle must ensure that all committed dirty buffers are written to disk before switching to a new log file. If you have a large SGA full of dirty buffers and small redo log files, a log switch must wait for DBWR to write dirty buffers to disk before continuing.

    Also examine the log file space and log file space switch wait events in V$SESSION_WAIT.

    redo log space wait time:

    The total elapsed time of waiting for redo log space request in 10s of milliseconds.
    Performance... Push the envelope!

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Hmmm, redo_buffer. You can have a bottle neck when writting to the REDO LOGS. Now, Oracle suggests that you probably won't get much of a boast in perfmorance in your REDO_BUFFER by increasing it > 1 MB. Even though.. I increased mine to 4 MB, and performce has been better. I'd look to see what device your REDO_LOGS are on, since writing to redo logs is performed sequentially, there is NO benefit is placing them on any drive that is stripped.

    I also agree with roobaron what suggested, definatley add an additional 2 logs, make this change before fiddleing with ou LOG_BUFFER sizing.

    Cheers,

    OCP 8i, 9i DBA
    Brisbane Australia

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Good size for redo logs is 4-10M.
    LOG_BUFFER should be at most 1M in 8i. There are several reasons for that. You cannot have better performance if you increase LOG_BUFFER from 1M to 4M. (I assume grjohnson meant LOG_BUFFER not REDO_BUFFER).
    2 redo log groups is the minimum, 3 is better, some DBA preffer to have 4 or 5.


  5. #5
    Join Date
    Aug 2000
    Posts
    236
    I think your problem is more with the size of your redo log file than the buffer. Let me explain.

    2 files of 200 M each. When one gets filed up, a logswitch/checkpoint occurs which cause DBWR to move all dirty buffers to the datafiles. In the meanwhile your other log file is getting filled up fast enough and it becomes full before the checkpoint on the first file is over. Naturally, when the second file fills up it has to wait to clear out and causes the log_buffer to fill up which in turn causes users to wait for space in the log buffer.

    Check your alert log file for "Checkpoint not complete" messages. If they exist then reduce the size of your log files/ increase number and also analyze your LOG_CHECPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT parameters.

    Nizar.

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    I agree that the size is a problem. I have never heard before of a 200M redo log file.

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    I say reduce your redolog size to 10M,
    make it at least 3 groups,
    log_buffers to 1M,
    log_checkpoint_interval greater than 10M or 99999999
    log_timeout to 0.
    And do not put your redologs to RAID5/or with stripping.
    Separate redologs disk source from archive logs disk source.


  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    I suggest follow the advise above. I personally set log_checkpoint_interval = 9999999 :-) not 99999999

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    200M log is not that big, I am just sitting on 4 DWH instance with redo log member of 250MB each and 5 groups

    Itīs all depends on your db size, transactions & batch jobs etc

  10. #10
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Well if you are going to use that much redo log size(200M),
    just make sure you have more than enough number of groups to make sure always have available redo logs.
    Anyway, your setup for redo logs size is for Data Warehousing/Datamart or DSS.

    I don't know if tansdot has a DSS system or OLTP type.

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