- 3,728,255 Users
- 2,245,586 Discussions
- 7,853,418 Comments
Forum Stats
Discussions
Howdy, Stranger!
Categories
- 380.6K All Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 2.4K Databases
- 699 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 32 Multilingual Engine
- 499 MySQL Community Space
- 10 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 426 SQLcl
- 69 SQL Developer Data Modeler
- 185.2K SQL & PL/SQL
- 21.1K SQL Developer
- 2.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 136K Development Tools
- 18 DevOps
- 3K QA/Testing
- 390 Java
- 16 Java Learning Subscription
- 14 Database Connectivity
- 72 Java Community Process
- 3 Java 25
- 14 Java APIs
- 141.2K Java Development Tools
- 15 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 18 Java SE
- 13.8K Java Security
- 4 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 159 LiveLabs
- 36 Workshops
- 13 Software
- 7 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 19 Español
- 1.9K Japanese
- 4 Portuguese
Recommended Redo Log size
Hi,
i want to now what is the better size for production database.
Please can you help me .
Thank you .
Med.
Best 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 :
Answers
-
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 :
-
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 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.
-
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.
-
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?
-
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.
-
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).
-
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?
-
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?
- Best practices are not always 100% true, but rather general guidelines which in most cases are correct and can assist in configuring environments properly.
- See:
- https://books.google.co.il/books?id=xxx0KAwY_ZMC&pg=PA331&lpg=PA331&dq=Oracle+suggests+sizing+redo+log+files+such+that+they+switch+about+every+20+minutes&source=bl&ots=4FolANqU5s&sig=6DQhywMyM2p4tSvgsNZ0_2Aj0Hw&hl=en&sa=X&ved=0ahUKEwi1z63I9MzMAhXLOxQKHYOkB6IQ6AEIGjAA#v=onepage&q=Oracle%20suggests%20sizing%20redo%20log%20files%20such%20that%20they%20switch%20about%20every%2020%20minutes&f=false "Oracle suggests sizing redo log files such that they switch about every 20 minutes".
- http://logicalread.solarwinds.com/oracle-log-file-switch-completion-wait-event-2/#.VzB3z_l96Uk "Often the performance impact of switching too many redo logs is the culprit. A good rule of thumb is to switch a redo log every 20 to 30 minutes. Listing 1 gives a SQL statement that will show how often log file switching is occurring every hour. You need only increase your redo log sizes to hold more redo and this will reduce the amount of log file switching."
-
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.
-
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.
-
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".
-
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
-
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
-
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
-
6am?
-
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
-
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
-
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
-
Well, Jonathan, I'll bite - the "obvious" answer is noon.
-
May be 1PM to 2PM.
Starting backwards where 23 is assumed to be 11 PM to 12 AM.
T.
Srini
-
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
-
I knew I would be wrong, Jonathan - I was wincing awaiting the inevitable rebuke
-
Hi,
yes i used the query ( V$ARCHIVED_LOG ). I find mininum 6 switschs/hour all the day.
-
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.