FSIOT vs. DB_BLOCK_MAX_DIRTY_TARGET
Andre Whittick Nasser, December 04, 2001 - 8:51 am UTC
Tom,
I was taking a closer look at all those parameters regulating checkpoint activity:
- LOG_CHECKPOINT_INTERVAL
- LOG_CHECKPOINT_TIMEOUT
- DB_BLOCK_MAX_DIRTY_TARGET
- FAST_START_IO_TARGET (EE)
- FAST_START_MTTR_TARGET (9i)
I understand the idea behind each of the 5 parameters above, including the checkpoint queue, etc.
All os there parameters regulate "how much" the data files may be "inconcistent" with the redo files, so for example, in the case of an instance recovery (roll forward), it's possible to impose a "limit" on the blocks applied.
My question is about DB_BLOCK_MAX_DIRTY_TARGET (here DBMDT) and FAST_START_IO_TARGET (here FSIOT).
Now, conceptually, FSIOT and DBMDT are different:
- FSIOT tunes how many blocks will be applied in the roll forward process, so you can e.g. agree on a SLA with a customer or management.
- DBMDT is the greatest number of dirty blocks in the buffer pool, when the server process triggers DBWR to flush.
However, in practice, they share the same essence, that is, the (approximate) maximum number of blocks that are allowed to be inconsistent between data files and redo files.
My question is, since they are functionally very close, can I simulate FSIOT using DBMDT ? After all, why FSTIO ?
December 04, 2001 - 12:47 pm UTC
they are functionaly very close and in 9i, fsiot is depricated in favor of a fast_start_mttr_target specified in seconds to recover, rather then IO's to recover.
About dirty blocks
Andre Whittick Nasser, December 04, 2001 - 6:35 pm UTC
Thanks again.
One more question.
During DML processing, DBWR flushes the buffer pool to the datafiles in some situations:
a) Every 3 seconds;
b) When the server process searches the LRU list for free blocks up to a certain limit;
c) When the dirty list reaches a threshold because of block coming from the LRU list;
d) When signaled by LGWR.
Both values b) and c) are not documented nor directly related to any parameter. Are they "hardcoded" ?
What makes a lot of sense to me and yet I was not able to test, is: Is DB_BLOCK_DIRTY_TARGET the limit in item c) ?
December 05, 2001 - 9:00 pm UTC
well, letter a sounds like LGWR with respect to the redo buffers, not DBWR.
C would match the description of that init parameter, yes.
LGWR
Andre Whittick Nasser, December 06, 2001 - 2:19 am UTC
Sorry Tom,
You're right. a) is related to LGWR, of course.
Thanks once more
Truncate and Checkpoint
Nathan, August 19, 2003 - 6:48 am UTC
We have a high insert ( over 35 million rows inserted per hour), with followingh config
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 4,096M Std Block Size: 8K
Shared Pool Size: 256M Log Buffer: 153,600K
A truncate of 2.3 million row table took over 29 minutes.
I did a test , here by truncate a table with one column , one row and I selected it once , It took over 5 minutes ...
I understood from Jonathan Lewis's column it was due the checkpoint activity .... but i'm not convinced about the time it takes for this operation ..
Also interestingly ,
dbms_stats.gather_table_stats(ownname=>'STBMADM',tabname=>'REPORT_STBL',estimate_percent=>0.1);
of 2.3 million row table takes over 50 minutes !!!!
I have just taken up this new role .. and these numbers are all too huge for me and i'm puzzled .. is there anything fundamentally wrong ? like version 9.0.1.2.0...? would a patch resolve these issues ?
Thanks
Nathan
August 19, 2003 - 7:52 am UTC
the patch would be faster disk?
you understand that it takes quite a while to checkpoint individual blocks (scattered writes all over the place -- worst possible kind of IO).
Perhaps you should be DIRECT PATHING this data, no dirty blocks in the buffer cache to checkpoint.
Have you taken the time to see what your disks are actually doing during these operations? you might be surprised. remember if you load 35million rows using conventional sql, they will load as "dirty blocks" that need cleaning -- so that analyze for example will "clean" each block (eg: a read will generate redo and re-dirty the block so if you loaded a block and it got flushed, the next read of the block could re-dirty the block and cause it to be written yet again)
sounds like a system in need of some direct path options.
MTTR Setting
Preeti, August 24, 2004 - 7:40 am UTC
Tom,
My database is in Noarchivelog Mode and Cold Backup is taken once a week.
The Redo Log file Size is 500MB and during peak batch procesing load the number of log switches are between 10 to 20. Current Value for parameter "fast_start_mttr_target" is 300.Is there any scope of tuning I/O by changing this parameter.
Thanks for any help.
August 24, 2004 - 7:54 am UTC
wow, you are going to lose a weeks worth of data -- not "might", but WILL -- absolutely dead on WILL lose a weeks worth of data.
wow... must not be important stuff at all.
fast start mttr target controls incremental checkpointing in Oracle. It'll cause dbwr to flush dirty blocks from the buffer cache to disk more aggresively so that if the instance fails (power outage for example), it'll take less time to restart (less time spent on instance recovery).
so, forget about the number of log switches (they each fire off a background checkpoint event, but they are not as aggresive as the fast start mttr would be, unless of course you hit checkpoint not complete in your alert log...) -- fast start mttr would cause dbwr to be writing more continously, more often, more aggresively. is that what you want?
I feel it is extremely short sighted of you to run an active system in noarchivelog mode. You WILL LOSE EVERYTHING someday, it is only a matter of time. forget about IO rates, you have much larger fish to fry here.
MTTR
Preeti, August 24, 2004 - 7:42 am UTC
The Log Switching mentioned above is per hour.
Thanks.
MTTR
Preeti, August 25, 2004 - 4:14 am UTC
Thanks Tom but choice to run it in Noarchivelog Mode is not mine.
August 25, 2004 - 7:40 am UTC
just make sure the people who care know that
"100% loss of all data is going to happen".
MTTR
Preeti, August 28, 2004 - 3:20 am UTC
Tom,
I ensure Export Dump is taken every day and the data can be restored.
Thanks Again!
August 28, 2004 - 9:57 am UTC
so? so what?
100% loss of data is the only thing assured in this situation.
Your export is "as of a point in time", you will lose EVERYTHING that happens after the export.
do you export with consistent=y? if not, odds are importing that data will result in a "huge mess of inconsistent, bad, useless data"
have you ever done the full import to see if you can even do it?
dmp files make poor backups, terrible backups, less than useful backups.
FAST_START_MTTR_TARGET = 0
AnilP, April 27, 2005 - 12:54 am UTC
Dont know whether this question is a repeated one. what will be the mean time to recover after instance
failure when FAST_START_MTTR_TARGET is set to zero
(0)?
Thanks
AnilP
April 27, 2005 - 7:47 am UTC
as long as it takes, you didn't set an MTTR so it'll be a function of how much redo must be applied to catch the database back up. Setting the MTTR target limits the amount of redo that needs be applied, not having -- doesn't
help
Aru, May 25, 2005 - 12:32 am UTC
Hi Tom,
You said in previous question that:-
"as long as it takes, you didn't set an MTTR so it'll be a function of how much redo must be applied to catch the database back up. Setting the MTTR target limits the amount of redo that needs be applied, not having -- doesn't
"
But Oracle9i comes with default value of:-
300 for fast_start_mttr_target
0 for log_checkpoint_interval
0 for fast_start_io_target.
This would mean that as long as log_checkpoint_interval and fast_start_io_target are 0 then there is 300 seconds of redo that will be needed for instance recovery. I don't get it frankly!.
Also when I select TARGET_MTTR from v$instance_recovery
the value returned is 63. More confusion, I thought it was
going to be 300.
Pls. help!!
Regards,
Aru.
May 25, 2005 - 7:45 am UTC
the default for 9i is zero for fast start mttr target
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch164.htm#1017170 <code>
but what don't you get? IF you have it set, THEN the recovery time is to be bounded by that value. ELSE if it is not set, it is not bounded by that.
the log_* parameters are sort of a "gross" or "obtuse" way to bound recovery time. the FAST_START_* parameters were designed to be an "easier", more fine tuned way to bound the recovery time.
the target_mttr is documented as being a calculated value (it would be redundant if it were just the parameter setting)