Databases Reference
In-Depth Information
11
Tuning Contention
In this chapter, we will cover:
F
Detecting and preventing lock contention
F
Investigating transactions and concurrency
F
Tuning latches
F
Tuning resources to minimize latch contention
F
Minimizing latches using bind variables
Introduction
In this chapter we will focus on preventing, detecting, and tuning contention-related issues.
In a database, as in any information system, there is a limited amount of resources to be
shared among many users. When more than one user asks for a resource—which can be a file,
a row in a table, a block in the buffer cache—there is a need for a synchronization mechanism
to avoid the contemporary use of the resource itself by more than one user at a time.
In this chapter we will investigate two different synchronization mechanisms used in Oracle
databases: locks and latches.
Locks are used to regulate access to a shared resource, such as a row or a table, while latches
protect shared data structures and shared memory allocations in the System Global Area.
The main difference between locks and latches is that latches are held for a short time, while
the purpose of locks is to protect resources for a relatively long time. Another difference is
that locks involve a queuing mechanism and prioritization, whereas latches don't.
