Skip to Main Content
  • Questions

  • Writes to Log Files Vs. Writes to Data Files

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sujith.

Asked: January 16, 2006 - 10:16 am UTC

Last updated: January 23, 2006 - 10:15 am UTC

Version: 9205

Viewed 1000+ times

You Asked

Hi Tom,
When a COMMIT occurs, Oracle writes to the on-line REDO logs, but not to the Datafiles. Why is that??
What are the reasons behind to say writing to REDO logs are faster or inexpensive compared to writing to datafiles??

Could size of the file have an effect here?? the log files are relatively small compared to datafiles. So, one could think to open log files and update it, is faster than opening a data file. But again, all the data files are opened by oracle while the database is open, isn't it??

Please educate me on this issue,

Thanks and I appreciate your help.

Kandy_Train

and Tom said...

The reason is primarily one of performance.

Say you insert a 40 byte row. It modifies:

a) a database block for the table
b) a database block for the undo for the table
c) a database block for each of the indexes at least (maybe more than a block, index splits and all)

Say you have an 8k block. That is at least 3 8k blocks (assuming one index and no splits). Maybe many more (index splits, more than one index, space management, auditing, etc...)

Versus the say 500 bytes of redo for this single row and index. We can write significantly less at the time of the commit.

Also, redo is "sequential IO", we are writing to a file from the start to the finish. Datafile writes would be scattered literrally all over the place - tons of random IO.

Not all of the datafiles are opened by Oracle.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another reason

Tamil, January 16, 2006 - 2:31 pm UTC

Another primary reason is recovery.

With out redo log entries, oracle may not able to recover the database at point in time.

Tamil

Tom Kyte
January 16, 2006 - 3:43 pm UTC

this is true - there are many reasons. performance, recovery (all types)....

Writes to Log Files Vs. Writes to Data Files

Sujith Wimalasooriya, January 21, 2006 - 9:21 pm UTC

Hi Tom,

Thanks for the response.
So, when Oracle deals with datafiles, it uses blocks,
because the smallest unit is a block on a Database Object.

but when dealing with online redo, it uses bytes. This should be true for any other file types, like control files, alert.log, recovery files.

Is that true??

Thanks again..

Kandy_Train

So, basically

Tom Kyte
January 22, 2006 - 9:35 am UTC

control files have "records" of varying widths - but yes, control files are written to in "bytes".

alert log, with "strings" of different numbers of bytes (but this is a bad file to compare to a data file, redo log file and so on)


Not all of the datafiles are opened by Oracle.

Pravesh Karthik from Chennai, January 23, 2006 - 4:30 am UTC

Tom,

when you say 'Not all of the datafiles are opened by Oracle.' .. Is there any datafile that is opened by Oracle to write. Please let us know which are so. You mean System datafiles? or anything more.

Thanks,
Pravesh Karthik


Tom Kyte
January 23, 2006 - 10:15 am UTC

any file could be opened, or not opened. they are all available - we only open them when we need to.



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.