How SGA works if the user process issuing SELECT statement to the database?

 

When a query is submitted by the user it comes to the shared pool and it is processed by the library cache i.e. statement is parsed their, execution plan is prepared and data is retrieved from the datafile and stored in the database buffer cache so that if next time the same query is given then it doesn't go for the whole process instead it just retrieves the data which is in the buffer.
 

select * from emp;
server process (via the PGA) checks SGA to see if the data is already in the buffer cache
If not then data is retrieved from disk and copied to the SGA (database buffer cache)
Data is returned to user via the PGA and server process.
Cached data is left in the SGA

Server process (via the PGA) checks SGA to see if the data is already in the buffer cache
In our situation chances are the data is still in the SGA
Data updated in the database buffer cache and marked as ‘dirty’
‘update emp’ placed into redo buffer
Row updated message returned to user

Data in the database buffer cache marked as ‘updated and ready for saving’
‘commit’ placed into redo buffer
LGWR writes redo buffer contents to redo log files & removes from redo buffer
Control file updated with latest SCN
Commit complete message returned to user
Later ... DBWR updates emp table in the data file and updates the header of the data file with the latest SCN

exit;
Unsaved changes are rolled back (we don’t have any)
Server process deallocates the PGA
Server process terminates
Remote user terminates
After some period of time - redo logs are archived by ARCH process