Let us review two memory structures (part of SGA) to understand the commits in Oracle.
- Data buffer cache
- Redo log buffer cache
Database buffer cache
Data buffer cache contains copies of data blocks read from datafiles that can be modified by users. We are aware that anything in the memory buffer is not permanent, you loose the data in the memory if the database or system crashes. So typically user commits should force this buffer back to the database before returning the control back to user. However under normal circumstance, this is not how Oracle works. Let us look at redo log buffer before we completely understand the behavior of Oracle.
Redo log buffer
Recovery is a must for most of the applications and therefore Oracle or any RDBMS architecture should have mechanism in place to help recover your database from failures. Redo log buffer is a memory structure that aids in recovery of database. It records changes made to a block in a database and this change is generally referred to as redo entry . Typically redo entry contains
- Changes made to the data block (After Image)
- Undo segment data block (Before Image)
- Transaction table of the undo segments (Table that tracks the state of transaction).
Main idea behind recording this information is to recreate all changes made to the database during recovery.
Oracle uses this concept to its advantage to improve the performance of the database. So Whenever a user issues commit, the transaction is considered complete when the writes are made to online redo log files from the redo log buffer. In another words, instead of writing from database buffer cache to datafiles , Oracle writes from redo log buffer to online redo log files. The writes to datafiles from data buffer cache are generally made asynchronously.