Instructions for using Oracle latch

First, Latch description

1.1 Latch

Latch belongs to System lock, which is used to protect a serial locking mechanism of shared data structures in the SGA area. Latch implementations are related to the operating system, especially if a process needs to wait for a latch and how long to wait.

Latch is a lightweight lock resource provided by Oracle, a lock that can be acquired and released very quickly, locks resources quickly and easily, prevents multiple concurrent processes from simultaneously modifying access to a shared resource, works only in the SGA, and is typically used to protect the description buffer The data structure of block in cache.

For example, the SGA, a variety of data are repeatedly read from the disk to memory, and was back to disk, if the concurrent users do the same thing, Oracle must use a mechanism to ensure that the data in the reading, can only be completed by a session, this protection mechanism is latch.

Concurrency (concurrency): is said to have more than two users of the same data modification (may include insertion, deletion and modification).

Parallel (parallel): is to say that one thing is divided into a lot of small, so that each part of the simultaneous execution, and finally summed up the results of the implementation of the final results.

There is also a purge process associated with each latch, which is invoked when the process that holds the latch becomes a dead process. Latch also has the relevant level to prevent deadlocks, and once a process obtains a latch at a certain level, it is impossible to obtain latch equivalent or below that level.

Latch does not cause blocking, it only causes waiting. Congestion is a problem in system design, and waiting is a problem of system resource contention.

1.2 Description of the spin:

For example, a block in the data cache to be read, we will get the latch of the block, this process is called spin, another process is exactly to modify the block, he also want to spin this block, he must wait, the current process to release latch before spin live, and then modify, If multiple processes are requested at the same time, there will be competition between them, without a team mechanism, once the process is released, the following process is swarming, there is no arrival concept, and all this happens very quickly, because latch is fast and short.

SPIN and hibernation:

Hibernation means a temporary abandonment of the CPU, context switches, so that the CPU to save the current process of running some state information, such as stacks, semaphores and other data structures, and then introduce the following process status information, processing and then switch back to the original process state, This process, if frequently occurs in a high transaction, high concurrency process of the processing system, will be a very expensive resource consumption, so Oracle chose spin, let the process continue to occupy the CPU, run some empty instructions, then continue to request, continue spin until the _spin_count value, At this time will give up the CPU, for a short sleep, and then continue just the action.

1.3 Process to obtain the latch process:

More Wonderful content: http://www.bianceng.cn/database/Oracle/

At any time, only one process can access a block of data in memory, and if the process is unable to obtain latch because another process is occupying a block, he will spin (rotate) The CPU for a very short time, spin continue to acquire after the unsuccessful, still spin until Spin number of times the threshold limit is reached (this is specified by the implied parameter _spin_count), at which point the process stops spin, takes a short sleep, and after hibernation continues just the action until the latch on the block is obtained.

Process sleep time is also an algorithm, he will increase with the number of spin, in the amount of seconds, such as 1,1,2,2,4,4,8,8, ... The threshold limit for hibernation is controlled by the implied parameter _max_exponential_sleep, the default is 2 seconds, and if the current process has occupied other latch, his hibernation time is not too long (too long will cause the other process latch wait), at this time the maximum sleep time has hidden parameters _ Max_sleep_holding_latch decided that the default is 4 pct seconds. This time-limiting hibernation is also known as short wait.

Another situation is a long wait for the latch (Latch wait Posting), at this time waiting for the process request Latch unsuccessful, into hibernation, he will be waiting for the latch chain list (Latch wait List) to press a signal to obtain the Latch request, The latch wait List is checked when the process releases latch, passing a signal to the requesting process to activate the hibernation process. Latch wait list is a list of processes maintained in the SGA area, and he also needs Latch to ensure that it works, by default share pool Latch and library cache Latch are using this mechanism.

If you set the implied parameter _latch_wait_posting to 2, all latch Use this wait method, which enables you to more accurately wake up a waiting process, but maintaining latch wait list requires system resources and the latch There may also be bottlenecks in latch competition on the list.

If a process requests, rotates, and sleeps latch for a long time, he notifies the Pmon process to see if latch's occupancy process has terminated or died unexpectedly, and if so, Pmon clears the latch resource that is being freed.

In short, latch gets the process: request-spin-Hibernate-Request-spin-hibernate ... Occupied.

1.4 Latch and Lock

In a sense, latch is a resource lock in memory, and the lock of a Database object (table, index, etc.) is called.

The difference between latch and lock:

(1). Latch is a mechanism for providing mutually exclusive access to memory data structures, lock is a different mode to get the shared resource objects, there are compatibility or exclusion between the various modes, from this point of view, Latch access, including the query is mutually exclusive, at any time, only one process can pin the memory of a piece, Fortunately this process is quite short-lived, otherwise the system performance will not be guaranteed, starting from 9I, allows multiple processes to simultaneously query the same block of memory.

(2). Latch is only used in memory, he can only be accessed by the current instance, while lock acts on database objects, allowing lock detection and access between instances in the RAC system

(3). Latch is an instantaneous occupancy, release, lock release that needs to wait until the transaction ends correctly, and the length of time it takes is determined by the transaction size

(4). Latch is a team, and lock is on the team.

(5). Latch There is no deadlock, and lock exists.

Second, Latch contention

If you find that the system often causes users to wait because of lock, then you need to consider whether the system has a logical design problem, such as the deletion or modification of the primary key by multiple users, whether there is a user using a SELECT ... for update syntax, a foreign key is the factor that creates an index. These factors need to combine the business logic of the system to design the database object.

If the system is found to be slow because a lot of latch contention, it is necessary to consider the system and the database itself whether there are problems, such as whether the use of binding variables, whether there is a hot fast, data storage parameters are reasonable design and other factors.

There are many reasons to wait for latch contention, and many resources in memory may be competing. The two most common types of latch contention are as follows:

(1) Latch contention in the shared pool.

(2) Latch contention in the data buffer pool.

2.1 Latch contention in a shared pool

If a large number of SQL is repeatedly analyzed in the shared pool, it will cause a lot of latch contention and long waiting, the most common phenomenon is that there is no binding variable.

The most common latch in the pooled pool is the library cache. You can use SQL to query:

Sql> SELECT * from v$latchname where name like ' Library cache% ';

latch# NAME HASH

---------- -------------------------------------------------- ----------

217 Library Cache 3055961779

218 Library Cache Lock 916468430

219 Library Cache Pin 2802704141

The library cache pin allocation 4107073322

221 Library Cache Lock Allocation 3971284477

222 Library Cache Load Lock 2952162927

223 Library Cache Hash Chains 1130479025

When analyzing system performance, if you see latch contention such as library cache, you can conclude that there is a problem in the shared pool, which is basically caused by SQL statements, such as no binding variables or some stored procedures being repeatedly analyzed.

Contention for a resource can be viewed by using the following SQL:

Sql> Select Event,count (*) from the V$session_wait group by event;

EVENT COUNT (*)

---------------------------------------------------------------- ----------

Sql*ne tmessage from client 4

Streams aq:waiting for messages in the queue 1

ASM Background Timer 1