- A Brief History of Oracle Performance Tuning
- Moving Beyond a Symptomatic Approach
- Stage 1: Minimizing the Application Workload
- Stage 2: Reducing Contention and Bottlenecks
- Stage 3: Reducing Physical IO
- Stage 4: Optimizing Disk IO
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Stage 2: Reducing Contention and Bottlenecks
After we adjust the application workload demand to a sensible minimum, we are ready to tackle contention within the Oracle server. Contention occurs when two or more sessions want simultaneous access to a resource, such as a lock or memory buffer.
When the application demand hits the database, contention—the proverbial "bottleneck"—limits the amount of work that can be done. From the applications perspective, the database appears slow or stalled. At lower levels—the disk subsystem, for instance—the demand appears to be lower than it really is. The contention bottleneck prevents the demand from getting through the database code into the IO subsystem. Figure 1-2 illustrates the phenomenon.
)
Figure 1-2 Contention is the proverbial bottleneck.
The two most prevalent forms of contention observed in Oracle-based applications are contention for rows within tables—generally showing up as waits for locks—and contention for areas of shared memory—showing up as waits for latches, memory buffers, and so on.
Lock contention is largely a factor of application design: Oracle's locking model allows for high concurrency because readers never wait for locks, writers never wait for readers, and locks are applied at the row level only. Typically, lock contention is caused by an application design that involves high simultaneous updates against a single row or in which locks are held for an excessive length of time, perhaps due to a pessimistic locking model. This sort of contention is almost impossible to eliminate without application logic changes—changes that we address in the first stage of tuning. However, there are scenarios in which excessive lock contention is caused by database or schema configuration problems, or by Oracle internal mechanisms.
Contention for shared memory occurs when sessions want to read or write to shared memory in the SGA concurrently. All shared memory is protected by latches (or mutexes), which are similar to locks except that they prevent concurrent access to data in shared memory rather than data in tables. If a session needs to modify some data in memory, it acquires the relevant latch, or mutex, and if another session wants to read or modify the same data, a latch, or mutex, wait might occur. Contention for data blocks in the buffer cache can occur for other reasons as well: A variety of buffer waits can occur when a block of memory is unavailable due to conflicting processing demands between sessions.
Chapters 15–17 address the techniques for eliminating Oracle contention. Specifically
- Detecting and dealing with lock contention, including Oracle internal locks
- Optimizing Oracle latching mechanisms that protect Oracle shared memory
- Identifying and correcting contention for shared memory itself