Database Reference
In-Depth Information
Excessive Commits
Executing a COMMIT statement triggers an action to the LGWR (Log writer) background process to write the contents
of the log buffer to the log file members. After completion of writing to log file members, LGWR posts waiting foreground
(server) processes, marking a successful commit. While the LGWR is performing a log flush, a foreground process
accounts wait time to a log file sync wait event. In a single-instance database, excessive commits can overload LGWR.
In a RAC database, CURRENT mode block transfers (and some Consistent Read mode block transfers, too)
require log flush, a scheme similar to commit processing. LMS processes must wait for LGWR process to complete log
flush before sending blocks to remote instance processes. The LMS process is instrumented to account the wait time
for the
gcs log flush sync
event.
The requirement for a log flush is due to the consistency mechanism in the database. Let me explain what
would happen if the log flush is not performed for a CURRENT mode transfer. Consider a scenario in which session
#1 updated a block in a PROD1 instance but has not committed the changes yet. Session #2 connecting to PROD2
is requesting to update the same block. So, the LMS process will transfer the block from the PROD1 instance to the
PROD2 instance in CURRENT mode. Assume the PROD1 instance crashes immediately after the block is transferred
to PROD2. Session #1 in PROD1 has a pending transaction that must be rolled back, but the log flush did not happen,
so the transaction changes are not recorded permanently in the redo log file. Changes made by session #1 would have
been transient and discarded if this is a single-instance database. But the block is transferred to the PROD2 buffer
cache with the session #1 changes. Essentially, uncommitted changes are permanent in the block, leading to a block
corruption. That's the reason the current mode block transfer requiring a log flush. So, if a block has a change SCN
higher than the current commit SCN, then a log flush is triggered before a block is transferred to another node.
A CR block fabrication requires redo change vectors from undo records to be applied to roll back the changes,
to create a block consistent with the requested block version. Applying change vectors causes redo generation, and
therefore redo records are copied into a log buffer. Hence, if the LMS process generated a redo to construct a CR block
copy, then that CR block transfer would require a log flush. However, an underscore parameter controls this behavior,
and there is absolutely no reason to consider the underscore parameter as that parameter change can cause data
consistency issues.
Delay in LGWR processing can induce delays in LMS processes, leading to a longer wait time for global cache
events in other nodes. Other processes trying to access the block in transit will wait for gc buffer busy wait events,
leading to a multilayered performance issue.
■
performance issues in log file parallel writes can induce massive waits in lMs processing, too. global cache
transfers can be stuck if lgWr is not able to complete log flush faster. lMs processes will be accumulating higher wait
time for the gcs log flush sync wait event. it is critically important for lgWr and log file writes to be highly efficient to
maintain faster global cache transfers.
Note
There is another inherent danger with excessive commits, which is a phenomenon known as
commit cleanout
.
When a transaction commits, not all transactions clean the entries in the Interested Transactions List (ITL) section
of a block header. Some transactions do not clean up ITL entries.
5
A subsequent session visiting that data block must
clean up the ITL entry by visiting an undo header block to determine the transaction status. In a RAC database, an
original transaction might have been initiated in another instance, so commit cleanouts can trigger undo header
blocks to be transferred from another instance. As undo header blocks and undo blocks are usually hot blocks,
excessive transfer leads to higher global cache workload and downstream performance issues.
Our recommendation is to avoid excessive commits. Avoid autocommit features in various languages. Commit
only on logical transaction boundaries.
5
There is no danger to transaction integrity because the transaction table in the undo header block maintains the true status of
a transaction.
