Tài liệu ORACLE8i- P21 pdf

T

his is the last piece of the tuning trilogy that began with Chapter 15. In this

chapter we will look at latches, locks, and waits—mechanisms in Oracle

database processing that can all cause significant execution delays. Users’

sessions can start to “pile up,” waiting for an available resource. If you can

proactively monitor the database and know when and where a problem is occurring,

you’ll have an opportunity to tune these mechanisms before users even know any￾thing is amiss.

We’ll start with a discussion of locking, latching, and waiting and how they affect

the database. When you understand the purpose and effects of these events, you can

more easily identify the problems that are most closely associated with them. From

there, we’ll cover monitoring these mechanisms and tuning them based on the results.

Latches and Locks

Much of the memory that Oracle allocates to the database process is shared mem￾ory—shared among all of Oracle’s processes for both reading and writing. In many

cases, Oracle needs to limit access to a particular area of memory to just one process.

Limiting access to blocks and other memory structures helps ensure that other

processes do not interfere in the ongoing operations of other sessions. Also, because

of the concurrency and consistency requirements of the database, Oracle must be able

to restrict access to user objects such as tables, clusters, partitions, and the data in the

rows in those objects.

Two Oracle mechanisms facilitate some form of restricted access to memory blocks:

the latch and the lock (also known as an enqueue). Although very different in their

implementation of access control, latches and locks have something else in common:

They both can be symptoms of database performance problems. Much as fever and

coughing are symptoms of the flu, the behavior of latches and locks can help you

diagnose underlying problems in database operations. By monitoring various statis￾tics related to locks and latches, you can improve the effectiveness of your tuning

efforts on the database and on SQL statements.

What’s a Latch?

A latch is an elemental locking mechanism used by Oracle to serialize access to vari￾ous memory structures. Oracle uses latches on structures that are expected to remain

unlocked for a long period of time. Latches protect memory structures and processes

such as redo-log buffer copy operations, the structures that manage the buffers in the

database buffer cache, and other operations that interact with the SGA.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

Oracle uses many different kinds of latches, and a given process may use more

than one latch. In fact, more than one latch may be used for a single operation of a

process. For example, when an Oracle process needs to write to the redo log buffer,

one or two latches are used to control that access, depending on the size of the redo

to be written.

Latches are low-level locking mechanisms and are more restrictive than most of

the locks used by Oracle. Latching is an atomic operation. In fact, locks use latches as

part of the overall locking mechanism. With a few exceptions, latches restrict any kind

of access (read or write) to the memory area to the process that is holding the latch.

Latch Contention

So, what happens during the life of a process waiting to acquire a latch? Getting a

latch is like winning the Cannonball Run—the first one to the latch wins. More

specifically, the first process to request the latch acquires the latch. Once a latch is

acquired by a process, other processes wait based on the mode in which the latch was

requested. These modes are willing-to-wait mode and no-wait mode.

Willing-to-Wait Latches

If a process acquires a latch in willing-to-wait mode, other processes requesting that

latch with a willing-to-wait request will stack up waiting for the latch to become

available to them. These waiting processes go through two distinct operations while

waiting: a spin operation, which involves continual attempts to reacquire the latch

over and over again. If the subsequent latch get attempts are not successful after a

fixed period of spinning, the process enters a sleep operation.

The waiting process’s spin operation is essentially a timed loop. The length of spin

time is controlled by the hidden parameter _SPIN_COUNT. Through each iteration of

the spin process Oracle checks again to see if the latch is available. Spin operations are

CPU intensive because the process is active, looping and doing very little of worth.

Still, spinning is much preferred to sleeping in most cases. Spinning represents intense

use of CPU resources, but sleeping generally has more negative ramifications on over￾all process performance. The reasons for this are discussed throughout this chapter.

Sleeping occurs when the requesting process fails to get the latch after coming out

of the spin operation (or, in some cases, after awakening from another sleep opera￾tion). The sleep operation is a suspension of the thread or process for a given amount

of time or until the sleeping process is awakened. Putting a process in sleep mode

works the CPU fairly hard because of context switching (or the cost of moving the

process and its related structures in and out of memory). When Oracle puts the process

to sleep, it also indicates how long the process should sleep. After this sleep operation

expires, the process awakens and attempts to acquire the latch. If that fails, the process

enters the sleep cycle again, and so on until the latch get is successful—or the power

LATCHES AND LOCKS 797

Beyond Simple

Database Management

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

798 CHAPTER 17 • MONITORING AND TUNING LATCHES, LOCKS, AND WAITS

company runs out of coal! As a waiting process moves from one sleep operation to the

next, the overall sleep time grows exponentially as Oracle increases the sleep cycle

duration.

All this can have an adverse effect on the performance of the process because

latches are not taken in any designated order. So when a process wakes, the latch it

wants may have already been acquired by another process. The process sleeps yet

again, unable to acquire the latch it wants. And with each sleep operation, the sleep

duration increases, sometimes taking far longer than it should have to get the latch.

One way to reduce the delay to a minimum is to raise the system’s _SPIN_COUNT

parameter so that processes spin for a longer time before sleeping. We’ll get into this

later in the sections on monitoring and tuning.

Latch Wait Posting

Another measure for controlling sleep operations is to use latch wait posting. When

latch wait posting is used, the process holding the latch will wake the sleeping process

that is waiting for the latch. This is a more expensive arrangement, but it can decrease

the overall time waiting to acquire a latch.

By default, only requests for the library cache and the shared pool latches use latch

wait posting. If you want to take advantage of latch wait posting for all latches, set

the hidden parameter _LATCH_WAIT_POSTING to 2. You can also disable this feature

completely by setting the parameter to 0.

TIP You can tell how many times a process has slept by looking for the Latch Free Wait

statistics in the V$SESSION_EVENT view, explained in the later section about Oracle waits.

Monitor latching very carefully if you choose to change the _LATCH_WAIT_

POSTING or the _SPIN_COUNT parameters, to make sure that the changes have a

positive impact. In particular, watch the V$LATCH and the V$SYSTEM_EVENT data

dictionary views for excessive latch contention. Look for significant wait times in the

latch free waits event in V$SYSTEM_EVENT. In both views, look for increased wait times

for latches in general. (You can see why having TIMED_STATISTICS turned on is so

important!)

When a willing-to-wait process uses latch wait posting, the process is still put to

sleep because it cannot acquire the required latch. Beforehand, however, the process

posts a request for the latch in the latch wait list, which notifies processes that cur￾rently own the latch that other processes are waiting. Based on the latch wait list, the

process acquiring the latch wakes up the sleeping process when the latch is freed.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

799

No-Wait Mode Latches

When a process takes a latch in no-wait mode, the process will not sleep or spin in

order to wait for a given latch. The process immediately tries to obtain the latch

again, without waiting, until it succeeds. Generally, no-wait latches are used in cases

where latch acquisition deadlocks between two or more processes might occur, and

other latches have already been acquired at the same or lower level. In this case, if

Oracle cannot service the process’s request, the latches already allocated are released

and the entire operation must be repeated. Very few latches use no-wait mode.

Parent/Child Latches

Oracle allows parent latches and child latches. Structures that require multiple latches,

such as the shared pool, will have a parent latch (shared pool latch) and one or more

child latches. Some parent latches have only one child (for example, the cache buffers

LRU chain, which has one child latch in the database we use) and some have many

children (for example, cache buffer chains, which has 512 in the database we use).

The use and number of parent/child latches vary among releases of Oracle.

Latch Levels

The V$LATCH view (discussed shortly) includes a LEVEL# column. Oracle assigns

level numbers to latches in order to avoid deadlocks. Frequently, an operation

requires acquisition of multiple latches, and multiple processes may attempt to

acquire the same latch. Deadlock problems are prevented by Oracle’s assignment of

an order for the acquisition of latches. The level numbers range from 0 to 15, and

Oracle acquires latches beginning at a lower level and proceeding to higher levels.

When a process attempts to get a latch, Oracle checks to make sure that a latch at the

same or higher level is not already being held.

Say that Processes 1 and 2 need Latches A and B. If Process 1 acquires Latch B first

and then tries to allocate Latch A, and Process 2 grabs A first and then tries to acquire

B, you’d have a deadlock condition. The two processes are each trying to acquire a

latch already acquired by the other process. Oracle prevents this by means of its latch￾level architecture. Processes 1 and 2 will always try to grab a latch first, because it’s a

lower-level latch. If Process 1 grabs Latch A, Process 2 will spin or sleep until Process 1

releases latch A. Process 2 never tries to grab Latch B until it has acquired Latch A,

thus preventing a potential deadlock situation.

Important Latches for Monitoring

Oracle’s V$LATCH view comprises 152 different latch types (in Oracle 8.1.7). Which

ones are the significant latches that you should be keeping an eye on? The following

list of important latches is organized based on the parts of the SGA that are protected

by a particular latch group.

LATCHES AND LOCKS

Beyond Simple

Database Management

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com