I know a SELECT statement's result is first searched in the SGA for the result set, and if not found the server fetches the result set and returns the rows upon saving a copy of the result set in the buffer for further such SELECT statements.
I vaguely remember reading that an INSERT statement first updates the physical data blocks on disk and retains a dirty buffer in SGA and stores the data block id in the undo segment for rollback. The same goes with UPDATE statement too, updated data goes to the disk(a dirty buffer is retained in SGA) while the old data is sent to the undo segments, again to address rollback operation....Is this correct? Could you please throw some light on this?
The only time you know for sure that blocks on disk are being written is when the user issues a COMMIT. A commit is not complete until the relevant information is written from the log buffer to the online redo logs. In some cases, the database data files are not changed until some time after the commit. If Oracle crashes, it has everything it needs in the online redo logs to restore the committed transaction.
While all of this is going on, the checkpoint process (CKPT) is responsible for making sure that changes in the buffer cache get written to disk. CKPT will cause the database writer (DBWn) to write changes to disk. But these changes can be a mixture of committed and uncommitted data.