Not a specific reply (sorry)
Sean Coughlin, May 08, 2002 - 7:38 am UTC
I have read these documents, the Oracle documentation etc. These are not answering my question. Why, when I have 200m redo logs, Log_checkpoint_interval set at 10000 blocks, an OS block size of 512 and log_checkpoint_timeout NOT set, Log_checkpoints_to_alerts set to TRUE don't I see any increamental checkpoints taking place in my alert log? I only see checkpoints occuring at log switches, which is a good thing, don't get me wrong, but according to the doco and all the articles, I should be seeing incremental checkpoint messages in my alert log every 5m worth of changes..
May 08, 2002 - 8:44 am UTC
Because INCREMENTAL checkpoints are not logged to the alert -- plain and simple. Period (sorry, I thought I said that in my response to you)
Imagine if they were. Once the incrementals start (after getting 5m of redo generated in your case -- from your question) the incrementals are almost CONTINOUSLY GOING. Your alert would exceed the size of your database in no time flat.
If you see:
......
Starting with Oracle 8.1, log_checkpoint_interval will be
interpreted to mean that the
incremental checkpoint should not lag the tail of the log by more than
log_checkpoint_interval number of redo blocks. This ensures that no more than
log_checkpoint_interval number of redo blocks will have to be read during
recovery. It helps in bounded recovery times.
...........
that means once it gets going, if you are generating redo, it'll keep on trickling the data out.
Mike, August 15, 2002 - 11:34 pm UTC
Someone cliams you enable to disable the parameters by setting to 0, thus the checkpoints only occurs as the log switchs. Is that ture? if so, what's the disadvantages of having huge online redo logs,which implies the infrequently checkpoints?
Another question in the mind:
look the parameter:
"
LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the
number of redo log file blocks that are written between consecutive checkpoints.
"
Because the checkpoint is the event as all the dirty db buffers are written to the datafiles by
the db writers, why it has to do with "the number of redo log file blocks " in the above definition?
August 16, 2002 - 7:30 am UTC
These days, the database is virtually constantly in a checkpoint like mode, trickleing blocks out in the background (you know, checkpoints are not evil, they aren't something you really want to turn off -- especially if you would like recovery to take less then a really long time).
There are many types of checkpoints, yes, you can make it so the official checkpoint happens at log switch by playing with these parameters....
abut checkpoint from ILT guide and cos
A reader, September 07, 2002 - 2:55 pm UTC
Hello Tom
From Oracle 8i backup & recovery instructor lead guide it states that checkpoint is affected by several things, one of them is this:
==========================================================
90% of the size of the smallest log file: A redo log file cannot be reused until the
checkpoint has advanced beyond the end of the log file. To minimize the chance
that update activity in the instance waits for a checkpoint to complete, Oracle8i
makes sure that the target for checkpoint position does not lag the end of the redo
log by more than 90% of the size of the smallest redo log.
The smaller the size of the smallest log, the more aggressively the Oracle server
writes dirty buffers to disk to ensure the position of the checkpoint has advanced to
the current log before that log completely fills.
========================================================
from Oracle 8i documents Tuning instance Recovery Performance it states
========================================================
Using LOG_CHECKPOINT_INTERVAL
Set the initialization parameter LOG_CHECKPOINT_INTERVAL to a value n (where n is an integer) to require that the checkpoint position never follow the most recent redo block by more than n blocks. In other words, at most n redo blocks can exist between the checkpoint position and the last block written to the redo log. In effect, you are limiting the amount of redo blocks that can exist between the checkpoint and the end of the log.
Oracle limits the maximum value of LOG_CHECKPOINT_INTERVAL to 90% of the smallest log to ensure that the checkpoint advances into the current log before that log fills and a log switch is attempted.
LOG_CHECKPOINT_INTERVAL is specified in redo blocks. Redo blocks are the same size as operating system blocks. Use the LOG_FILE_SIZE_REDO_BLKS column in V$INSTANCE_RECOVERY to see the number of redo blocks corresponding to 90% of the size of the smallest log file.
=========================================================
Since English is not my native language these statements look rather confusing to me, are the two statements talking about the samething?
I dont understand what do they mean by saying
"checkpoint has advanced beyond the end of the log file" and "checkpoint position"
How does a checkpoint advance? I thought checkpoint was simply a mechanism to syncronize file headers?
September 07, 2002 - 7:02 pm UTC
Answering backwards.
A checkpoints primary job is to flush dirty blocks from the buffer cache to the database files. Updating file headers -- thats somewhat secondary. (important but not the crux)
Yes, they basically say the same thing.
A checkpoint advances by making sure that the blocks protected by some area of REDO log are safely flushed to disk. If a redo log file protects the cached versions of blocks 1 thru 100 in file 5 (eg: if the system crashed right now, we would need that redo in order to reconstruct the changes to blocks 1..100 since they are cached and not on disk) -- we can advance the checkpoint by writing those blocks out. Now, we no longer need that REDO log file for recovery and we can reuse it.
90% of smallest log
A reader, September 07, 2002 - 7:49 pm UTC
quote:"Oracle limits the maximum value of LOG_CHECKPOINT_INTERVAL to 90% of the smallest log"
does that means that if I have 3 redo logs of 5MB, 10MB and 15MB even I set LOG_CHECKPOINT_INTERVAL to a very large value such as 100000 (what we used to do in oracle 7 and 8, setting this high to ensure checkpoint occur only in log switches) Oracle will limit it to 90% of 5MB which is 9216?
September 08, 2002 - 9:24 am UTC
exactly, that is exactly what it is saying. You should know that checkpoints are almost continual in most systems today -- with fast_start_mttr_targets and other mechanisms to bound recovery times.
(checkpoints are *not* evil, they are something you want to have happening on a regular basis to avoid spiking and unduly long recovery times in the event of a failure)
from your first reply
A reader, September 09, 2002 - 7:29 am UTC
hi
you stated this in first reply
"So, for example, assume log_checkpoint interval is set to 1000. Oracle
continually calculates the address of redo record that was written 1000 records"
Should 1000 record be 1000 redo blocks? According to Oracle 8.1.7 Reference guide, v$instance_recovery is based on blocks
For example LOG_FILE_SIZE_REDO_BLKS in v$instance recovery definition is: Maximum number of redo blocks required to guarantee that a log switch does not occur before the checkpoint completes.
September 09, 2002 - 8:34 am UTC
I said:
was written 1000 records (OS blocks) ago
the clarification is in the answer....
regarding LOG_CHECKPOINT_TIMEOUT
A reader, September 09, 2002 - 9:57 am UTC
Hi
From new definition of LOG_CHECKPOINT_TIMEOUT isnt it quite hard to estimate how aggresive checkpoint would occur? I mean 60 seconds worth of redo is relative, we can generate lots of redo in 60 seconds or not as much as we might think
Am I right about LOG_CHECKPOINT_TIMEOUT new defintion?
September 09, 2002 - 10:09 am UTC
It is pretty much happening all of the time (and you want it to). You cannot really predict when it will happen until you see what you are doing on your system (load wise)
Differentation
Vikas Khanna, September 10, 2002 - 8:55 am UTC
Hello Tom,
Need to understand Log_Checkpoint_timeout = 1800, Log_Checkpoint_interval = 10000 and Fast_start_io_target = 500. How would LGWR flush redo blocks from log buffer to Online redo logs based on these parameter values.
Do any of these gets more priority over the other ones.?
Please explain in greater insights to understand it better?
September 10, 2002 - 9:07 am UTC
LGWR flushes blocks to the redo logs not based on ANY OF THOSE.
every 3 seconds it'll flush the redo buffers
when 1/3 full it'll flush the redo buffers
when 1meg full it'll flush the redo buffers
when someone commits - it'll flush the redo buffers
My Mistake
Vikas Khanna, September 11, 2002 - 12:25 am UTC
I needed to ask regarding Checkpoint, and when does DBWR flushes the dirty buffers from the dirty list to the respective datafiles. Is this some parameter specific?
How fast these checkpoints gets initiated? In general please take all specific cases to illustrate these parameters in combination with others?
Thanks in advance.
September 11, 2002 - 7:42 am UTC
Almost continously these days. Almost continously. Too many parameters come to mind -- fast_start_mttr_target (io_target in later releases) log checkpoint interval, log checkpoint time out, log switches themselves, various SQL commands (eg: a parallel query can do it), many DDL commands, etc etc etc.
Since DBWR is going in the background, you want this to happen. checkpoints are not evil, they are not something to totally defer. Over time your ability to control when they happen is going away (self managing).
Vikas Khanna, September 13, 2002 - 6:21 am UTC
In earlier releases of Oracle there used to be a parameter to be set named checkpoint_process = TRUE/FALSE depending upon whether to initiate a checkpoint or not, but with new releases it is going away from the hands of DBA. There are numerous parametets which do often checkpoint, and feeling is that much of checkpointing is calling DBWR to be called very frequently and the disk I/O is initiated at regular intervals. This is just to maintain less MTTR as there would be very less dirty blocks in the Dirty list for DBWR to write.
But isn't it too much on the system overhead.? There would be a time when at every commit it would be calling DBWR to write the dirty buffers to datafiles.
Moreover, if I set LOG_CHECKPOINT_INTERVAL = 8,LOG_CHECK_TIMEOUT= 5 and FAST_START_IO_TARGET = 10 then suppose we get a checkpoint when the dirty blocks were exceeding 10 and at the same point we have 5 log blocks, and the time elapsed is 3 sec, would at that point it would reinitialize the parameters to start from fresh or it would continue with th old set values. If it is not resetting the old values for these parameters then LOG_CHECKPOINT_INTERVAL just need 2 more redo blocks before CKPT is initiated again.
Please do verify and give your valuable comments on this.
Thanks
September 14, 2002 - 1:54 pm UTC
No, that is not what checkpoint process did. That parameter started the optional CKPT process which is responsible for updating the data file headers -- it did not turn checkpoints on and off. Never did.
If you define new releases as "all software made in the last 5 years", you would be correct.
A commit won't be calling DBWR to write blocks out. Remember -- in a well running system -- DBWR just chugs in the background, you do NOT wait on him. It is only in a poorly tuned/overloaded system that you do (and it is NOT during a commit, it is when you need a free buffer in the buffer cache and we have to signal DBWR to make some for us -- that would be a side effect of NOT checkpointing frequently enough!!!! so it is actually NOT checkpointing that would cause you to wait on DBWR, not checkpointing -- you have it backwards)
A reader, December 31, 2002 - 11:41 am UTC
Further reading
Oren, April 27, 2003 - 9:33 am UTC
Hi Tom.
Can you please recommend on a good and thorough document, explaining in detail the various types of checkpoints?
Thanks,
Oren.
April 27, 2003 - 4:30 pm UTC
metalink may have some. the only time i really care about a checkpoint -- is when they stop my database -- and then I fix it and move on.
Checkpoints happen almost constantly and continously these days -- they are not much of a "point" anymore, more of an ongoing thing.
Answers to Checkpoint process
Nikhil, April 28, 2003 - 12:19 am UTC
My search ends here while looking for docs/white papers on checkpoint and related params...
Definitely a good page worth to read 10 times ...
dbwr
Reader, January 17, 2004 - 12:09 pm UTC
Excellent thread and great answers from Tom.
I was asked by a person the following. I could not give a convincing answer. Could you help? Thanks.
When dbwr writes dirty buffers every three seconds, the timeout of 3 seconds itself should advance the checkpoint position. Why do we need to set other parameters like log_checkpoint_interval and such. Please clarify. Thanks.
January 18, 2004 - 12:52 pm UTC
i would say in most all cases you do not need to set such parameters -- it mostly takes care of itself these days. things like fast_start_mttr_target are "better".
ckpt writes control files every three seconds, lgwr flushes redo... but dbwr isn't doing that to datafiles
just a small clarification about incremental checkpoint
H.S.Anand, January 29, 2004 - 11:58 pm UTC
I understand the difference between log_checkpoint_interval and log_checkpoint_timeout and that
Oracle will internally calculate an optimal checkpoint positions if I set both! Which means i need to use only one of the two!
Just one niggling doubt.
If i set neither of the two, would it mean that effectively, Incremental checkpoints will never happen?
Please clear this one doubt!!
REgards
January 30, 2004 - 8:10 am UTC
see fast_start_mttr_target....
it is almost always happening these days.
FAST_START_MTTR_TARGET effectiveness
Bob, November 26, 2004 - 10:07 am UTC
Hi Tom
I have read the following
"You must disable the initialization parameters FAST_START_IO_TARGET, LOG_CHECKPOINT_INTERVAL,and LOG_CHECKPOINT_TIMEOUT parameters when using FAST_START_MTTR_TARGET. Setting these parameters to active values obstructs the normal functioning of FAST_START_MTTR_TARGET, thereby resulting in unpredictable results."
Is this true??
Does it mean i should set the following if i wish to use FAST_START_MTTR_TARGET correctly:
FAST_START_IO_TARGET = 0 (default = All the buffers in the cache)
LOG_CHECKPOINT_INTERVAL = 0 (default = 0)
LOG_CHECKPOINT_TIMEOUT = 0 (default = 1800)
November 26, 2004 - 10:26 am UTC
you would just NOT SET them (fast_start_io_target, log_checkpoint_interval)
fast_start_io_target in 9ir2 defaults to a zero value.
as does log_checkpoint_interval.
FAST_START_MTTR_TARGET effectiveness
Bob, November 26, 2004 - 12:13 pm UTC
So leaving LOG_CHECKPOINT_TIMEOUT = 1800 won't interfer with
FAST_START_MTTR_TARGET as well
November 26, 2004 - 1:07 pm UTC
I see they clarified this in the 10g doc set, which says:
Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set.
so, yes, that should be set to zero
block dirty forever
Alberto Dell'Era, November 27, 2004 - 11:36 am UTC
Is it possible for a block to remain dirty in the buffer cache "forever", without being written to disk ?
Eg assume (obviously an extreme case just to illustrate) that log switch checkpoints occur every 10 years, log_checkpoint_timeout, log_checkpoint_interval, fast_start_mttr_target are "disabled", and the block is modified every second, so it stays at the hot end of the LRU "list" and is not collected by DBWR ...
Thanks (and sorry for my bad english today)
November 27, 2004 - 11:45 am UTC
But 10 years is not "forever"
So the answer is no, eventually something will trigger the flushing of the block to disk.
eventually something will trigger a flush.
Alberto Dell'Era, November 27, 2004 - 12:53 pm UTC
> But 10 years is not "forever"
Ok :) let me rephrase, if i continuosly modify a block so that it stays at the hot end of the LRU, will i decrease the probability that it gets written to disk (in other words, will it gets written to disk less often statistically), since i keep it more away from the DBWR mouth, that eats at the cold end afaik ?
Consider a row that is continuosly updated; i wanted to know whether it *may* be able to escape the DBWR mouth indefinetely, assuming of course that the amount of redo log generated by its modifications doesn't trigger a log switch, and that log_switch_timeout and fast_start_mttr_target are disabled or set to very high values. If that is the case, we would observe *less*, not *more*, disk activity on the datafile that contains the block as the amount of modifications/sec increase.
November 27, 2004 - 1:09 pm UTC
hot blocks will be written less frequently yes. they'll be less of a candidate for flushing as dbwr is asked to make space.
however, in a system with say a small buffer cache -- dbwr may be forced to write the block out anyway to make room for read consistent copies of other blocks, or just to get other blocks in from disk and cache them.
indefinitely -- no, cause you checkpoint from time to time (not indefinitely) and because dbwr will flush blocks for reasons other than checkpoints as needed.
Alberto Dell'Era, November 27, 2004 - 1:18 pm UTC
thanks, perfect answer :)
A reader, December 15, 2004 - 3:07 am UTC
checkpoint intern(v)als
Arunkumar, February 20, 2005 - 7:14 am UTC
Excellent read. Clarifies many internals regarding checkpoints
"1/3 full" query
Sandeep, August 11, 2005 - 6:42 am UTC
Hi Tom,
I read this often but didn't pay enough attention, but now....
regarding one of the situations when the LGWR writes from the redo buffers to the online redo log -
"when 1/3 full it'll flush the redo buffers"..
My query is - does this mean that 2/3 of the redo buffer is actually never utilized? Sorry! if I've completely lost the plot?!
Thanks,
Sandeep
it is not completely clearly about log_checkpoint_interval 8 and 8i
Andr., March 28, 2006 - 5:45 am UTC
Hi Tom
Ñould you be so kind as to explain once more about the difference
log_checkpoint_interval in 8 and 8i ?
Comment,please.
From your explanation avove I understood that
the only difference between 8 and 8i
(and no more differences) is:
a) In 8 incremental checkpoints don't take into consideration
the log switch checkpoint.
For a example (from log1 to log3):
log1 log2 log3
------------------------------- >
| | | |
* * * * *
Here: | - log file switch, * - incremental checkpoints (there are
equal log_checkpoint interval redo blocks between * )
b) In 8i it take into consideration the log switch checkpoint(and others
checkpoints)
For the example (with the same check_point_interval):
log1 log2 log3
------------------------------- >
| | | |
* * *
Or I'm not right ?
PS Excuse me for my bad English.
sizing of log_buffer and related parameter
Fayyaz ZAHEER, July 10, 2006 - 4:50 am UTC
I have following init paramters in my db
log_buffer=6291456
log_checkpoint_interval = 300000
log_checkpoint_timeout = 1800
At one busy day, i have following statistics
=======================================
Latches
=======================================
Latch Name Gets Misses Sleeps Immediate Gets Immediate Misses
------------------------------ ------------ ------------ ------------ -------------- ----------------
Redo Allocation 274,639,862 1,060,115 105 0 0
Redo Copy 47,712 0 0 261,958,956 110,646
Redo On-Disk Scn 0 0 0 0 0
Redo Writing 20,458,194 1,022 0 0 0
------------ ------------ ------------ -------------- ----------------
sum 295,145,768 1,061,137 105 261,958,956 110,646
Elapsed: 00:00:00.05
=======================================
System Statistics
=======================================
Statistics Name Value
------------------------------ ----------------
redo synch writes 1,740,729
redo synch time 239,396
redo entries 261,876,265
redo size 42,971,119,216
redo buffer allocation retries 4,011
redo wastage 1,618,410,980
redo writer latching time 1,783
redo writes 6,375,731
redo blocks written 211,169,278
redo write time 710,421
redo log space requests 4,475
redo log space wait time 13,122
redo log switch interrupts 0
redo ordering marks 1,742
Can you tell me about following statistics in special
redo log space requests 4,475
redo log space wait time 13,122
and about others as general.
Is every thing OK?
relation of redo log member size with redo log space requests
Fayyaz ZAHEER, July 12, 2006 - 2:54 am UTC
in relation to my previous question,
I have 12 redo log groups with one member of size 102400 KB each
Is it OK or I have to increase/decrease size of redo log member ?
July 12, 2006 - 4:25 pm UTC
how about you answer my previous question as well??
You need more redo log space allocated - either by adding more online redo logs or making each you have "bigger".
Log_checkpoint_interval and timeout", version Oracle 8i
Karen Hughes, October 23, 2006 - 10:56 am UTC
Quick question; what's the implication of setting this following to 0?? The reason I ask is because these were all set to 0 when I took over and I am dead sure something is wrong. I would want to change the log_checkpoint_interval but maybe I shouldn't, though it may hurt restore periods??
October 23, 2006 - 12:42 pm UTC
why do you want to change and why are you dead sure this is wrong?
are you experiencing any "symptons" you believe are related to this?
if you set the log checkpoint interval, dbwr will checkpoint blocks to disk more frequently (you'll experience increased physical write IO probably) and it'll DECREASE (not hurt) your mean time to "recover" (not sure what "restore period" means in this context, assuming you mean time to recover from an instance failure - since this will not really affect time to restore from backups).
Re: Log_checkpoint_interval and timeout", version Oracle 8i
Karen Hughes, December 05, 2006 - 8:08 am UTC
Thanks Tom for that. I have another question but I will ask this as a new question.
Log_checkpoint_timeout and log_checkpoint_interval
Sanji, December 15, 2006 - 3:41 pm UTC
Tom
We are on 9i Rel2, HP-UX 11i. The OS block size is 8K
fast_start_mttr_target has not been set in the database,
Redo files are all 1 Gb.
log_checkpoint_interval=50000,
log_checkpoint_timeout = 1800,
By definition, OS Block size * log_checkpoint_interval would be the size of redo data to be flushed by DBWR between the checkpoint and the last redo record written 50,000 OS blocks ago.
Means there would be 8Kb * 50000 Blocks =>400 Mb of redo data between checkpoints.
1> Considering the fact that DBWR continuously writes cached data to disk in the background, is there a concept of incremental checkpoints when the log_checkpoint parameters are in place.
If yes, what determines the invoking of these incremental checkpoints, since Checkpointing after every log_checkpoint_interval redo blocks doesn't really look like incremental checkpointing.
2>What would be the instance recovery mechanism in this scenario.
Would it look for 400 Mb of redo data for rolling forward the db, because that is what is being defined by log_checkpoint_interval.
If not, then how would Oracle calculate the amount of data required for instance recovery.
3> We see a lot of "Checkpoint not complete" errors in the alert log. One reason can be slow DBWR. Can the other reason be attributed to log_checkpoint_interval setting, since it's generating a lot of redo to be flushed to disk by DBWR, and DBWR is not able to keep up.
Your feedback is greatly appreciated as always.
Thanks
Sanji
December 16, 2006 - 5:47 pm UTC
1) that is not a fact. you would set the init.ora parameters to cause that via incremental checkpointing (eg: like fast start mttr target)
2) it would be the same as anything. We apply the redo that needs to be applied and then rollback.
3) see #1
incremental checkpoint
Sanji, December 18, 2006 - 10:20 am UTC
Tom,
Considering the fact that fast_start_mttr_target is not set and log_checkpoint_interval with a value of 50000, or with log_checkpoint_timeout = 1800, Oracle generates 400 Mb of redo data between incremental checkpoints, is it wrong to deduce that in view of an instance crash, there "might" be a "maximum" of 400 Mb redo required during instance recovery ?
Thanks
Sanji
December 18, 2006 - 10:24 am UTC
or more actually...
CHECKPOINT QUEUE
Aman..., April 03, 2008 - 6:57 am UTC
Hi sir,
For the incremental checkpoint to happen , Oracle maintains the checkpoint queue(dirty queue also I guess was the old name).Its mentioned that there are two lists, one is LRU and is LRUW.The LRUW list contains the dirty buffers which are moved from the lru list to the LRUW list and willbe the candiate for the flushing to the datafile by dbwr.I was under the impression that Checkpoint queue contains the list of the blocks which are dirty and this list is scanned and flushed by dbwr by the setting of the fast_start_mttr_target.But here is one quote from 10g Performance tuning(OU) book
<quote>
To allow buffer replacements to occur, it is necessary to write cold dirty buffers that have aged out to the cold part of the list. Such writes are referred to as aging writes and are performed by DBWn processes. The DBWn processes work to write enough cold buffers to guarantee a uniform supply of free buffers for replacement purposes. However, because a hot buffer located in the hot part can have the first change in the redo log, aging writes might not be able to advance the thread checkpoint used for crash recovery purposes. That is why another list called the checkpoint queue, which orders buffers in lowest redo block address (RBA) order, is used in addition to the LRU list. Each working set contains two checkpoint queues each protected by a separate checkpoint queue latch of type checkpoint queue. This allows user processes to add buffers to one list while DBWn is writing buffers from the other list.</quote>
From this quote, here are the fewlines which I could not understand.These are in quotes.
>>However, "because a hot buffer located in the hot part can have the first change in the redo log, aging writes might not be able to advance the thread checkpoint used for crash recovery purposes. That is why another list called the checkpoint queue, which orders buffers in lowest redo block address (RBA) order, is used in addition to the LRU list." Each working set contains two checkpoint queues each protected by a separate checkpoint queue latch of type checkpoint queue. This allows user processes to add buffers to one list while DBWn is writing buffers from the other list.<<
Here are the doubts,
1)Is the first change that is mentioned here is called the high RBA?
2)What does it mean by this line "because a hot buffer located in the hot part can have the first change in the redo log, aging writes might not be able to advance the thread checkpoint used for crash recovery purposes. That is why another list called the checkpoint queue, which orders buffers in lowest redo block address (RBA) order"
3)Here is what I have understood that the LRUW list contains the blocks in the order of their most recent change and the checkpoint queue contains the link list of the low RBAs.Thsi means that when the incremental checkpoint will be requiredthat Oracle will flush the data from the LRUW first but it may be not the first change that is done to the block so to contain that change , the CKPTQ is maintained.is this correct sir?
4) What time than CKPTQ will be flushed?
I have asked this here as the thread corresponds to the incremental checkpoint and buffer cache sir.I hope it did not the ruleof posting a review.
Thanks and regards
Aman....
IT worker, June 25, 2008 - 12:31 pm UTC
we are on 10.2.0.3, we have seen redo log switch occuring every 2-3 min at peak hrs (we monitor the files timestamps changs).
We have the redo log size of 1G.
and parameter set as
log_checkpoint_interval= 10000
log_checkpoint_timeout= 300
question, beside the DMLs make the switchs, do these parameters have impact on the rate of the redo log switchs?
TIA
June 25, 2008 - 3:25 pm UTC
your logs switch when the fill up (at peak hrs... you generate more redo then one would presume)
you are generating that much redo, that is all
Please clarify
Satheesh Babu S, July 25, 2008 - 12:54 pm UTC
Hi,
In one of the response above you mentioned that incremental checkpoint will not be logged in the alert.log when log_checkpoints_to_alert=TRUE. But as per the note 568049.1, all the incremental checkpoint will be logged.
Looks like i missed something here.
Regards,
Satheesh Babu S
Checkpoint
Prasad, March 02, 2010 - 1:10 am UTC
If a Logswitch happens every 20 mins and LOG_CHECKPOINT_TIMEOUT is set to 30 mins will checkpoint occur at timeout?
March 02, 2010 - 7:26 am UTC
In current releases (9i and above) This parameter also signifies that no buffer will remain dirty (in the cache) for more than NNN seconds. It doesn't really fire a checkpoint (checkpoints are always happening these days, they are not singular events really). It is not a "timed thing" that happens every N seconds, it controls how long a block can remain dirty in the cache.
Therefore, if the log switch caused all of the blocks that would be more then NNN seconds dirty (10 minutes from now) to be flushed - then there would be nothing to do.
Don't think of this like a clock ticking time. Think of this more like an expiration date on a bit of food - (dirty block). We throw out the food (put block to disk) based on this.
Checkpoint
Prasad, March 02, 2010 - 1:11 am UTC
LOG_CHECKPOINT_INETRVAL parameter is set for 1M blocks. Will checkpoint occur after 1MB of write if my redolog size is 900K.
March 02, 2010 - 8:08 am UTC
fast_start_mttr_target is the setting to use in 9i and above.
the documentation however says
... log checkpoint interval: Therefore, if the value exceeds the actual redo log file size, checkpoints occur only when switching logs ...
pls question
A reader, February 21, 2017 - 9:49 am UTC
in my oltp database all the tables/indexes are created NOLOGGING.
and have those values of param.
log_checkpoint_interval integer 1
log_checkpoints_to_alert boolean FALSE
log_checkpoint_timeout integer 0
is that good parameters?
tkx
February 22, 2017 - 1:18 am UTC
I would not interval or timeout, and use fast_start_mttr_target instead.
And keep an eye on v$instance_recovery to see if you're happy with its estimate on recovery time, eg
SQL> select * from v$instance_Recovery
2 @pr
==============================
RECOVERY_ESTIMATED_IOS : 54
ACTUAL_REDO_BLKS : 319
TARGET_REDO_BLKS : 7718
LOG_FILE_SIZE_REDO_BLKS : 1244160
LOG_CHKPT_TIMEOUT_REDO_BLKS : 7718
LOG_CHKPT_INTERVAL_REDO_BLKS :
FAST_START_IO_TARGET_REDO_BLKS:
TARGET_MTTR : 0
ESTIMATED_MTTR : 24 <=======
CKPT_BLOCK_WRITES : 706076
OPTIMAL_LOGFILE_SIZE :
ESTD_CLUSTER_AVAILABLE_TIME :
WRITES_MTTR : 0
WRITES_LOGFILE_SIZE : 0
WRITES_LOG_CHECKPOINT_SETTINGS: 0
WRITES_OTHER_SETTINGS : 0
WRITES_AUTOTUNE : 1246237
WRITES_FULL_THREAD_CKPT : 0
CON_ID : 0
Pls elaborate
A reader, February 22, 2017 - 5:51 am UTC
Thank you for help.
And what recovery estimates may make me *happy*? (Or not).
How would and could calculate?
February 23, 2017 - 2:34 am UTC
That's the amount of time we expect the system to be available again, ie, time to start, if (say) you lost power.
Only you can decide that.