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).
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
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
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: