What is happening when update statement?

aloneboylz

Scenario:

Assuming that a user is updating a record from her SQLPLUS. The following SQL statement is her SQL transaction:

SQL> UPDATE emp SET sal = 1000 WHERE empno = 100;

SQL> COMMIT;

Let us see what would be happen when oracle process it.

Step 1:

The user will type the above SQL statement and press enter key. This user either is connect to the database by dedicated server or shared server (MTS). If the user is using multi-threaded servers then her request will be given to a dispatcher and the dispatcher will give the request to shared server. If the user is using dedicated server then the dedicated server will be all hers. Now, her user process is talking to shared or dedicated server.

Step 2:

Now, the user’s SQL statement will be parsed and assigned an executed plan to be compiled in the Library Cache in the Shared Pool. In order the SQL statement be compiled, Oracle need to make sure its table and columns are valid and the user did not violated any security information. It goes to the Dictionary Cache known as Raw Cache to get all necessary information about the table. If there was no syntax problem and its table and columns were valid, then the SQL statement will be parsed successfully and the execution plan will be perform.

Step 3:

Now, there is no problem. The Server process fetches the record. If the data or record is in the Buffer Cache then an update process will be applied to it and the block will be marked as dirty block. Notice that before the user save the update, the before block images are in the UNDO segment. When the user executes commit statement or more than one third of the Redo Log buffer have filled out, then LGWR writes the user’s entries from the redo log buffer to the Online Redo Log files. Still the block may not been stored in the database.

In the case that the record is not in the buffer cache, the server process read the block containing the record from the datafile (disk) and places it into the buffer cache.

Step 4:

Now, the checkpoint process will be activated based on the LOG_CHECKPOINT_INTERVAL, LOG_CHECKPOINT_TIMEOUT parameters, or may be due to a log switch. This action will force DBWR or CKPT to write all dirty block in the database (datafile).