Forum Stats

  • 3,728,255 Users
  • 2,245,586 Discussions
  • 7,853,418 Comments

Discussions

Recommended Redo Log size

2995489
2995489 Member Posts: 114

Hi,

i want to now what is the better size for production database.

Please can you help me .

Thank you .

Med.

Tagged:
Srini RamaswamyPini DibaskAndrewSayerRaj Kushawaha

Best Answer

  • Pini Dibask
    Pini Dibask Member Posts: 521 Gold Badge
    edited May 2016 Accepted Answer

    The best practice is to avoid having a high frequency of log switches because then you will encounter many "checkpoint not complete" wait events (appears in the alert log as "checkpoint not complete, cannot allocate new log", which will impact the database performance.

    The redo log size depends on the database workload. Usually you will need to make sure that during peak load (when there are many changes and many redo records are being generated) the frequency is not higher than once in 20 minutes, so start by monitoring the log switch frequency, and based on that determine whether the current size if sufficient or perhaps you need to make it larger.

    You can monitor the log switch frequency using Toad for Oracle (see for example: https://www.youtube.com/watch?v=uXFzOwGfAgE) or  if you don't have Toad you can query V$ARCHIVED_LOG to obtain the log swithc frequency.

    See also :

«1

Answers

  • Pini Dibask
    Pini Dibask Member Posts: 521 Gold Badge
    edited May 2016 Accepted Answer

    The best practice is to avoid having a high frequency of log switches because then you will encounter many "checkpoint not complete" wait events (appears in the alert log as "checkpoint not complete, cannot allocate new log", which will impact the database performance.

    The redo log size depends on the database workload. Usually you will need to make sure that during peak load (when there are many changes and many redo records are being generated) the frequency is not higher than once in 20 minutes, so start by monitoring the log switch frequency, and based on that determine whether the current size if sufficient or perhaps you need to make it larger.

    You can monitor the log switch frequency using Toad for Oracle (see for example: https://www.youtube.com/watch?v=uXFzOwGfAgE) or  if you don't have Toad you can query V$ARCHIVED_LOG to obtain the log swithc frequency.

    See also :

  • Srini Ramaswamy
    Srini Ramaswamy Member Posts: 68
    edited May 2016

    As Pini commented - Oracle recommends a log switch every 20 minutes. If you DB is tested for production load or its already in production use the below query to find out how many log switch happened every hour.

    SELECT
        SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)  "DAY(Month/Day)"
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
      , COUNT(*)                                                                      TOTAL
    FROM
      v$log_history  a
    GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
    ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) desc;
    
    2995489
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 2016
    2995489 wrote:
    
    Hi,
    
    i want to now what is the better size for production database.
    
    Please can you help me .
    
    
    Thank you .
    
    Med.
    

    What is it currently set to? Are you experiencing any problems which you can attribute to the size of your redo logs?

    Please point to documentation which claims that a certain frequency of switches is preferred.

  • 2995489
    2995489 Member Posts: 114
    edited May 2016

    Hi Pini Dibask,

    i thank you so much for your help, i find 6 switchs every 1 hour. I think i must increse the size of the redo log file.

    I will use toad to monitor switch, just don't have access to youtube at work.

    Thank you again Pini.

    Best Regards,

    Med.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 2016
    2995489 wrote:
    
    Hi Pini Dibask,
    
    i thank you so much for your help, i find 6 switchs every 1 hour. I think i must increse the size of the redo log file.
    I will use toad to monitor switch, just don't have access to youtube at work.
    
    Thank you again Pini.
    
    Best Regards,
    Med.
    

    Why must you?

    What problems are you actually experiencing?

  • Pini Dibask
    Pini Dibask Member Posts: 521 Gold Badge
    edited May 2016

    Hello,

    You don't have to use Toad, it is just a convenient way to look at this.

    You can also use the query the info in V$ARCHIVED_LOG as I mentioned in my previous comment (see the response of Srini with a query that you can use).

    BTW, if you have 6 log switches per hour during peak loads it is not that bad (1 log switch every 10 minutes), but personally I would recommend on having a log switch no more than once in 20 minutes.

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 2016
    The best practice is to avoid having a high frequency of log switches because then you will encounter many "checkpoint not complete" wait events (appears in the alert log as "checkpoint not complete, cannot allocate new log", which will impact the database performance.

    THis is not 100% true. You can have a high frequency of log switches without getting checkpoint not complete messages in the alert log as long as DBWR can keep up (so, a combination of DBWR being fast enough and having enough redo log groups irrespective of size).

    Raj Kushawaha
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 2016
    personally I would recommend on having a log switch no more than once in 20 minutes.

    Why? Can you explain why 20 minutes is better than 10 minutes or 19 minutes or any other number?

  • Pini Dibask
    Pini Dibask Member Posts: 521 Gold Badge
    edited May 2016
    John Stegeman wrote:
    
    personally I would recommend on having a log switch no more than once in 20 minutes.
    Why? Can you explain why 20 minutes is better than 10 minutes or 19 minutes or any other number?
    
    
    
    1. Best practices are not always 100% true, but rather general guidelines which in most cases are correct and can assist in configuring environments properly.
    2. See:
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 2016

    I know that Tom Kyte has said that in the past, but there are more than just "best practices" to consider, especially if you are using a standby database... Let's say, for example, that archive_lag_target is set to 600 - you will always get a log switch at least every 10 minutes, regardless of how large the redo log group members are.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 2016

    I've just queried our shop's main application production db, we try to switch logs at least every 5 minutes to keep a standby in sync.

    SELECT

        SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09

      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10

      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11

      , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12

    FROM

      v$log_history  a

    where first_time >trunc(sysdate)

            H09          H10          H11          H12

    ____________ ____________ ____________ ____________

              14          15          17            9

    SELECT event

          ,COUNT(*)

    FROM   v$active_session_history

    WHERE  sample_time between trunc(sysdate)+interval'11'hour and trunc(sysdate)+interval'12'hour

    and session_type= 'FOREGROUND'

    GROUP BY rollup ( event)

    order by 2

    /

    EVENT                                                                            COUNT(*)

    ________________________________________________________________ ________________________

    ..

    enq: TX - index contention                                                            691

    log file sync                                                                       3,458

    SQL*Net message from dblink                                                         4,615

                                                                                       40,674

    db file sequential read                                                            68,327

                                                                                      119,141

    27 rows selected.

    Looking at the wait events recorded in v$ash for foreground sessions in the period 11am to 12am, Less than 4% of the rows in v$ash are from 'log file sync' which could be due to a lot of transactions committing. No other wait events were recorded with event like '%redo%' for foreground sessions.

    I don't see there to be any need to reduce the frequency of the log switches, in fact we could probably make them even more frequent if we wanted. I fail to see the ground in the 20 minute frequency advice, instead I would say unless there are actual performance issues being caused by log switches (and the wait events will make this known) then the current value is probably absolutely fine.

  • EdStevens
    EdStevens Member Posts: 28,043 Gold Crown
    edited May 2016

    Yes, and it is just rule of thumb.  But if one has log switches more frequently during peak loads -- even  much more frequently -- but there is no operational problem that is attributable to the frequency of log switch, then what's the issue?  And that is exactly what we're trying to drag out of the OP --"what's the issue?"  So far it sounds like there is no issue at all and he has fallen into the trap of thinking that he needs to find some magical number that is somehow "correct". 

    Srini Ramaswamy
  • Srini Ramaswamy
    Srini Ramaswamy Member Posts: 68
    edited May 2016
    THis is not 100% true. You can have a high frequency of log switches without getting checkpoint not complete messages in the alert log as long as DBWR can keep up (so, a combination of DBWR being fast enough and having enough redo log groups irrespective of size).
    

    @John - I totally agree that its not a hard/fast rule - may be its just to make things simpler for most environments. A round off number which fits most environment. Like the speed limit is 55 miles (the reason is speed more than 55 miles and during crash the impact increase by 7x) - technically is it exactly 55 miles and not 54 or 56 miles? May be its the closest round off number.

    Smaller redo log would not have impact on performance?

    T.

    Srini

    Pini Dibask
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,583 Gold Crown
    edited May 2016

    Probably a good idea to read this note (and the dialogue that follows it) before you get too concerned:

    https://community.oracle.com/thread/3902806#13716850

    Regards

    Jonathan Lewis

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,583 Gold Crown
    edited May 2016

    Absolutely.

    And just to put the boot in on that annoying little script of "redo log switches per hour" that keeps resurfacing - who wants to guess when the most significant redo-related waits appeared in this 24 hour period:

    DAY        00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23

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

    06-MAY-16   4   3   4   5   3   2   0   1   3   4   1   5  12   8   2   1   7   3   0   1   5   5   2   4

    Regards

    Jonathan Lewis

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 2016
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,583 Gold Crown
    edited May 2016

    John,

    I honestly didn't see that one coming. I know I ask tricky questions from time to time, but not that tricky (at least, not this time).

    (And it's not 6 pm either)

    Regards

    Jonathan Lewis

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited May 2016

    2pm? I would expect the same number of switches as 1pm (ish!) , possibly there is a large number of redo log groups (up to 20 possibly) but we're now waiting on one to be archived so we can reuse the log group. This would have been fine before because of the amount of time between [number of groups] redo log switches

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,583 Gold Crown
    edited May 2016

    Andrew,

    I can appreciate the argument - which is clearly based on what I'd call "reasonable guesswork", backed by a rational justification - but it's not the right answer.

    Is anyone going to go for the extremely obvious guess - or is the trap too obvious ?

    Regards

    Jonathan Lewis

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 2016

    Well, Jonathan, I'll bite - the "obvious" answer is noon.

  • Srini Ramaswamy
    Srini Ramaswamy Member Posts: 68
    edited May 2016

    May be 1PM to 2PM.

    Starting backwards where 23 is assumed to be 11 PM to 12 AM.

    T.

    Srini

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,583 Gold Crown
    edited May 2016

    Wrong !

    But thanks for co-operating.

    It's a reasonable guess, of course - clearly the system must have seen a higher average rate of change over that hour (and the following one) - but that doesn't mean the system was under stress at any particular time.  The only thing this report can tell you is that the pattern isn't the same as usual - and when the pattern hasn't changed you might have a problem, and when the pattern has changed you might not have a problem.

    The point at which the most significant redo waits appeared was from 20:58 to 21:02 when there were 10 log file switches in just over 3 minutes. As Cary Millsap puts it: you can't derive detail from a summary.

    Regards

    Jonathan Lewis

    Srini Ramaswamy
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 2016

    I knew I would be wrong, Jonathan - I was wincing awaiting the inevitable rebuke

  • 2995489
    2995489 Member Posts: 114
    edited May 2016

    Hi,

    yes i used the query ( V$ARCHIVED_LOG ). I find mininum 6 switschs/hour all the day.

  • 2995489
    2995489 Member Posts: 114
    edited May 2016

    Hi,

    i have said i must because i noticed locks sometimes in the DB. and when i have checked (diagnostic) i have found that requests wait the LGWR process.

    i have understand that it wait the switch to can write in the next redo log file..

    Med.

This discussion has been closed.