Data flow architecture of oracle database

Sqlplus scott/tiger@mandb

SQL>select * from emp;

SQL>update emp set salary=10000 where empid=5;

SQL>commit;

1. sqlplus command is fired, the user process (client) access sqlnet litener.

2. sqlnet listener confirms that database is open & creates a server process.

3. server process allocates PGA into memory. Each server process has its own PGA area, PGA contains data & control information of server process.PGA ( Private Sql area & Session Memory ).

4. ‘Connected’ message returned to user.

5. select * from emp;

6. select statement is parsed into shared pool ( library cache & data dictionary cache) , this is called hard parse because the select statement is not already present in shared pool. An executable code is generated for select statement in shared pool. Server process got the all necessary information for select statement before fetch.

7. server process takes the data from data file , loads into the buffer cache, puts into the most recently used(MRU) area & gives the data to the client via PGA.

8. update emp set salary=10000 where empid=5;

9. Before update statement , I want to say that empid=5 has a salary=8000

10. update statement is parsed & executed in shared pool.

11. server process checks( via PGA) buffer cache for data is available or not.

12. In our case data is available in buffer cache.

13. So data is updated into buffer cache with new salary i.e., 10000 & keep old copy i.e., 8000 into undo blocks for rollback.

14. update is placed into redo log buffer.

15. row updated message returned to client via Server Process(via PGA).

16. commit;

17. server process sends a commit to redo log buffer , a SCN is assigned.

18. LGWR process writes redo buffer contents to current online redo log file on disk.

19. CKPT process updates the header of all control files with new SCN. It signals DBWR to write modified blocks (dirty blocks) from buffer cache to data file on disk.

20. commit complete message return to user.

21. DBWR process writes modified blocks from buffer cache to data file on disk & CKPT updates header of the data file with new SCN.

22. Undo data blocks associated with this transaction are released from Buffer cache.

23. exit