Hi
I would like to know is there any formula or caluclation in sizing the redo log. apart from the general funda and talk.:)
Printable View
Hi
I would like to know is there any formula or caluclation in sizing the redo log. apart from the general funda and talk.:)
you might need to know first of what are the factors
or configuration parameters that greatly influence
the performance of your redo logs.
redo log buffers
LOG_CHECKPOINT_INTERVAL or
LOG_CHECKPOINT_TIMEOUT
multiplexing redo logs
Archiving redo logs
+
maybe hardware configuration factors(RAIDs,mirroring)
For me, the size of a redo logs will depends in how a
database is being utilized. Most of the db config setting
LOG_CHECKPOINT_TIMEOUT to 0 will allow the archiving of redo logs
at LOG SWITCHES/CHECKPOINT(if could remember it right). If you have a long processes in your db, and you have a huge size set up in your redo log files, it might take a lot
of time for SMON to recover your db during instance recovery if CHECKPOINT is set high. Again, it all depends in your database activities. You probably needs redo logs to be archived more often in away that it won't hurt additional IO cost.
IMHO, there could be no definite calculation of redo log size, but there is a calculation for LOG_CHECKPOINT_INTERVAL.
Hmm,
You need a formula ...
Y = x*x*x + x*x+x+1
I hope this helps you.
Tamil
This is what I do...
Start with 100M redologs
See how often they switch in peak periods of activity in database, adjust the size so that the log switch occurs every 30 min.
adjust the size so that the log switch occurs every 30 min.
___________
I am trying to understand why we need to make a switch for every 30 min. For your DB it may be best way. How can we generalize the time intervel. Any good reason ??
If you're running a standby server (not DataGuard) or just simply shipping the archlogs to another location, the interval between log switches determines how much work you lose in case of total destruction of your server.
So (e.g.) I force a log switch every 10 minutes during working hours - that's how much OLTP the user management decided they could afford to lose and re-enter.
No, that's just a rule of thumb that doesn't work for every (arguably any) environment. As long as your arch can keep up with your lgwr and you don't get "checkpoint not complete" messages, your logs are probably fine. My logs switch every couple minutes. I'd have to have a 20G log file if I wanted it to switch every 30 minutes and then it would pound the crap out of my log_archive_dest for a couple minutes.Quote:
Originally posted by raghud
I am trying to understand why we need to make a switch for every 30 min. For your DB it may be best way. How can we generalize the time intervel. Any good reason ??
If you're sending them over a network, this may contain more useful formulae:Quote:
Originally posted by tamilselvan
Hmm,
You need a formula ...
Y = x*x*x + x*x+x+1
http://cm.bell-labs.com/cm/ms/what/s...hannon1948.pdf
Given the circular nature of redo logs, I thought the formula wasQuote:
Originally posted by DaPi
If you're sending them over a network, this may contain more useful formulae:
http://cm.bell-labs.com/cm/ms/what/s...hannon1948.pdf
PI*R*R ;)
Seniors Guys be patience !
Good explanation from "marist89". Some times there may be basic questions from the users. Any way knowledgable DBAs do not need help, they can solve their problems. But these kind of sites (user groups )are very helpful for the Jr.DBA's .
(db_block_size * some_number * #_members ) / #_redo_logsQuote:
Originally posted by chandoos
Hi
I would like to know is there any formula or caluclation in sizing the redo log.
The amount of redo you generate depends on the total tranaction per minute and the average size of your tranactions. The other factors that go into sizing redo logs are how much data can you afford to lose if you lose your database and all of your online redor logs.Quote:
Originally posted by chandoos
Hi
I would like to know is there any formula or caluclation in sizing the redo log. apart from the general funda and talk.:)
If you size you redo logs to 100 megs each, have them duplexed, and get a log switch every 10 minutes. You have them sized correctly as long as you database performance is good, and your are not getting errors and or warnings when the log switch happens.
If you are getting errors like check point not complete, or if you are doing log switches too often or almost never you will need to readjust the size of your redo logs or add additional log groups.
There is no formula for calucalting the size of your redo logs!
I can't tell if you are being serious or if you are joking.Quote:
Originally posted by marist89
(db_block_size * some_number * #_members ) / #_redo_logs
better?Quote:
Originally posted by gandolf989
I can't tell if you are being serious or if you are joking.
Jeff Hunter ,, Serious ?? .He is Like "J W BUSH" President of Amer_Jamica.
Sizing redo logs doesn?t rely on a strict formula, but rather on workload analysis and log switch frequency.
A commonly accepted approach is to size redo logs so that log switches occur every 15?30 minutes during peak usage. This helps balance performance and recovery time.
You can estimate it like this:
Determine redo generation rate
Example: 6 GB per hour ≈ 100 MB per minute
Choose a target switch interval
Example: 20 minutes
Calculate redo log size
100 MB ? 20 = ~2 GB per redo log file
After that, monitor system behavior and adjust if needed.
⚙️ Best Practices:
Maintain 3?4 redo log groups
Keep sizes consistent
Avoid too frequent switches or excessively large logs
🔗 Additional Insight:
Interestingly, the same principle of using historical data and real-time patterns applies in other areas too. For example, tools like
snow day prediction tool
use past trends and current conditions to improve prediction accuracy?similar to how database tuning relies on workload patterns.