Thanks for the question, John.
Asked: April 15, 2014 - 8:19 pm UTC
Last updated: April 15, 2014 - 9:20 pm UTC
Version: 11.2.0
Viewed 1000+ times
You Asked
Hello Tom,
The 15-30 minute redo log switch time interval is common knowledge. However, I would like to know if this rule of thumb applies for tuning redo log switching on a database that is set to "no archivelog" mode?
To get a better understanding please also answer the following:
Hypothetically speaking, on an 11g database that doesn't have "FAST_START_MTTR_TARGET" explicitly set (According to the Oracle documentation, if we don't explicitly set it, then "Automatic Checkpoint Tuning" is on.). Also, in this database, it switches its redo log on average every 2 minutes during peak times, what would be the recommended log switch interval? If 2 minutes is too fast, roughly how much fast can we make the system by tuning it?
Would this switching time drive the "checkpointing" or would the "Automatic Checkpoint Tuning" cause faster checkpointing?
Also, on the other hand, is there a big problem if we size it to be a lot bigger than necessary i.e. 10gB and now the redo logs switch once a day? Will this cause a significant hit when it performs the checkpoint after the log switches? Or will it now be significant since "Automatic Checkpoint Tuning" will be in control of checkpointing?
Thank you, Tom.
and Tom said...
15-30 minutes is just a guideline. It is the most basic way to limit your recovery time - and a very basic way to reduce your exposure to data loss (if you let your redo logs switch every 5 hours - you might lose a lot of transactions if your entire machine blows up and you don't have any remote mirrors of the redo available).
In short, a redo log cannot be reused until the database blocks it protects are written to disk. That is the key point to remember.
It is mostly the aggregate amount of redo log you have configured that matters more than the size of the redo log files. If you are switching logs and you try to switch into a log that is still needed to protect blocks in the buffer cache - bam - you stop, dead in the water - "checkpoint not complete, cannot allocate new log".
One way to "fix" that issue is to create more logs - having enough online redo log to keep you going through that peak period of redo generation so that you have enough 'spare' redo log files online to avoid trying to overwrite that first one until its checkpoint completes.
Another way to "fix" that issue is to create new logs that are N times the size of the original. That way you will once again have enough online redo log to keep you going through that peak period of .... (same as above)
Reducing the number of switches, increasing the time between switches - will not really make you "faster" in general.
If you increase the time between switches by increasing the log size, and leave everything else as it is, you will likely increase your time to recover/shutdown (the time it takes for instance crash recovery to happen, or the time it takes to shutdown normal). That'll be because there will likely be more "dirty" blocks in the buffer cache. You might perform nominally faster - if you are currently really really waiting on IO to your datafiles (you'll write to them less frequently).
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment