Detailed explanation of the processing process of Oracle Database DML commands

OracleDatabaseDML commandThe processing process is what we will introduce in this article. From Oracle 9i, there are two undo Management Methods: Automatic Undo Management (AUM) and Manual Undo Management (MUM ). Before Oracle 9i, only MUM can be used. In MUM, undo segment is also called rollback segment. Since Oracle 9i, we recommend that you use AUM instead of MUM.

DML statement and undo

When we issue a dml statement such as update tab set col1 = 'A' where col1 = 'B'), the execution process can be roughly summarized into the following steps.

1. parse in the shared pool to generate an execution plan.

2. Assume that the records of col1 = 'B' are stored in the No. 54 data block of the No. 10 data file according to the execution plan.

3. The server process finds an available undo data block in the buffer cache. If no undo data block is found, find an available undo block in the undo tablespace and transfer it to the buffer cache. Assume that the obtained undo data block number is 24 and is located in the undo data file no. 11.

4. Place the previous value, that is, A, into the undo data block on the 11th.

5. A redo record is generated because the undo data block has changed. Assume that the redo record number is 120.

Row number transaction id file # block # row column value

120 T1 24 11 10 col1

6. Find the No. 54 data block in the buffer cache. If no data is found, it is transferred from the data file no. 10.

7. Place the changed value, that is, B, into the 54 th data block.

8. A redo record is generated because the data block has changed. Assume that the redo record number is 121.

Row number transaction id file # block # row column value

121 T1 10 54 10 col1 B

9. The control is returned to the user. If DML is executed in SQL * Plus, the cursor is returned.

10. When the user issues the commit command, the LGWR process is triggered to write the redo records 120 and 121 to the online log file, set the transaction status mark recorded in the header of Data Block 54 and undo data block 11 to submitted. Then the control is returned to the user. If you perform the DML operation in SQL * Plus, the cursor is returned.

11. At this time, the No. 54 data block and the no. 11 undo block are not necessarily written into the data file by DBWn. Data is written only when the number of dirty data blocks reaches a certain level.

As long as the transaction is committed or rolled back, the undo block used by the transaction can be overwritten. For the above example, after the user issues the commit command in step 1, the data in the undo block on the 11 th can be overwritten by other firms.

This article describes how to process the DML commands of Oracle databases. I hope this article will help you gain some benefits!