How A DML Statements Is Processed And Committed in Oracle?

Here we are going to understand DML query processing and committed in details

• When the user executes the DML statements, the server checks the shared SQL area in the library cache to find if the query already exists or not.
• In case, the query is available there is no need to parse the same query and then checks the data dictionary cache. whether the related table info is there, then there is no need to get the privileges or definitions of the related tables
• If these data or not in the library cache and data dictionary cache that are returned from the disc and written into these areas.
• Once this data is ready, first it checks the query is true or not because if it is a valid query, then it checks the buffer cache for the related data and undo segments.

Oracle DML statement processing

• As we know that, if you do a DML operation the related data is written to the buffer cache first. This is valid both for querying and running a DML operation.

Dirty Blocks:

• If the data that needs to be modified is not in the buffer cache, the server fetches these data from the discs and writes them to the buffer cache.
• After ensuring that the related data is in the buffer cache, it locks the related blocks for the other users until the transaction completes i.e either it should be a commit or rollback no one else can’t change these blocks.
• All of our changes are written into the related blocks in the buffer cache these are called as “dirty blocks”.

Write Ahead Logging:

• meanwhile, all the related redo log data is written into the redo log buffer before changes are done in the buffer cache. it is called “Write ahead logging”.
• Here redo log data is created before the modification for the data consistency Because the actual data is more important than data changes. the number of affected rows to the user. But the user can continue modifying the data in its transaction.
• If the new modifications are on the same blocks, they are done in the buffer cache.
• If new data is needed, they are also fetched from the discs and the same process is done for the new ones.

System Change Number(SCN):

• Once the user commits the changes, the following steps are executed respectively. As we remember, the redo log buffer assigns a system change number(SCN) to the changes while writing them to the redo log files.
• A system change number is a unique number including the timestamp of that date. So, the server creates a commit record with the current system change number in the redo log buffer.
• Now, the log writer process starts writing all the redo log buffer entries created up to now about the related operations including the just created commit record to the redo log files and then deletes them from the redo log buffer.
• Then, the database writer process writes the dirty blocks to the disc and the server unlocks the related blocks for the other users.

Conclusion:

• In this blog, we understand how the update query is processed in the oracle database.
• After completing all these, the server returns feedback to the user about the completion of the transaction. So when the commit occurs successfully, your transaction finishes and you start another transaction