Home » RDBMS Server » Performance Tuning » Inner workings: how often is data written to disk
Inner workings: how often is data written to disk [message #402851] Tue, 12 May 2009 15:39 Go to next message
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 #402909 is a reply to message #402851] Wed, 13 May 2009 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: Inner workings: how often is data written to disk [message #403293 is a reply to message #402909] Thu, 14 May 2009 11:30 Go to previous messageGo to next message
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 #403295 is a reply to message #403293] Thu, 14 May 2009 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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.

The rest is ok, many reasons can lead DBWR to write, log switch and fast_start_mttr_target are 2 of them.

Regards
Michel
Re: Inner workings: how often is data written to disk [message #403297 is a reply to message #403295] Thu, 14 May 2009 12:08 Go to previous message
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 Embarassed

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

Previous Topic: query
Next Topic: Index is not working properly in my query ?
Goto Forum:
  


Current Time: Sat Dec 03 22:16:19 CST 2016

Total time taken to generate the page: 0.08150 seconds