In Oracle performance tuning circles, we often talk about reducing contention. But it’s easy to lose sight of what contention really is, how it’s caused and how to avoid it. This section contains a series of articles in which I’ll discuss the various forms of Oracle contention, how this contention arises and how we can avoid or cure contention.
-Guy
May 2008: Reducing Oracle Network Contention
Overview:
It’s usual for your Oracle client – be it an application server, windows client or even humble old SQL*Plus – to be running on a computer other than the server hosting the database. Obviously, in those circumstances, Oracle must transfer data between the client and the database server. Occasionally, this network traffic can in itself become a bottleneck.
So, to reduce network contention we need to either improve the bandwidth of the network or reduce the number of packets sent across the network. In this month's article, Guy discusses how to reduce packet transmissions in Oracle.
|
Overview:
A properly configured buffer cache can be the key to avoiding a disk-bound database and delivering peak database performance. However, contention for blocks in the data cache can prevent the buffer cache from reaching it's full potential..
This article examines how contention within the buffer cache can reduce the effectiveness of caching. Reading from the buffer cache involves latch operations, and ( as discussed in March's article ) how latch contention can result. This month, Guy examines other forms of contention within the cache and provides guidelines to avoid buffer cache contention.
|
Overview:
Latches protect areas of Oracle shared memory from concurrent access in roughly the same way that locks protect data in tables. When a session wants a latch it will repeatedly attempt to obtain the latch until reaching the value of "_spin_count" after which it will sleep and a "latch free" wait will occur. Excessive latch sleeps can create restrictions on throughput and response time.
The two most frequently encountered forms of latch contention in modern Oracle (10g/11g) are:
- Library cache/shared pool latch contention. This is usually caused when an application issues high volumes of SQL which are non-sharable due to an absence of bind variables. The CURSOR_SHARING parameter can often be used to alleviate this form of contention.
- cache buffer chains contention. This is usually associated with very high logical read rates and "hot" blocks within the database (sometimes index blocks). After tuning SQL to reduce logical IO and eliminate repetitive reads of the same information, partitioning is often a possible solution.
If latch contention is causing serious problems, and the system has some free CPU capacity, adjusting the value of the undocumented parameter _spin_count may be effective in reducing contention. As always, modifying undocumented parameters should be approached with great caution.
|
February 2008: Dealing with Oracle Lock Contention
Overview:
Oracle provides a highly efficient row level locking capability that maximizes transaction concurrency. Most of the time, lock contention is an inevitable consequence of application locking strategies. Each application is different, but the most significant and widely applicatable design pattern you can employ to minimize locking is the optimistic locking strategy.
Certain scenarios can require that Oracle apply locks above the row level of granularity. These include:
- Unindexed foreign keys
- Bitmap indexes
- Inability to grow the Interested Transaction List (ITL)
Oracle sometimes applies internal locks and – in some circumstances – these can cause contention at the application level. Two examples of problematic internal locking are:
- Space Transaction (ST) locks in dictionary managed tablespaces
- Row cache locks caused by sequences with low CACHE settings.
|
January 2008: Contention the Proverbial Bottleneck
Excerpt:
Contention occurs when two or more Oracle sessions wish to use a resource but are prevented from doing so due to some form of limitation on that resource. Usually these limitations relate to concurrency – a limitation on how many sessions can use the resource at the same time. Sessions contend for the use of the resource and at least one of the sessions suffers a processing delay while they wait for the resource to become available.
Contention has a two fold effect on our perception of performance:
- Contention reduces the amount of work that the database can get done. So lock contention – for instance – might reduce the number of SQL statements that are executed per second.
- Contention therefore reduces the amount of load on other parts of the system. So contention for locks might actually reduce the amount of IO demand. When we eliminate the lock contention the load on the disks might actually increase.
...
|