Just putting into words what I understand about how Oracle database handles the reading and writing of data.

Did some brain storming in a hope that it will be useful to somebody.

Reading in Oracle is completely handled by Server processes.

All instruction (reading or writing) from client’s processes first goes to server process.

Reading the Data

• The server process first checks the buffer cache for the presence of data.
• If not found then only copy the data from datafile to buffer cache.
• Then send the data to the client.

DML operations

INSERT

A space is found in the block in the buffer cache and data is inserted into the buffer cache.

UPDATE or DELETE

• First the server process checks the buffer cache for the presence of data
• If not found then only copy the data from datafile to buffer cache.
• Then copy the affected buffer blocks to undo segment in UNDO tablespace for the purpose of read consistency and rollback operation.
• Then only modify or delete the data in buffer cache.

Writing of data from modified buffers (dirty buffers) to datafile is done solely by DBWR process.