- ANKUSH THAVALI
- 13 Sep, 2023
- 0 Comments
- 1 Min Read
How does Update Statement Works in Architecture Level?
How does Update Statement Works in Architecture Level?
Sqlplus sys/oracle@prim SQL>select * from emp; SQL>update emp set salary=50000 where empid=40; SQL>commit;
So now we will understand How update statement works internally.
- Once we hit sqlplus statement client process(user) access sqlnet listener.
- Sql net listener confirms that DB is open & create server process.
- Server process allocates PGA.
- ‘Connected’ Message returned to user.
- SQL>select * from emp;
- Server process checks the SGA to see if data is already in buffer cache.
- If not then data is retrived from disk and copied into SGA (DB Cache).
- Data is returned to user via PGA & server process.
- Now another statement is
SQL>Update emp set salary=50000 where empid=40;
- Server process (Via PGA) checks SGA to see if data is already there in buffer cache.
- In our situation chances are the data is still in the SGA (DB Cache).
- Data updated in DB cache and mark as ‘Dirty Buffer’.
- Update employee placed into redo buffer.
- Row updated message returned to user
- SQL>commit;
- New SCN obtained from control file.
- Data in DB cache is marked as ‘Updated and ready for saving’.
- commit placed into redo buffer.
- LGWR writes redo buffer contents to redo log files & remove from redo buffer.
- Control file is updated with new SCN.
- Commit complete message return to user.
- Update emp table in datafile & update header of datafile with latest SCN.
- exit from SQL prompt.
- Unsaved changes are rolled back.
- Server process deallocates PGA.
- Server process terminates.
- After some time redo log are archived by ARCH process.