metalink关于latch free的介绍.doc

文档 ID: 注释:34576.1主题: WAITEVENT: latch free Reference Note类型: REFERENCE状态: PUBLISHED内容类型: TEXT/X-HTML创建日期: 27-FEB-1996上次修订日期: 11-NOV-2002latch free Reference NoteThis is a reference note for the wait event latch free which includes the following subsections: Brief definition Individual wait details (eg: For waits seen in ) Systemwide wait details (eg: For waits seen in ) Reducing waits / wait times See for an introduction to Wait Events.Definition: Versions:7.0 - 9.2 Documentation: 9.0 Latches are like short duration locks that protect critical bits of code. This wait indicates that the process is waiting for a latch that is currently busy (held by another process). Individual Waits:Parameters: P1 = Latch address P2 = Latch number P3 = Tries Latch address The address of the latch that the process is waiting for. The hexadecimal value of P1 (P1RAW) can be used to determine which latch is waited for thus: SELECT name, Child |child#, gets, misses, sleeps FROM v$latch_children WHERE addr=&P1RAW UNION SELECT name, null, gets, misses, sleeps FROM v$latch WHERE addr=&P1RAW ;This will show Child N in the second column if the latch is a child latch. Latch number This is the latch number that indexes the V$LATCHNAME view: SELECT * FROM v$latchname WHERE latch# = &P2 ;Note that the latch number for a given set of latches can vary between releases / platforms so it is best to avoid hard coding P2 or LATCH# in SQL scripts. Tries This is basically a counter that counts the number of times we tried to get the latch (slow with spinning) and the process had to sleep. See the Wait Time notes below. Wait Time: When a session waits on latch free it effectively sleeps for a short time then re-tests the latch to see if it is free . If it still cannot be acquired then P3 is incremented and the session waits again. The wait time increases exponentially and does not include spinning on the latch (active waiting). The maximum wait time depends on the number of latches that the process is holding. There is an exponential back off (up to 2 seconds) between each attempt to get the latch. For certain latches a waiting session may be posted once the latch is free. Oracle9i uses this latch posting far more than Oracle8i (and earlier) releases. The SECONDS_IN_WAIT figure in shows the total time spent waiting for the latch including all sleeps. Systemwide Waits:As a latch wait is typically quite short it is possible to see a large number of latch waits which only account for a small percentage of time. If the TIME spent waiting for latches is significant then it is best to determine which latches are suffering from contention. Both STATSPACK and Bstat/estat reports include sections which show latch activity in the period sampled. These sections are based on (which gives a summary of latch activity since instance startup) and can give an indication of which latches are responsible for the most time spent waiting for latch free thus: SELECT latch#, name, gets, misses, sleeps FROM v$latch WHERE sleeps0 ORDER BY sleeps ; Note that this select gives the worst latches at the BOTTOM of the list.Some lines in this report are actually for multiple latches all of the same type. To determine if the latch activity is concentrated on one particular latch in the set one can query (only available from 7.3 onwards): SELECT addr, latch#, gets, misses, sleeps FROM v$latch_children WHERE sleeps0 and latch# = &LATCH_NUMBER_WANTED ORDER BY sleeps ;This gives the system wide number of waits for each child latch of the type LATCH#. If there are no rows returned then there is only a single latch of the type you are looking at. If there are multiple rows the important thing to note is whether the SLEEPS are reasonably distributed or if there are one or two child latches responsible for 80% of the SLEEPS. If the contention is focused on one or two child latches make a note of which children are seeing a problem - note the ADDR column. One can also look at: Does the same session/s keep appearing in Sessions with high latch waits in (Although it is important to note that innocent sessions may show high numbers of waits if some other session is repeatedly holding the latch) Reducing Waits / Wait times:There is no general advice to reduce latch waits as the action to take depends heavily on the latch type which is causing the waits. If there is no particular latch and waits occur across all latches then check for CPU starvation or uneven O/S scheduling policies - a CPU bound system will often show numerous latch waits across many types of latch. The latches most likely to show high sleeps are listed below along with some possible actions: shared pool latch Heavy use of literal SQL will stress this latch significantly. If your online application makes heavy use of literal SQL statements then converting these to use bind variables will give significant improvements in latch contention in this area. See for issues affecting the shared pool. library cache latches From Oracle 7.2 onwards the library cache latch has child latches . Problems with these latches are typically due to heavy use of literal SQL or very poor shared pool configuration. If your online application makes heavy use of literal SQL statements then converting these to use bind variables will give significant improvements in latch contention in this area. See for issues affecting the shared pool. cache buffers lru chain latch Setting to TRUE can adversely affect this latch - always ensure DB_BLOCK_LRU_STATISTICS is set to FALSE. From Oracle 7.3 it is possible to have multiple of these latches by specifying DB_BLOCK_LRU_LATCHES although this really needs multiple CPUs to be of most benefit. Heavy contention for this latch is generally due to heavy buffer cache activity which can be caused, for example, by: Repeatedly scanning large unselective indexes Oracle7/8.0 only: Sorting in buffer cache and not using SORT_DIRECT_WRITES (From Oracle8i onwards direct writes are always used for sorts) See for things you can do to reduce contention in the buffer cache. cache buffers chains latches Individual block contention can show up as contention for one of these latches. Each cache buffers chains latch covers a list of buffers in the buffer cache. If one or two child latches stand out from V$LATCH_CHILDREN then:In Oracle8: SELECT File# , dbablk, class, state FROM x$bh WHERE hladdr=&ADDR_OF_CHILD_LATCH ;In Oracle7: SELECT dbafil FILE# , dbablk, class, state FROM x$bh WHERE hladdr=&ADDR_OF_CHILD_LATCH ; If this list is short (3 to 10 buffers) then one of the buffers in this list is probably very hot - ie: suffers from lots of concurrent access attempts. Repeatedly monitoring X$BH for this latch should show which blocks are always there and which are transient. In Oracle8i there are often far fewer cache buffers chains latches (especially with large buffer caches) and so there can be many buffers covered by a single hash latch. There is a touch-count column in X$BH in Oracle8i (X$BH.TCH) which can be used to see the HOT buffers. Hot buffers will typically have a high touch count.There is also a V$LATCH_MISSES view which may be of help to Oracle Support in more obscure cases: SELECT WHERE, SLEEP_COUNT, WTR_SLP_COUNT, LONGHOLD_COUNT FROM v$latch_misses WHERE parent_name=&ADDR_OF_PROBLEM_LATCH ORDER BY 1 ;This shows where-abouts in the code the latch holder and latch waiters were when the latch was requested but not obtained immediately. In some releases V$LATCH_MISSES does not have the WTR_SLP_COUNT and LONGHOLD_COUNT columns. The view does not exist prior to Oracle7.3. Related:Tracing User sessions 8.1.7和9i版本的v$latch_children的区别SQL connect sys/chang_on_installhdbgsmpcsConnected to Oracle9i Enterprise Edition Release 9.2.0.1.0 Connected as SYSSQL select name,count(*) from v$latch_children group by name;NAME COUNT(*)- -SQL memory manager workarea list latch 67TLCR meta context 1Token Manager 3buffer pool 8cache buffers chains 1024cache buffers lru chain 8channel handle pool latch 1channel operations parent latch 55checkpoint queue latch 16commit callback allocation 11done queue latch 1enqueue hash chains 2global tx hash mapping 47granule operation 1job workq parent latch 3ksfv messages 2latch wait list 2library cache 3library cache pin 3library cache pin allocation 3NAME COUNT(*)- -longop free list parent 2message pool operations parent latch 15msg queue latch 1name-service namespace bucket 32parallel query alloc buffer 4parallel query stats 2post/wait queue 4redo allocation 1redo copy 4row cache objects 22session idle bit 2session queue latch 1session switching 4shared pool 7sim partition latch 1simulator hash latch 32simulator lru latch 8transaction allocation 11undo global data 3virtual circuit buffers 170virtual circuit queues 1041 rows selectedSQLConnected to Oracle8i Enterprise Edition Release 8.1.7.4.1 Connected as internalSQL select name,count(*) from v$latch_children group by name;NAME COUNT(*)- -Token Manager 2cache buffers chains 1024cache buffers lru chain 1channel handle pool latch 1channel operations parent latch 4checkpoint queue latch 1done queue latch 1enqueue hash chains 1global transaction 10global tx hash mapping 11ksfv messages 2latch wait list 1library cache 1message pool operations parent latch 4msg queue latch 1parallel query stats 2redo copy 2session idle bit 1session queue latch 1session switching 4NAME COUNT(*)- -simulator hash latch 4simulator lru latch 1temp table ageout allocation latch 123 rows selected书签转到末尾文档 ID: 注释:22908.1主题: What are Latches and What Causes Latch Contention类型: REFERENCE状态: PUBLISHED内容类型: TEXT/X-HTML创建日期: 29-MAR-1995上次修订日期: 18-MAY-2004Purpose: This bulletin focuses on latches. It attempts to give a clear understanding of how latches are implemented in the Oracle RDBMS and what causes latch contention. The information provided can be used in tuning the various kinds of latches discussed. Contents: 1. What is a Latch? 2. Latches vs Enqueues 3. When do we need to obtain a latch? 4. Latches request modes? 5. What causes latch contention? 6. How to identify contention for internal latches? 7. Useful SQL scripts to get latch information 8. List of all the latches 9. List of latches that are of most concern to a DBA 10. Tuning _SPIN_COUNT (_LATCH_SPIN_COUNT in Oracle7) WHAT ARE LATCHES AND WHAT CAUSES LATCH CONTENTIONThe Oracle RDBMS makes use of different types of locking mechanisms. They are mainly latches, enqueues, distributed locks and global locks (used in parallel instance implementations). This bulletin focuses on latches. It attempts to give a clear understanding of how latches are implemented in the Oracle RDBMS and what causes latch contention. The information provided can be used in tuning the various kinds of latches discussed. 1. What is a latch? Latches are low level serialization mechanisms used to protect shared data structures in the SGA. The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long. A latch is a type of a lock that can be very quickly acquired and freed. Latches are typically used to prevent more than one process from executing the same piece of code at a given time. Associated with each latch is a cleanup procedure that will be called if a process dies while holding the latch. Latches have an associated level that is used to prevent deadlocks. Once a process acquires a latch at a certain level it cannot subsequently acquire a latch at a level that is equal to or less than that level (unless it acquires it nowait).2. Latches vs Enqueues Enqueues are another type of locking mechanism used in Oracle. An enqueue is a more sophisticated mechanism which permits several concurrent processes to have varying degree of sharing of known resources. Any object which can be concurrently used, can be protected with enqueues. A good example is of locks on tables. We allow varying levels of sharing on tables e.g. two processes can lock a table in share mode or in share update mode etc. One difference is that the enqueue is obtained using an OS specific locking mechanism. An enqueue allows the user to store a value in the lock, i.e the mode in which we are requesting it. The OS lock manager keeps track of the resources locked. If a process cannot be granted the lock because it is incompatible with the mode requested and the lock is requested with wait, the OS puts the requesting process on a wait queue which is serviced in FIFO. Another difference between latches and enqueues is that in latches there is no ordered queue of waiters like in enqueues. Latch waiters may either use timers to wakeup and retry or spin (only in multiprocessors). Since all waiters are concurrently retrying (depending on the scheduler), anyone might get the latch and conceivably the first one to try might be the last one to get.3. When do we need to obtain a latch? A process acquires a latch when working with a structure in the SGA (System Global Area). It continues to h