When a user issues a query

When a user issues a query ?

A SP is created and the related objects checks in the data dictionary cache, based on the objects parsing and execution plan is created in the library cache, depending on the execution plan it will check the data in database buffer cache, if found it will give the result to the user via SP, if not it will retrieved the data from the disk.

when a user issues a insert,update, delete statement ?

In our case data is still in the SGA database buffer cache.

Data updated in the db buffer cache and marked as dirty buffer, immediately statement is placed into redo buffer, row updated message return to the user.

After issuing commit:

1.new SCN obtained from controlfile

2.commit place into redo buffer.

3.LGWR writes redo buffer contents to redolog files & remove from redo buffer.

4.Controlfile is updated with new SCN.