Shulou(Shulou.com)06/01 Report--

This article introduces you how to explain Oracle Latch, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

Oracle Latch description

one。 Latch description

1.1 Latch

In the fourth part of a previous article, Latch is explained, refer to:

Lock deadlock blocking Latch waiting for detailed explanation

Http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5822674.aspx

Latch belongs to System Lock, a serialized locking mechanism used to protect shared data structures in the SGA zone. The implementation of Latch is related to the operating system, especially whether a process needs to wait for a latch and how long it needs to wait.

Latch is a lightweight lock resource provided by Oracle, which can be acquired and released very quickly. It can lock resources quickly and in a short time, and prevent multiple concurrent processes from modifying and accessing a shared resource at the same time. It only works in SGA and is usually used to protect the data structure describing block in buffer cache.

For example, in SGA, all kinds of data are repeatedly read from disk to memory and then rewritten back to disk. If concurrent users do the same thing, Oracle must use a mechanism to ensure that data can only be read by one session. This protection mechanism is Latch.

Concurrency: means that more than two users make changes to the same data (which may include inserts, deletions, and modifications).

Parallel: it means to divide a thing into many small parts, let each part be executed at the same time, and finally summarize the execution results into the final result.

There is also a cleanup process associated with each latch that is called when the process that holds the latch becomes a dead process. Latch also has a related level to prevent deadlocks, and once a process gets a latch at a certain level, it is no longer possible to get latch at or below that level.

Latch does not cause blocking, it only causes waiting. Blocking is a problem of system design, and waiting is a problem of contention of system resources.

1.2 description of SPin:

For example, if a block in the data cache is to be read, we will get the latch of this block. This process is called spin. Another process happens to modify this block, and he also wants to spin this block. At this time, he must wait for the current process to release latch before it can spin live, and then modify it. If multiple processes request at the same time, there will be competition between them, without a queue mechanism, once the previous process releases the set. The rest of the process flocked, there was no first-come-first-come concept, and it all happened very quickly, because Latch is characterized by being fast and short.

SPIN and hibernation:

Hibernation means temporarily abandoning CPU and performing context switching (context switch), so that CPU saves some state information of the current process when it is running, such as stack, semaphore and other data structures, and then introduces the state information of subsequent processes, and then switches back to the original process state after processing. If this process occurs frequently in a processing system with high transactions and high concurrent processes, it will be a very expensive resource consumption. So Oracle chooses spin to let the process continue to occupy the CPU, run some empty instructions, then continue the request, continue the spin until the value of _ spin_ count is reached, and then abandon the CPU, sleep for a short time, and then continue the previous action.

1.3 the process of obtaining Latch:

At any time, only one process can access a block of data in memory. If the process cannot get the Latch because another process is occupying the block, it will spin the CPU for a very short time, and the spin will continue to be obtained, but will remain spin unsuccessfully until the number of spin reaches the threshold limit (this is specified by the implicit parameter _ spin_count). At this time, the process will stop spin and sleep for a short time. After hibernation, the previous action continues until the Latch on the block is obtained.

There is also an algorithm for the dormancy time of the process, which increases with the number of spin, in centiseconds, such as 1, 1, 2, 2, 4, 4, 8, and 8. The threshold limit of sleep is controlled by the implicit parameter _ max_exponential_sleep. The default is 2 seconds. If the current process already occupies another Latch, its sleep time will not be too long (too long will cause other processes to wait for Latch). At this time, the maximum sleep time is determined by the implicit parameter _ max_sleep_holding_latch. The default is 4 seconds. This time-limited dormancy is also known as short-term waiting.

Another case is waiting for the latch (Latch Wait Posting) for a long time, at this time, waiting for the process to request Latch is not successful, go into hibernation, he will press a signal to the latch waiting list (Latch Wait List) to indicate the request to obtain Latch, when the occupying process releases Latch, it will check Latch Wait List, and send a signal to the requested process to activate the dormant process. Latch Wait List is a list of processes maintained in the SGA zone, and it also needs Latch to keep it running, which is used by share pool latch and library cache latch by default.

If the implicit parameter _ latch_wait_posting is set to 2, all Latch adopt this wait mode, which can wake up a waiting process more accurately, but maintaining Latch Wait List requires system resources, and there may be bottlenecks in the competition for Latch on Latch Wait List.

If a process requests, rotates, and hibernates the Latch for a long time, it will notify the PMON process to see if the occupied process of the Latch has been unexpectedly terminated or dead, and if so, PMON will clear the Latch resources occupied by the release.

In short, the process of Latch acquisition: request-SPIN- hibernation-request-SPIN- hibernation. Occupied.

1.4 Latch and Lock

In a sense, Latch is a resource lock in memory, and a lock on database objects (tables, indexes, etc.) is called Lock.

The difference between Latch and Lock:

(1)。 Latch is a mechanism that provides mutually exclusive access to memory data structures, while Lock acquires shared resource objects in different modes, and there is compatibility or exclusion among different schemas. From this point, Latch access, including queries, is also mutually exclusive. At any time, only one process can pin a certain piece of memory. Fortunately, this process is quite short, otherwise the system performance will not be guaranteed, starting from 9i. Allows multiple processes to query the same memory block at the same time.

(2)。 Latch acts only in memory and can only be accessed by the current instance, while Lock acts on database objects, allowing Lock detection and access between instances in the RAC architecture

(3)。 Latch is an instant occupation and release. The release of Lock needs to wait until the transaction ends correctly. The length of time it takes is determined by the size of the transaction.

(4)。 Latch is not on the team, while Lock is on the team.

(5)。 There is no deadlock in Latch, but it exists in Lock.

For information about lock locks, please refer to my blog:

Oracle locking mechanism

Http://blog.csdn.net/tianlesoftware/archive/2009/10/20/4696896.aspx

Solving the problem of oracle Lock

Http://blog.csdn.net/tianlesoftware/archive/2009/10/28/4733630.aspx

two。 Latch contention

If you find that Lock often causes users to wait in the system, you need to consider whether there are problems in the logical design of the system, such as multi-user deletion or modification of primary keys, whether users use select. Syntax like for update is a factor in whether foreign keys create indexes or not. These factors need to be combined with the business logic of the system to design database objects.

If it is found that the system is slow because of a lot of Latch contention, it is necessary to consider whether there are problems in the design of the system and database itself, such as whether to use bound variables, whether there is thermal fast, whether the design of data storage parameters is reasonable and so on.

There are many reasons for waiting for Latch contention, and there may be contention for many resources in memory. 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.

Detailed description of Oracle memory architecture

Http://blog.csdn.net/tianlesoftware/archive/2010/05/16/5594080.aspx

2.1 Latch contention in shared pool

If there is a large amount of SQL in the shared pool that is repeatedly analyzed, it will cause a lot of Latch contention and long waiting. the most common phenomenon is that there are no bound variables.

The most common Latch in a centralized shared pool is library cache. You can query it through SQL:

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

220 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 bound variables or some stored procedures are repeatedly analyzed.

Contention for resources can be viewed through the following SQL:

SQL > select event,count (*) from 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

Gcs remote message 1

Ges remote message 1

Jobq slave wait 1

Rdbms ipc message 14

Smon timer 1

Pmon timer 1

Streams AQ: qmn slave idle wait 1

Class slave wait 1

SQL*Net message to client 1

Streams AQ: waiting for time management or cleanup tasks 1

Streams AQ: qmn coordinator idle wait 1

DIAG idle wait 1

15 rows selected.

2.2 data buffer pool Latch contention

Frequently accessed data blocks are called Hot Block. When many users access certain data blocks together, it will lead to some Latch contention. The most common latch contention is:

(1) buffer busy waits

(2) cache buffer chain

The contention between the two Latch occurs at different times when the data block is accessed.

For a description of waiting events, please refer to:

33 common wait events in Oracle

Http://blog.csdn.net/tianlesoftware/archive/2010/08/12/5807800.aspx

The cause of Cache buffer chian:

When a session needs to access a memory block, it first goes to a structure like a linked list to search whether the data block is in memory. When the session accesses the linked list, it needs to get a Latch. If the acquisition fails, Latch cache buffer chain wait will occur. The reason for this wait is that there are too many sessions accessing the same data block or the list is too long (if you read too much data in memory. The hash list that needs to manage the data block will be very long, so the session scanning list time will increase, the chache buffer chain latch holding time will be longer, the chances of other sessions getting this Latch will be reduced, and the wait will increase.

The cause of Buffer busy waits:

When a session needs to access a block of data that is being read from disk to memory by another user or is being modified by another session, the current session needs to wait, resulting in a buffer busy waits wait.

The direct cause of these Latch contention is the hot fast problem caused by too many sessions accessing the same data block, which may be caused by database settings or repeated SQL frequently accessing some of the same data blocks.

Latch is a simple, low-level serialization technology that protects shared data structures in SGA, such as concurrent user lists and blocks information in buffer cache. A server process or background process must obtain the corresponding latch before starting to operate or looking for a shared data structure, and release the latch after completion. There is no need to optimize the latch itself, and if there is competition in the latch, it indicates that part of the SGA is experiencing abnormal resource use.

For more information on this section, please refer to:

Lock deadlock blocking Latch waiting for detailed explanation

Http://blog.csdn.net/tianlesoftware/archive/2010/08/20/5822674.aspx

three。 Check the relevant SQL of Latch

3.1View the hot fast that caused the LATCH BUFFER CACHE CHAINS waiting event

SELECTDISTINCTa.owner,a.segment_name

FROMdba_extentsa

(SELECTdbarfil,dbablk

FROMx$bh

WHEREhladdrIN (SELECTaddr

FROM (SELECTaddr

FROMv$latch_children

ORDERBYsleepsDESC)

WHEREROWNUM

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.