How DML Works

1. Server process send the request of user process to Library Cache.
2. Server process will Handover the Statements to “library Cache” (L.C) of shared pool.
3. A Pre-check will be done for the existence of parsed statement.
4. If the server process doesn’t finds an already parsed statement, it will initiate the phase of Parsing ( First phase in SQL execution).
5. Parsing will be done in three steps.

         a. Dividing the SQL statement into literals.
         b. Checking for syntax error.
         c. Checking for symantic error.

6. The statement will get executed in PGA (using the best execution plan provided by the optimizer).
7. Since DML are changes to the DB redo entries will be Generated.
Redo entry or redo record or change entry (vector ): A single automatic change happen to the DB.

        Ex: if a statement is updating 100 rows 100 redo entries will be generated 
        i.e 100 times DB got modified or changed.

8. Server process will copy the generated redo entries from PGA to log Buffer cache.
9. By taking information from DDC ( data dictionary cache ) server process will check for the data in LRU LIST of database buffer cache.
10.If the data is not found server process will copy both data block and undo block to MRU END of LRU LIST of database buffer cache.

Note: if the data is available in LRU LIST server process will copy only undo block.

11. The old value which is there in data block will be moved to undo block, a new value will be inserted into data block.
12. The modified block are dirty blocks will be moved to write list when they reach LRU end.

Note: every block in database buffer cache holds any of the following:
UN-USED BLOCK : the block is never used.
FREE BLOCK    : the block had been used previously but currently it as free.
PINNED BLOCK  : the block which is currently in use.
DIRTY BLOCK   : the block which got modified.

13. Database writer will write down the contence of write list to corresponding data files.
14. Before DB writer log writer will write down the contence of log buffer cache to redo log files.