| Inner workings: how often is data written to disk [message #402851] |
Tue, 12 May 2009 15:39  |
Joppybt
Messages: 7
Registered: May 2009
|
Junior Member |
|
|
Out of curiosity I was wondering how often Oracle writes information for one update statement.
Assume you do an update statement of a very large (1MB, BLOB) field in an table.
(generating UNDO)
1) a copy of the original data is appended to the REDO log
2) next it is actually written to the UNDO datafile.
(actual update)
3) the new data is appended to to REDO log
4) the new data is actually written to the final datafile.
(some time later)
5) the block is once more written when the REDO log is archived.
Even when I do not count minor writes like updates to controlfiles, is Oracle really writing the data 5 times? Now I understand why it is so robust.
Update: I just read in 'Oracle® Database Administrator's Guide 10g Release 2 (10.2)'
| Quote: | Note: Automatic tuning of undo retention is not supported for LOBs.
This is because undo information for LOBs is stored in the segment itself and not in the undo tablespace
|
So BLOB's do not end up in the UNDO? But for example for a large VARCHAR2(4000) it would still apply.
[Updated on: Tue, 12 May 2009 17:03] Report message to a moderator |
|
|
|
|
|
| Re: Inner workings: how often is data written to disk [message #403293 is a reply to message #402909] |
Thu, 14 May 2009 11:30  |
Joppybt
Messages: 7
Registered: May 2009
|
Junior Member |
|
|
| Michel Cadot wrote on Wed, 13 May 2009 08:18 | | Quote: | Out of curiosity I was wondering how often Oracle writes information for one update statement.
|
If you meant dirty blocks on data file, often enough to try to satisfy fast_start_mttr_target parameter (or equivalent).
Regards
Michel
|
So is this correct?
1) and 3), writing to the REDO-log always happens, immediately (3 seconds) bij the LGWR process
2) and 4), writing to UNDO and DATAFILE is buffered in the cache and can be postponed quite a while (up to the next logfile switch). Setting fast_start_mttr_target (or equivalent) can cause this to happen sooner/more often. In the end the writing is done by the DBWR process.
Effectively Oracle can avoid many random writes to the DATAFILES by first doing sequential writes to the logfiles and buffering the rest?
|
|
|
|
|
|
| Re: Inner workings: how often is data written to disk [message #403297 is a reply to message #403295] |
Thu, 14 May 2009 12:08 |
Joppybt
Messages: 7
Registered: May 2009
|
Junior Member |
|
|
| Michel Cadot wrote on Thu, 14 May 2009 18:49 | | Quote: | Effectively Oracle can avoid many random writes to the DATAFILES by first doing sequential writes to the logfiles and buffering the rest?
|
I don't understand what you mean.
|
Sorry, tring to cram too much information into one sentence 
Oracle keeps a dirty block in memory a while before writing it to disk. In that period it can even be overwritten in memory by a second update of the same block. By postponing writing, the first and second update are written 'together'.
As for the 'random' remark: writes to datafiles can be scattered all over the datafile, slow on harddisks. Writing to REDO-logs is much 'cheaper' as it is always sequentially at the end.
[Updated on: Thu, 14 May 2009 14:47] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
