Database Reference
In-Depth Information
There are a few issues with this type of application design:
1.
It is typical of these SQL statements to use index-based access paths. As many processes
will be concurrently accessing these index blocks, contention will be centered at fewer
index blocks.
2.
Also, concurrent processes are interested in recently inserted rows and thus contention
will be concentrated at a few table/index blocks.
3.
Contention is magnified because cardinality of these indexed columns, such as status
column, is very low. Therefore, concurrent worker processes compete against each other,
accessing the same block aggressively. In a single-instance database, excessive localized
access leads to waits for various events such as buffer busy waits, ITL contention, and so on.
In a RAC database, worker processes might be connected to all instances. The data blocks of
table and indexes are transferred between instances, thereby causing severe performance
issues. Numerous waits for events such as gc buffer busy acquire or gc buffer busy release
will be visible in the database during the occurrence of this performance issue.
4.
If the block is busy undergoing changes, then the global cache block transfer can be
delayed, resulting in waits for global cache events in other instances. This delay is
controlled by the _gc_defer_time parameter, which defaults to 3 centiseconds.
5.
Also, because the rows are locked for update, ITL entries in the block header will show an
active transaction. Other processes accessing those blocks must determine the transaction
status by accessing the transaction table in the undo header block. Because the original
transaction might have been initiated in another instance, the undo header and undo blocks
from other instances might need to be transferred. This excessive block transfer leads to
complex performance issues.
Therefore, performance problems can be encountered in many layers such as contention in table blocks, index
blocks, undo header, and undo block. There are few options to resolve this design issue:
1.
If the application performs an excessive amount of localized access, then you should
consider applying affinity and keep all workers in the same instance. While this reduces
the severity of the issue, it does not completely resolve the issue.
2.
Redesign application tables to use hash-partitioned tables and hash-partitioned indexes.
Modify the query and execution plan to access local hash partitions.
3.
Use the Advanced Queuing feature with multiple queues such that the worker processes
read a smaller set of data.
4.
Design application so that one process assigns work items to the workers. Worker
processes will perform a work item and update the row to completion status.
Small Table Updates
Frequently updated smaller tables can induce block contention. An example is an application that uses a table to keep
track of maximum column value instead of using a sequence. The following pseudocode explains this coding practice.
The maximum value of the employee_id column is tracked in the emp_seq_table. The query retrieves the max_employee
column value, inserts rows into employees table with that value, and then updates the max_employee column, adding
one to the value. If this code is executed aggressively, then there can be excessive contention on the emp_seq_table:
--Retrieve maximum value;
Select max_employee_id into v_employee_id
from emp_seq_table where column_name='EMPLOYEE_ID';
