Database Reference
In-Depth Information
Long Pending Transactions
Long pending transactions on critical tables can trigger massive amounts of undo construction, too. In an Oracle
database, a SQL statement cannot see changes from uncommitted transactions created by another session. SQL
statements create a read-consistent view of a block
6
by applying undo records from uncommitted transactions.
Further, every session accessing the block must build a read-consistent view of the block.
In a single-instance database, transactions are initiated only in a local instance, so the undo application is the
only additional workload. In a RAC database, transactions can be initiated in any instance, so the undo header blocks
and undo blocks must be transferred between the instances if the data block is accessed in another instance. This
transfer will result in an additional global cache workload. If there is a long pending transaction on a heavily accessed
table, this problem can quickly escalate to a cluster hung issue.
Further, blocks may not be transferred immediately to another instance if the block is considered busy. A delay in
gc transfer can induce further waits for the gc buffer busy event.
Our recommendations are as follows:
1.
Defer long, intrusive transactions on critical tables for a low-activity period. This strategy
will reduce the severity of the issue in a RAC database.
2.
Update only required columns. Developers write code to delete and reinsert rows instead
of updates. Not only is this delete + insert strategy unnecessary, it is redo intensive,
which results in a higher number of undo records to be applied for read- consistent
block fabrication.
Localized Access
Small sets of blocks accessed from all instances can cause contention-related performance issues. It is a common
requirement in many applications to query a request table to identify work items to be processed. For example,
manufacturing applications typically query a request table to identify the next work item to process. After processing
the work item, the row is updated, marking the work item with a completion status. Typically, these work items are
picked up by many such concurrent processes, resulting in concurrent read and write on a few blocks of tables and
indexes. The concurrent processing layer in an E-Business suite application also uses a similar strategy to execute the
next concurrent request.
The performance issue is exacerbated as new rows are inserted into the table concurrently, too. The following
pseudocode explains the application code. Worker programs constantly query the request_table to identify new work
items to process. At the end of the processing, rows are updated, marking the work item to a completion status.
Loop
Select request_id into v_req from request_table Where status='P'
Order by request_id
For update of request_id skip locked;
-- Process row
-- update row mark to C
Update request_table set status='C' where request_id=v_req;
End loop;
6
A new feature, _undo_cr, allows construction of a read-consistent row, but it is applicable only to special cases such as accessing
a row through a unique index, and so on.
