Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Committed for Update Transaction in the Oracle Database Architecture.

As per my experience, I'm explaining here about Committed to Update Transactions in the Oracle Database Architecture.

Step 1: When a user sends a request to update transaction in the table.

Step 2: User request will go to the listener process, then the listener process is chosen for hard parsing as well as choosing cost-based optimization. Based on the best execution plan, it will get to the library cache.

Step 3: If the library cache doesn't have the hash value of the query, then it will go to the data dictionary cache, generate metadata for update transactions from the base table, then create a hash(#) value, and place it in the library cache.

Step 4: Based on the hash (#) value, the server process passes the request to the database buffer cache.

Step 5: In the database buffer cache (DBBC). Here, the DB writer takes a copy of user-requested data (Update statement) from datafiles to the Database buffer cache, and one extra copy is kept in undo tablespace when the commit does not happen to roll back the transaction.

Step 6: When changes are done after, when issuing a "commit", all the change vectors, the server process will pass it redo log buffer cache, then the LG Writer will write change vectors from the redo log buffer cache to online redo log files.

   Change Vector= Block ID + Bit address is called change vector.  
      
    Note: When performing an update/insert transaction. If DDBC doesn't have sufficent buffer, then it will go to PGA. Still not sufficient buffer, then it will have temporary tablespace for sorting operations.  
      
   Change Vectors: In four possible scenarios, LGWR writes change vectors from redo log buffer cache to an online redo logfile.  
   a) every 3 seconds  
   b) when commit happens.  
   c) whenever log switch occurs.  
   d) whenever a check point occurred.

Step 7: Finally, the Archiver background process will write change vectors from online redo logfile to archive logfile for recovery.

Comments
Post Details
Added on Jun 17 2025
0 comments
88 views