Behind the scenes

Executing SELECT Statement.

Execution of select statement is a staged process. Server process executing the statement will first check whether the blocks containing the data required are already in the memory, in buffer cache. If they are, execution can proceed immediately. If they are not, server must locate them on disk and copy them into database buffer cache.

If the query encounters a block that has changed since the time query started, the server process will go to the undo segment that protected the change, locate the older version of data and for the purpose of the current query only rollback the change.

Thus any changes initiated after the query commenced will not be seen.

Executing Update Statement.

For any DML operations it is necessary to work on both data blocks and undo blocks and also to generate redo blocks.

First step in executing DML is same as executing select; required block must be in buffer cache if It’s not it must be copied to buffer cache. The only difference is that the empty block of undo segment is needed to.

First, locks must be placed on any rows or underlying indexes that are going to be affected. Server process writes to log buffer the change vectors that are going to be applied to data blocks. This generation of redo is applied to both table blocks changes and to undo block changes.

If a column of a row is to be updated then the rowid and the new value of the columns are to be written to the log buffer and also the old value.

Having generated the redo, the update is carried out in database buffer cache and old version of changed column is written to the blocks of undo segment. From this point until the update is committed, all the queries from other sessions addressing the changed row will be redirected to undo data.

Executing Commit

When you say commit, all that happens physically is that LGWR flushes the log buffer to disk.

To make transactions durable all that is necessary is that changes that make up the transactions are on disk. There is no need whatsoever for the actual data to be on the disk.

If the changes are on the disk in the form of multiplexed redo files, then in the extent of damage to the database, the transaction can be re-instantiated by restoring the datafiles from the backup taken before the damage occurs after applying the changes from the logs.

Database writer background process has nothing to do with the commit process.

Executing Rollback

If the session that initiated the transaction fails, the PMON will detect that there is a problem with the session and rollback the transaction.

If the server is rebooted while the database is in use then the SMON will detect the problem and rollback the changes.

The pre-update version of the columns are stored in the blocks of undo segment are used to construct another update command that will set the values back to original.

To rollback an insert, oracle retrieves the rowed of the inserted row from the undo block and uses this as a key to generated delete command.