How update statement works internally in oracle

UPDATE command in sql is used to modify existing records in a table. How SQL UPDATE statement works inside the Oracle architecture and the sequence of steps which oracle follow internally for the execution of update statement is explained below.

UPDATE table_name

SET column1 = value1, column2=value2, …

WHERE condition;

How does the update query execution occur?

1. 1. SQL*PLUS checks the syntax on client side.

1. 1. If syntax is correct the query is stamped as a valid sql statement and encrypted into oci (oracle call interface) packets andsent via lan using tcp to the server.

1. 1. Once the packets reach the server the server process will rebuild the query and again perform a syntax check on server side.

1. 1. Then if syntax is correct server process will continue execution of the query.

1. 1. The server process will go to the library cache. The library cache keeps the recently executed sql statements along with their execution plan.

1. 1. In the library cache the server process will search from the mru (most recently used) end to the lru (least recently used) end for a match for the sql statement. It does this by using a hash algorithm that returns a hash value. If the hash value of the query we have written matches with that of the query in library cache then server process need not generate an execution plan (soft parsing) but if no match is found then server process has to proceed with the generation of execution plan (hard parsing).

1. 1. Parsing is the process undertaken by oracle to generate an execution plan.

1. 1. The first step in parsing involves performing a symantic check. This is nothing but check for the existence of the obj and its structure in the database.

1. 1. This check is done by server process in the data dictionary cache. Here server process will ask for the definition of the object, if already available within the data dictionary cache, server process will process the check. If not available then server process will retrieve the required information from the system tablespace.

1. 1. After this in case of hard parsing the server process will approach the optimizer, who will read the sql statement and generate the execution plan of the query. The optimizer generates multiple execution plans during parsing.

1. 1. After generation of the e-plan’s by the optimizer the server process will pick the best possible and cost effective e-plan and go to the library cache.
   2. In the library cache the server process will keep the e-plan in the library cache along with the original sql text.

1. 1. At this point in time the parsing ends and the execution of the sql statement will begin.

1. 1. After generation of e-plan server process will keep the plan in the library cache on the mru end.

1. 1. Thereafter the plan is picked up by the server process and execution of the update will begin.

1. 1. Server process will bring the required blocks from the specific datafile of the table which has to be updated.

1. 1. The blocks will be brought into database block buffers(or database buffer cache).

1. 1. The blocks will be containing the original data of the table.

1. 1. Then server process will bring equal no of empty blocks from the undo tablespace and they will also be brought into the database block buffers(or database buffer cache).

1. 1. Server process will copy the original data from the userdata blocks into the empty rollback/undo blocks and create a before image.

1. 1. Then server process will bring a set of userdata blocks into the pga (program global area) and after performing filter operations the selected rows will be updated with new content.

1. 1. The above update process will continue until all the userdata blocks have been checked and updated.

1. After the update operation is complete then dbwriter will write the data back to the respective datafiles after a certain time gap.