13 Replies Latest reply on Feb 23, 2010 3:20 PM by Radek

    Redo logs size


      I'm dealing with 15TB db. I'm wondering what are the optimal redo log files size? For now I have them setup to 1.5GB.
      Sometimes there are over 20 log switches within an hour.This is pretty busy db.
      I'm worrying that this is having performance. This is production environment.
      Should I leave them as they are or increase them to 2.5GB ?

      We don't want to set MTTR for the db, as startup time is not very important for us.

      What would you suggest?
        • 1. Re: Redo logs size
          Rafi (Oracle DBA)
          Hi ,

          Every DBA knows that the size of their redo logs is very important. Too small, and the frequent log switches tie-up the LGWR, ARCH and DBWR background processes. To large, and you risk loosing data during an instance crash.

          Oracle 10g has introduced a new advisory utility that allows you to specify your optimal mean time to recovery (MTTR) recovery interval and uses this to suggest the optimal redo log size. In Oracle 10g the fast_start_mttr_target parameter is used.

          Oracle recommends using the fast_start_mttr_target initialization parameter to control the duration of startup after instance failure. With 10g, the Oracle database can now self-tune check-pointing to achieve good recovery times with low impact on normal throughput. You no longer have to set any checkpoint-related parameters.

          This method reduces the time required for cache recovery and makes the recovery bounded and predictable by limiting the number of dirty buffers and the number of redo records generated between the most recent redo record and the last checkpoint. Administrators specify a target (bounded) time to complete the cache recovery phase of recovery with the fast_start_mttr_target initialization parameter, and Oracle automatically varies the incremental checkpoint writes to meet that target.

          The target_mttr field of v$instance_recovery contains the MTTR target in effect. The estimated_mttr field of v$instance_recovery contains the estimated MTTR should a crash happen right away.

          For example,



          ----------- -------------- -----------------

          37 22 209187

          Whenever you set fast_start_mttr_target to a nonzero value, and while MTTR advisory is ON, Oracle Corporation recommends that you disable (set to 0) the following parameters:




          Querying the advisor

          In addition to the MTTR information in v$instance_recovery we also have an important column called optimal_logfile_size, and we can query for this value at any time. The value for optimal_logfile_size is expressed in megabytes and it changes frequently, based on the DML load on your database. For example,


          If your database is relatively stable, then you can use this suggested size and rebuild your online redo log files to match the value. We would expect that a future version of Oracle will automate this and allow for dynamic re-sizing of online redo log files, but this is an issues because many Oracle systems expect the archived redo log files to always be the same size.

          • 2. Re: Redo logs size

            redo sizing is corresponding with the redo bytes generated ..

            i'nk the below link might be useful.


            • 3. Re: Redo logs size
              Rafi, Is there really need to copy and paste internet article?
              I can use google

              I don't need theory, thanks. If you read my post you would see we don't need to setup MTTR, therefore redo log advisor cannot work.

              I am asking if you think that having over 20 log changes could create serious performance issues and if so should files be increased?

              Edited by: user8951250 on 19-Feb-2010 03:05
              • 4. Re: Redo logs size
                Rafi (Oracle DBA)
                Hi ,
                I wanted you to give explanation in details.So I copied from there.You can make the redo log file size less and increase the numbers.The performance will be good.
                Here is what we are adopting for our stage database.kindly look below.


                SQL> select
                2 3 4 5 6 7 8
                ---------- ---------- ---------- --- ----------------
                11 2 524288000 NO INACTIVE
                12 2 524288000 NO INACTIVE
                13 2 524288000 NO CURRENT
                14 2 524288000 NO INACTIVE
                15 2 524288000 NO INACTIVE
                16 2 524288000 NO INACTIVE
                17 2 524288000 NO INACTIVE
                21 2 524288000 NO CURRENT
                22 2 524288000 NO INACTIVE
                23 2 524288000 NO INACTIVE
                24 2 524288000 NO INACTIVE

                ---------- ---------- ---------- --- ----------------
                25 2 524288000 NO INACTIVE
                26 2 524288000 NO INACTIVE
                27 2 524288000 NO INACTIVE
                31 2 524288000 NO INACTIVE
                32 2 524288000 NO INACTIVE
                33 2 524288000 NO ACTIVE
                34 2 524288000 NO CURRENT
                35 2 524288000 YES UNUSED
                36 2 524288000 YES UNUSED
                37 2 524288000 YES UNUSED
                41 2 524288000 NO INACTIVE

                ---------- ---------- ---------- --- ----------------
                42 2 524288000 NO INACTIVE
                43 2 524288000 NO INACTIVE
                44 2 524288000 NO INACTIVE
                45 2 524288000 NO INACTIVE
                46 2 524288000 NO INACTIVE
                47 2 524288000 NO CURRENT

                28 rows selected.

                • 5. Re: Redo logs size
                  Rafi (Oracle DBA)
                  Our stage Database size is around 370 mb.So we follow the above technique.
                  SQL> select sum(bytes)/1024/1024/1024 from dba_data_files;


                  SQL> select *from v$version;

                  Oracle Database 10g Enterprise Edition Release - 64bi
                  PL/SQL Release - Production
                  CORE Production
                  TNS for Linux: Version - Production
                  NLSRTL Version - Production
                  • 6. Re: Redo logs size
                    Rafi (Oracle DBA)
                    Sorry it was a typo from me The size is around 370 GB.And each redo log file size is around 524 mb as you can see above.

                    • 7. Re: Redo logs size
                      Thanks a lot Rafi for all your replies and effort.

                      I'm afraid we don't understand each other.

                      I know how to setup log files size and how to query v$INSTANCE_RECOVERY view [which in fact relies on MTTR - not relevant here].
                      This is not the point.

                      Let me rephrase my question then: Has anyone dealt with such big db? If so, what are your log files size?

                      Please do not compare 370GB to 16TB. There is too big gap between them.

                      Edited by: Radek on 23-Feb-2010 02:16
                      • 8. Re: Redo logs size
                        Read the link.

                        what should be the redlog size in OLTP environment like banks?(10g)

                        Hope it will help.

                        Asif Kabir

                        -- mark your answer as correct/helpful
                        • 9. Re: Redo logs size
                          Anurag Tibrewal
                          Yes Radek,

                          There would be many people out here who would have definately worked with 15TB+ databases.
                          I have myself worked with many such db, you just want to know about some experiences then here it is, I have one my DB with is 20TB+ and my redo log size is 1GB. I have 8 groups with 2 member each. Log switch occurs every 2 minutes. This is controlled by "archive_lag_target" as we would like to propogate the db changes through Golden Gate (That is something our project personal requirement). The total archive generation is nearly 25G with 30-35 files during normal peak time.
                          With above frequent switches still we do not face any problem with DB.

                          I have also many 20TB+ db with 1G redo log and switches occuring 5-20 times on an average.

                          I did not said anything what you should do to check if you are facing any issue with current size just because I felt you are not interested to know about that.

                          • 10. Re: Redo logs size
                            Thank you both.

                            Anurag, I'm sure much people here deal with 15TB+ dbs. Not necessarily any of them would have any interest in this thread ;)

                            So, you have log switches every 2 minutes... 30 per hour. Similar to mine. Oracle recommends 4-5/h the most.

                            How did you measure the performance. I mean, does it matter how long queries run on the server? Do you have SLA or such? Do you measure execution time?

                            I'm not saying that by having 1.5GB setup for each redo log would affect the performance. We are just trying to address any possible bottleneck

                            Edited by: Radek on Feb 23, 2010 2:48 AM
                            • 11. Re: Redo logs size
                              Anurag Tibrewal

                              Yes we have strict sla's. We have sla of 5sec for all the query(transaction) that is generated by web applications.
                              We have sla's even on the batch jobs but offcourse not for 5 sec.

                              How I measure the performance issues (I would be talking about related to log size only).
                              By checking whether I do not have checkpoint not completed message in my alert log too frequent.
                              By checking awr report for top events not pertaining to log file sync or similar event.

                              • 12. Re: Redo logs size
                                Jonathan Lewis
                                Radek wrote:

                                I'm dealing with 15TB db. I'm wondering what are the optimal redo log files size? For now I have them setup to 1.5GB.
                                Sometimes there are over 20 log switches within an hour.This is pretty busy db.
                                I'm worrying that this is having performance. This is production environment.
                                Should I leave them as they are or increase them to 2.5GB ?

                                You've already had a few comments about the effects of sizing the redo - viz, possible data loss vs. increased I/O.

                                Oracle keeps changing details of the implementation, so although there is a commonly "nice feeling" suggestion of abour 4 per hour, this isn't derived scientifically.
                                The tradiational problem with log file switches was the high priority that was given to flooding all dirty blocks to disc as soon as the log switch occurred - but the continuous checkpointing, and changes in prioritisation have made that much less of a threat.

                                The only consideration really for extreme cases is: if I change the size of the log files, what will the change in volume of DBWR writes will I see, and does it matter. I I think one way to monitor the potential impact of a different log file size (in your specific circumstances) is to take snapshots of v$instance_recovery, in particular the column: write_logfile_size. In theory this is the number of data blocks written because of the size you have picked for your log files (technically the size of the smallest of your log files).

                                One of the measures that Oracle uses for the continuous checkpoint is that it tries to avoid leaving dirty blocks in memory if their first change was recorded more than "90% of a logfile" in the past. So the smaller your log file, the sooner Oracle will write dirty blocks - which means a small log file may make you write the same block many times if it is subject to many changes over a relatively short period of time - but a larger log file will leave it unwritten for longer, and therefore have to write it fewer times.

                                This suggests that: if writes_logfile_size is a large fraction of the total number of blocks written from cache, and you are worried about the total write I/O load, and the nature of your application is to update a relatively small number of blocks repeatedly, then a large log file size may reduce your I/O load and help performance.

                                Jonathan Lewis

                                To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                                {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                                fixed format
                                There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
                                +"I believe in evidence. I believe in observation, measurement, and reasoning, confirmed by independent observers. I'll believe anything, no matter how wild and ridiculous, if there is evidence for it. The wilder and more ridiculous something is, however, the firmer and more solid the evidence will have to be."+
                                Isaac Asimov                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                • 13. Re: Redo logs size
                                  Thank you, Jonathan. Very informative description.

                                  Is 1645633 writes much or little for WRITES_LOGFILE_SIZE ?