LOG SWITCHES/CHECKPOINTS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: LOG SWITCHES/CHECKPOINTS

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Hi All
    Frequent log switches hurt/degrade performance.
    recommendation from Oracle states an average of 30-45 daily that's about once every 30 mn or so.

    Is there any reprecautions or what will rather be the impact on the system that perform 15-18 log switches

    Please advise
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by Ablakios
    recommendation from Oracle states an average of 30-45 daily that's about once every 30 mn or so.

    That's certainly the conventional wisdom. Personally, I believe that you can switch as often as you like as long as your I/O subsystem can handle it and you're not experiencing any LGWR waits.


    Is there any reprecautions or what will rather be the impact on the system that perform 15-18 log switches
    As far as performance goes, fewer log switches is fine. You run the risk of losing a larger period of data if you lose one of your online logs, but fewer switches is fine.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    One Oracle recommendation is having a log file switch occur every 20-30 minutes during normal database usage (48-72 times per day; don't know how you got 30 minutes out of 30-45 daily).

    Smaller log file => less data potentially lost if that log file is not yet archived => more of a drain on the system because of the overhead to switch log files that frequently.

    Larger log file => less drain on performance, but risk losing larger chunks of data.

    Decision: depends on what you consider to be more important, what the trade-offs are, etc. There is no one single answer for every user.

  4. #4
    Join Date
    Feb 2001
    Posts
    290
    To my understanding from your POST ,
    You are having checkpoint at every log switch ( ONLY ), and log switch is occuring ( 15-18 time )when the redo log is getting filled up. That means you have a big redo size , that will be absolutely OK. Only disadvantage here is your recovery time will be more.

    In my DB(s) , i have manged to have 24-30 checkpoints/log switches a day.


    Madhu Reddy
    xdollor@yahoo.com

  5. #5
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    mrvajrala
    You are absolutely right and I can control faster recovery with FAST_START_IO_TARGET.

    On another note the vendor (developer/dba) who comes and go and used to manage the database never tune this system before I was hired and noticed excessive log swithes about
    269 to 634/day.

    I just resized the online redo logs and this is the 2nd business day that I am monitoring the log swithes.

    Thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  6. #6
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    Everytime a Log Switch occurs, if your db is in Archive Log Mode then the Archiver Starts and begins to write to the disk.

    I've seen terrible performance degradation when doing bulk loads into the databases with smaller log files.

    If the OS can handle continuous writes, ie if the Archive Destination is on a separate disk drive, then log switch should not be an issue. It becomes an issue only when logs are switching in terms of seconds. A log switch defines a state of consisteny of a DB.

  7. #7
    Join Date
    Feb 2001
    Posts
    290
    There is a BUG associated with FAST_START_IO_TARGET

    pl find the link

    http://metalink.oracle.com/metalink/...Bug&id=1711803

    Improper value to this parameter will take you back to ole situation. Its upto you ...
    Madhu Reddy
    xdollor@yahoo.com

  8. #8
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    It depends on

    a). Your recovery time period, i.e. if you want to recover as close to the point of failure as possible. Switching every 5 minutes would be better than switching every 20 minutes. Oracle does recommend every 20 minutes (obviously, every databases requirements are different). And is that 20 minutes during quiet time or busy time.. again this is VERY open to interpretation.

    b). You ARCHIVING process, have you enough time to archive off your logs before they are required again.

    Cheers,

    OCP 8i, 9i DBA
    Brisbane Australia

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