This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Sep 23, 2010 7:49 PM by Aman.... RSS

log switch must occur every 15 to 30 minutes: good practice or myth?

705249 Newbie
Currently Being Moderated
I often heard that it is recommended to size redo log files in a way that Oracle performs a log switch every 15 to 30 minutes.

I would like to know if it is true or if it is a myth ?
I would like to know the best way for sizing redo log files ?
What happen exactly in the database when a log switch occurs ?

Please help me to clarify these concepts.
  • 1. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    EdStevens Guru
    Currently Being Moderated
    Farenheiit wrote:
    I often heard that it is recommended to size redo log files in a way that Oracle performs a log switch every 15 to 30 minutes.

    I would like to know if it is true or if it is a myth ?
    Probably neither. It is considered to be a "sweet spot" between switching too often (which may lead to some performance issues) and not often enough (which may cause a recovery situation to take longer.

    Considering that the update activity on a db is generally not perfectly even, I don't get to exercised about trying to hold to this. There may very well be some spikes, particularly during a batch operation, where you get a few log switches every minute. You have to ask yourself, "well, what did that really cost me?" If I have a batch operation running off-shift (no human users being impacted) and it takes 20 minutes to run, how much effort should I put in to getting it down to 15 minutes? 10 Minutes? 1 minute?

    If my on-line transactions are averaging 0.75 seconds to complete, how much effort should I put in to a 10% improvement? A 50% improvement? A 90% improvement?

    This is not to say that one should not be concerned about performance, and certainly performance should be a big consideration at design time. But once a system is up and running, you have to weigh the cost/benefit ratio to any tuning effort. If the only place the result of the tuning effort will ever be perceived is in numbers on a performance report, then it probably isn't worth the effort to fix.

    I would like to know the best way for sizing redo log files ?
    What happen exactly in the database when a log switch occurs ?

    Please help me to clarify these concepts.
  • 2. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    705249 Newbie
    Currently Being Moderated
    thanks for your answer,

    what about my last question?
  • 3. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Centinul Guru
    Currently Being Moderated
    The documentation is your friend :)

    Online Redo Log Switches
  • 4. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Mark D Powell Guru
    Currently Being Moderated
    I think Ed covered the reasoning behind sizing fairly well. I have always like to shoot for between 24 and 48 log switches per day. I have never found that larger logs seem to have much effect on database restart time after a crash.

    And what happens when the online redo logs switch is indeed well explained in the documentation but can be summed as all database data files are made consistent to a specific point in time. This means that the dirty blocks in the buffer cache are flushed to disk and the database data file headers and control file are updated with the SCN.

    HTH -- Mark D Powell --
  • 5. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    705249 Newbie
    Currently Being Moderated
    Mark D Powell wrote:
    And what happens when the online redo logs switch is indeed well explained in the documentation but can be summed as all database data files are made consistent to a specific point in time. This means that the dirty blocks in the buffer cache are flushed to disk and the database data file headers and control file are updated with the SCN.

    HTH -- Mark D Powell --
    So we can say that a log switch induces a full checkpoint. That's correct ?
  • 6. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Mark D Powell Guru
    Currently Being Moderated
    Considering that the 10gR2 glossary defines a checkpoint as "A data structure that defines an SCN in the redo thread of a database. Checkpoints are recorded in the control file and each datafile header, and are a crucial element of recovery." then yes. A checkpoint always occurs on log switch. See the Reference manual entry for log_checkpoint_interval for this fact.

    HTH -- Mark D Powell --
  • 7. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Aman.... Oracle ACE
    Currently Being Moderated
    So we can say that a log switch induces a full checkpoint. That's correct ?
    Well, no :-) . Indeed with a log switch the checkpoint event is generated requesting DBWR to flush the buffers from the checkpoint-queue to the data files but this is not a full checkpoint and it would not update the datafile and control file headers. The Full Checkpoint happens with the alter system checkpoint ( as shown) , database being shutdown properly.

    Please see the below code which shows the same,
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select checkpoint_change# from V$datafile;
    
    CHECKPOINT_CHANGE#
    ------------------
               3507400
               3507400
               3507400
               3507400
               3507400
               3507400
               3507400
    
    7 rows selected.
    
    SQL> select * from V$log;
    
        GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
    ---------- ---------- ---------- ---------- ---------- --- ----------------
    FIRST_CHANGE# FIRST_TIM
    ------------- ---------
             1          1         91   52428800          1 NO  CURRENT
          3507399 13-APR-10
    
             2          1         89   52428800          1 NO  INACTIVE
          3486253 13-APR-10
    
             3          1         90   52428800          1 NO  INACTIVE
          3486256 13-APR-10
    
    
    SQL> alter system switch logfile;
    
    System altered.
    
    SQL> select * from V$log;
    
        GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
    ---------- ---------- ---------- ---------- ---------- --- ----------------
    FIRST_CHANGE# FIRST_TIM
    ------------- ---------
             1          1         91   52428800          1 NO  ACTIVE
          3507399 13-APR-10
    
             2          1         92   52428800          1 NO  CURRENT
          3507906 13-APR-10
    
             3          1         90   52428800          1 NO  INACTIVE
          3486256 13-APR-10
    
    
    SQL> select checkpoint_change# from V$datafile;
    
    CHECKPOINT_CHANGE#
    ------------------
               3507400
               3507400
               3507400
               3507400
               3507400
               3507400
               3507400
    
    7 rows selected.
    
    SQL> select checkpoint_change# from V$datafile;
    
    CHECKPOINT_CHANGE#
    ------------------
               3507400
               3507400
               3507400
               3507400
               3507400
               3507400
               3507400
    
    7 rows selected.
    
    SQL> alter system checkpoint;
    
    System altered.
    
    SQL> select checkpoint_change# from V$datafile;
    
    CHECKPOINT_CHANGE#
    ------------------
               3507936
               3507936
               3507936
               3507936
               3507936
               3507936
               3507936
    
    7 rows selected.
    
    SQL>
    HTH
    Aman....
  • 8. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    705249 Newbie
    Currently Being Moderated
    thanks for your explanation.

    So, to summarize we can say:
    - a log swith induces a checkpoint but not a full check point
    - a checkpoint induces the longest dirty block in the buffer cache to be written to disk
    - a full check point induces all dirty blocks to be written to disk + the synchronization of the database

    please correct me if I'm not correct.
  • 9. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Aman.... Oracle ACE
    Currently Being Moderated
    a log swith induces a checkpoint but not a full check point
    Yes , a log switch induces a "log switch checkpoint" which is not a Full checkpoint. The change is there since version 8i I guess.
    a checkpoint induces the longest dirty block in the buffer cache to be written to disk
    A checkpoint triggers DBWR which eventually does it but yes, you may say that a checkpoint "makes" a dirty buffer being written to the disk.
    a full check point induces all dirty blocks to be written to disk + the synchronization of the database
    Yes.

    HTH
    Aman....
  • 10. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    705249 Newbie
    Currently Being Moderated
    thank you very much.
    i think it's clear for me now
  • 12. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    789379 Newbie
    Currently Being Moderated
    OCA Oracle Database 11g Administration I Exam Guide states like that, "There is
    no checkpoint following a log switch. This has been the case since release 8i,
    though to this day many DBAs do not realize this."
  • 13. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    776966 Newbie
    Currently Being Moderated
    hai ,

    Please check this metalink note 274264.1 which also suggest
    thumb rule on switching logs as once every fifteen minutes.
    This note also explained about redo log advisory feature available from 10g.


    Regards
    Meeran
  • 14. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user10175904 wrote:
    OCA Oracle Database 11g Administration I Exam Guide states like that, "There is
    no checkpoint following a log switch. This has been the case since release 8i,
    though to this day many DBAs do not realize this."
    Is there a document and page reference you can supply for that statement because someone will have to raise a documentation bug somewhere - either in the Guide or in the 11.2 Concepts guide where it says (paraphrase):

    <h2>When Oracle Initiates Checkpoints</h2>
    <h3>Thread Checkpoints</h3>
    Thread checkpoints occur in the following situations:
    * Online redo log switch

    See: http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/startup.htm#CNCPT89043

    Regards
    Jonathan Lewis
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points