6 Replies Latest reply: Dec 6, 2010 1:56 PM by Jonathan Lewis RSS

    log_buffer size

    user11977218
      Hello ,

      How to determine the correct size of log_buffer ?
      According to oracle documentation the checkpoint is triggered when : log_buffer have filled 1/3 of it size or 1MB or every 3 seconds .
      Then the size of log_buffer=6120448 (5.8 MB) is correct or no ?


      Thank's
        • 1. Re: log_buffer size
          618702
          Dear user11977218,

          Determining the best log_buffer parameter is important since if you have set it too high or too low than there could be wait events on the commit type.

          That is subject to change and i believe that there should not be any calculation to determine the log_buffer. You have to check your redolog sizes and switch frequency per hour and also you need to check the Enterprise Manager to see if you have wait events because of too high or too low log_buffer.

          There are numerous documentations out there if can go and search the google.

          To wrap it up, if there is no need to tune the log_buffer than do not so. log_buffer can be changed because of the attitude of the database (increased updates or inserts etc.)

          Regards.

          Ogan
          • 2. Re: log_buffer size
            sb92075
            Then the size of log_buffer=6120448 (5.8 MB) is correct or no ?
            Under normal load conditions, how often does Oracle switch REDO log files for you site & application?

            Some/many site size redo log file such that the REDO switches occur about every 15 minutes or so.
            • 3. Re: log_buffer size
              725662
              Redo log switches are a factor of the size of the redo logs and number of groups AND the log_biuffer. The redo log buffer gets flushed when you commit, it's 1/3 full or every 3 seconds. Any space you allocate to the redo log buffer above and beyond what gets used (based on the above constraints) is wasted log buffer. Therefore, set the redo log buffer to an appropriate (but not excessive) size and then adjust the size of the redo log memober files and the number of log groups to attain a healthy cycle on the redo logs.

              To determine what healthy is, size the redo logs so that in addition to the current log, only one other group is ACTIVE. If your groups are too few, or the mmbers to small, you will end up with a situation where all your log files are current or active and you may get a

              'private strand flush incomplete'

              or

              'Checkpoint not complete'


              SELECT * FROM V$LOG;

              and you will see output similar (perhaps to this:
              <pre>
              niku> set lin 2000
              niku> /

              GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
              ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------------
              1 1 431 1073741824 1 NO INACTIVE 6040091582 03-DEC-10
              2 1 432 1073741824 1 NO INACTIVE 6041112547 04-DEC-10
              3 1 433 1073741824 1 NO INACTIVE 6041954837 05-DEC-10
              4 1 434 1073741824 1 NO CURRENT 6042824300 05-DEC-10
              5 1 430 1073741824 1 NO INACTIVE 6039159571 02-DEC-10

              </pre>

              In this database we have one CURRENT and no ACTIVE logs at this moment.


              George Milliken
              http://oracleobserver.com
              • 4. Re: log_buffer size
                790026
                Hi,

                You should check AWR reports.

                In case you'll see "log buffer space" waits then log_buffer is too small.

                If you'll see "log file sync" waits then log_buffer is too large.

                Check out this article - http://www.ixora.com.au/tips/tuning/log_buffer_size.htm

                Good luck!

                ----------------------
                http://dba-star.blogspot.com
                • 5. Re: log_buffer size
                  725662
                  Let's all remember that AWR reporets are only available if you are licensed for the Diagnostic and Tuning Packs.

                  Much of the workd is not.
                  • 6. Re: log_buffer size
                    Jonathan Lewis
                    user11977218 wrote:
                    Hello ,

                    How to determine the correct size of log_buffer ?
                    According to oracle documentation the checkpoint is triggered when : log_buffer have filled 1/3 of it size or 1MB or every 3 seconds .
                    Then the size of log_buffer=6120448 (5.8 MB) is correct or no ?
                    Which version of Oracle are you using ?
                    If 10g or later then you should not need to set the log_buffer at all, a suitable value will be determined by the instance at startup. The allocation will typically be a single memory granule (4MB or 16MB depending on total size of SGA - 8MB for some Windows platforms).

                    Most of the information about log_buffer sizing on the Internet is out of date - and even some of the Oracle manuals have only recently been corrected.

                    In extreme cases you may need to set the log_buffer to a larger value - typically this will be because you are running 10g with flashback database enabled and you see the RVWR process waiting for space in the flashback buffer.

                    Regards
                    Jonathan Lewis