Skip to Main Content
  • Questions
  • Physical writes for delete vs update statement.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rahul.

Asked: March 28, 2017 - 9:10 am UTC

Last updated: March 28, 2017 - 11:31 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hello,

The DBWR will write out the whole block even if only few bytes have changed following an DML statement. Considering the db block size of 8K and I have a table of just 7 columns with row size small enough to fit into a single block. If I execute a update statement which changes value of one column of a single row, then oracle will write out the whole block to the datafile. So if I delete the same row then also same 8K write out operation will be performed. So what will be the overhead in terms of database resource when deleting the one row vs updating one column of the same row? Specifically in terms of Elapsed time, DB CPU, Size and number IO requests.

Ref question : How are dirty buffers written to the datafiles?

Thanks,
Rahul

and Connor said...

The DBWR is a background process, not a foreground one.

So when you update a column in the single block table....DBWR does nothing. DBWR doesnt really care what *you* do, it is "detached" from your activity.

It will just work away in the background, flushing those dirty blocks out to disk. This means...

- You might do 1 update...and the block might get flushed.
- You might do 50 updates... and the block might *not* get flushed
- 30 different sessions might do 10 updates each to that one block... and the block might *not* get flushed

That is perhaps a fundamental motivation for a dedicated DBWR, it does "just enough" work. After all, why bother flushing out a dirty block every time someone changes it - that is wasted effort, and wasted effort means less efficiency.

Obviously an insert vs an update vs a delete will different amounts of *redo* log associated with them, but that is the redo volume not the database blocks.


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