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 is the first of a series or articles for Toad World in which I’ll discuss the various forms of Oracle contention, how this contention arises and how we can avoid or cure contention.
Contention – the proverbial bottleneck
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.
Figure 1 Contention - the proverbial bottleneck
Figure 1 illustrates this phenomenon. While contention reduces the amount of work that the application can do, it also masks the amount of load at other levels of the database – typically the IO subsystem. For this reason, it’s normally best to deal with contention only after you’ve addressed application demand issues (e.g, tuned your SQL and PL/SQL) and before you try and configure your IO subsystem (see http://guyharrison.typepad.com/SystematicOracletuning.pdf for a discussion of how contention fits into a structured tuning methodology).
Contention in Oracle
Theoretically, all computer resources are subject to contention: CPU, network, disk and memory. However, in this series, I’m going to concentrate on contention that is specific to the Oracle database.
In a healthy database, sessions spend most of their time either performing operations on the CPU or performing IO operations. CPU operations might include parsing SQL statements, reading data from the SGA or executing PLSQL code. IO mainly involves reading data from disk (writes to disk are handled by Oracle background processes).
However, from time to time, an Oracle session will need to wait for a resource to become available or for further instructions. When an Oracle session needs to wait, it records the wait in the “wait interface”: as exposed to us by various system tables such as v$system_event.
Not all of these waits are contention; some simply indicate that Oracle is waiting for instructions – for instance waiting for a SQL statement to be issued by the calling application. These waits can be considered “idle” waits and are identified as such by the wait_class column of the v$event_name table.
When an Oracle session is not in a wait state it usually be executing program code and therefore consuming CPU resources. From 10g onwards, we can accurately measure this CPU consumption by looking at the “time model” data – as shown in tables such as v$sys_time_model.
Figure 2 shows how we can join the data from these three tables to get a breakdown of overall database time. In a healthy database, almost all the time should be spent in IO and CPU operations – as is the case in Figure 2.
Figure 2 Getting a breakdown of Oracle session active time
However, in a database suffering from severe contention, we may see excessive wait time consumed in other categories. For instance, Figure 3 we see a huge amount of time has been spent waiting for “buffer busy waits” and also waiting for redo logs that need archiving.
Figure 3 Database suffering from severe buffer busy wait contention
Using spotlight to examine waits and contention
It takes a fair amount of experience to interpret the wait data generated by Oracle. For instance in 10g, there are 813 non-idle wait categories defined! Spotlight on Oracle – now included within the Toad DBA suite – interprets real-time wait information, as well as many other Oracle indicators – to determine the exact cause of an abnormal wait condition. For instance, Figure 4 shows how spotlight associates the buffer busy waits with the buffer cache while other problems are associated with the redo logs, shared servers and so on.
Figure 4 Spotlight on Oracle home page
Spotlight features a rich set of detailed drilldowns that help you address specific issues. For instance, there is a specific drilldown to help you establish the root cause of the buffer busy problem shown above. Spotlight also lets you establish the sessions and SQLs associated with any specific event – in Figure 5 we identify the specific SQL associated with the buffer busy problem shown in Figure 4
Figure 5 Spotlight wait analysis page
Types of contention
In subsequent articles, I’ll be covering specific contention scenarios, how they arise and what you can do about them. Here’s a high level summary of the major categories of contention, each of which we’ll discuss in more detail in a subsequent article:
| Locks |
Locks exist to prevent two sessions from changing the same table data in a way that could cause corruption or violate transactional integrity. Most lock contention problems stem from application design issues, although you can also sometimes see internal lock contention on data dictionary tables. |
| Latches |
Latches are similar to locks, but protect data in shared memory rather than data in database files. Latch contention can occur for a variety of reasons some of which relate to database configuration, but mostly result from very high logical read rates, excessive SQL statement parsing or “hot” blocks. |
| Buffer busy |
Buffer busy waits occur when an Oracle session wants to access a block but it is currently being accessed by another session in an incompatible mode. For tablespaces not using ASSM (Automatic segment space management) this most often occurs when multiple inserts are being attempted on a table with too few freelists (as in the example shown in Figure 5). |
| Free buffers |
The database writer (DBWR), Recovery Writer (RVRW) and Redo log writer (LGWR) are all responsible for flushing changed or “dirty” blocks out to disk. If these processes cannot keep up with demand, then various buffers may become full of dirty blocks and slow down DML. The solution is usually to increase the write IO bandwidth to the database files, flashback logs or redo/archive logs. |
| Sequences |
Many applications use Oracle sequences to generate non-blocking unique keys. While sequences are usually the fastest way of generating keys, high transaction volumes can still cause contention. |
| Redo/Archive log |
The flow of “redo” information from redo log buffer to redo log and eventually to archived log is critical to the recoverability of most mission critical databases. Redo logs cannot be re-used until archived and all relevant transactions checkpointed. Furthermore, each log switch creates an interruption in transaction processing. Sessions wishing to commit transactions during some of these operations may be delayed. |
| Shared servers |
Some databases are configured so that sessions share server processes (MTS or Multi Threaded Server mode). Also, sessions that perform Parallel operations and background jobs draw on a pool of servers. If there are insufficient free servers to meet demand then these sessions or jobs will experience contention. |
| Global Cache |
RAC (Real Application Clusters) introduces a new dimension of contention in which multiple instances of Oracle work together to create a single logical database. These instances share a common disk subsystem and maintain a global cache that must be consistent. Many contention points – especially those relating to “hot” blocks – can be magnified in a RAC environment. |