How does an INSERT/UPDATE statement behave when executed compared to a SELECT statement?

 

How does an INSERT/UPDATE statement behave when executed compared to a SELECT statement?

 

I know a SELECT statement's result is first searched in the SGA for the result set, and if not found the server fetches the result set and returns the rows upon saving a copy of the result set in the buffer for further such SELECT statements.

 

I vaguely remember reading that an INSERT statement first updates the physical data blocks on disk and retains a dirty buffer in SGA and stores the data block id in the undo segment for rollback. The same goes with UPDATE statement too, updated data goes to the disk(a dirty buffer is retained in SGA) while the old data is sent to the undo segments, again to address rollback operation....Is this correct? Could you please throw some light on this?

 

EXPERT RESPONSE

Your reading of the behavior of the INSERT statement is incorrect. DML statements, (INSERT/UPDATE/DELETE) never modify data directly on disk. They only modify blocks in the buffer cache. Undo information will be written to blocks belonging to the Undo tablespace, but those blocks are also in the buffer cache. Information to redo the transaction is written to the log buffer…again in memory. The log buffer also contains information to redo the undo blocks as well.

 

The only time you know for sure that blocks on disk are being written is when the user issues a COMMIT. A commit is not complete until the relevant information is written from the log buffer to the online redo logs. In some cases, the database data files are not changed until some time after the commit. If Oracle crashes, it has everything it needs in the online redo logs to restore the committed transaction.

 

While all of this is going on, the checkpoint process (CKPT) is responsible for making sure that changes in the buffer cache get written to disk. CKPT will cause the database writer (DBWn) to write changes to disk. But these changes can be a mixture of committed and uncommitted data.

 

 

Scenario:

Assuming that you have 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 connecting 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 Row 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 performed.

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).