Oracle Update Statement Internal Working Mechanism

In this article we are going to discuss how oracle update statement work internally. Here, we try to explain oracle update statement architecture with an example. We assume update a value "Sarker" from "Masud" for EmpName column of Employees table.

Update Statement: 

UPDATE EMPLOYEES SET EMPNAME='Sarker' WHERE EMPID=1001;

Let's explain oracle update statement architecture briefly. 

Step 1: Oracle server process first receive the update statement from client and before starting the update server process require the validation check of the update statement. Server process validate the syntax and semantic of the update statement whether the update statement is valid or not. 

Syntax validation means check the statement whether it is valid or not.

For example: update Employee Empname='Sarker' where empid=1001;

Output: ORA-00971: missing SET keyword 

Above query syntax validation fail because SET keyword is missing.

Semantic validation means check the statement whether it is meaningful or not

For example: update Employee1 set Empname='Sarker' where empid=1001;

Output: ORA-00942: table or view does not exist

Above query semantic validation fail because EMPLOYEE1 table doesn't exist. Moreover, in data dictionary of shared pool, the server process checks the semantic of the sql statement and in this stage server process check the definition of the object.

 Step 2: After syntax/Semantic check pass then server process will go to the library cache and in library cache server process will search the statement for match the sql statement. If the update sql statement match in library cache sql statement don't generate execution plan this is called soft parse on the other hand if don't match the update statement server process will generate multiple execution this is called hard parse. 

Step 3: After completing the statement parsing process the update statement execution job will began. In this stage server process bring the original data blocks into database buffer cache from respective data file of the table which has to be updated. Blocks contain original data of the table.

Step 4: Server process will bring the same number of blocks from undo tablespace into database buffer cache. At this time old value will be stored in undo block and undo block that contain the old value of update statement will be reserved no other transaction will not be able to reuse this undo block until the current query complete as well as original data block in database buffer cache will be lock. During this update process when other transaction like update, delete issues on the same data block it will be waiting until the current transaction issues rollback/commit. This scenario is called row level lock and this is one of the oracle features that ensure consistence of the data. A copy of the old, new data and sql id will be stored in the redo log buffer.

Step 5: The user will now have two options rollback/commit. However, if the user issues a rollback it means no data will be updated in the original block. As the undo has the old data so old value bring from undo block and original block will be replaced by the old value in the database buffer cache. 

Another scenario, now if the user issues a commit statement, then undo block will be release for reuse by other transaction and the original block value has been changed in the database buffer cache as well as row level lock will be released. At this time original blocks of the database buffer cache will be declared as the dirty buffer. After issues the commit statement the transaction is assigned a system change number (SCN) number and oracle background process LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the transaction's redo entries. Oracle guarantees that all changes will be written to redo Log immediate commit is run, but oracle does not guarantee that changes will be written to the data file immediate commit is run. Recover database change data from Redo Logs if database crash before writing changes to data files. During the next checkpoint, Oracle background process database writer will write all the dirty buffer of database buffer cache in the physical data file.

Thanks