What is a "Latch"?Latches are a kind of very short "Locks" to protect data structures in the SGA. Other than enqueues, for latches there exists no FiFo wait queue - so waiting processes either "spin" (in multi-processor environments only) or "sleep", and retry their request later. As a side-effect, there's no special order in processing of requests: in fact, the first process encountering a special latch wait could be the last one getting the latch. Connected with each latch is its level - to avoid deadlocks, a process already holding a latch cannot obtain another latch of the same or subordinate level. What is this table about?Pct Get Misses and Pct NoWait Misses should be low. Pct Get Miss is the percentage of time a latch was requested (in a willing-to-wait mode) and not obtained immediately. For latches requested in No-Wait mode, Pct NoWait Misses is a percentage based on the number of times a latch was requested in NoWait mode, and the latch request was not successful. For willing-to-wait latch gets, also examine the Avg Slps/Miss statistic which shows the average number of times a server process had to sleep before being able to acquire the latch. This statistic should be low. Look at the raw sleep data in the Latch Sleep Breakdown section, and identify latches which are obtained by spinning or by sleeping, with sleeping being the most expensive method of getting the latch. The Latch Miss Sources report is primarily useful to Oracle Support staff. The data here is used to identify the code which was executing at the time the latch was not obtained (i.e. "missed"). Three of the most common latches waited for are the shared pool, library cache and cache buffers chains latches. Latch contention is not usually a problem in itself, but is symptomatic of other issues. For example, contention on the shared pool and library cache latches can often be symptomatic of unnecessary parsing, or of very high rates of logon/logoffs initiated by middle-tier software. Unnecessary parsing can be can be avoided by writing sharable SQL which uses bind variables. Middle tier software can be designed to connect to the database once and maintain the connections, rather than connect/disconnect from the instance for each database call. Latch contention for these latches can also be caused by loading large PL/SQL packages into the shared pool; to avoid this activity, look at pinning these packages to avoid them aging out. Contention on a cache buffers chains latch can sometimes be caused by very heavy access to a single block - this would require identifying the hot block, and then why the block is being contended for. Potential Fixes for indicated Latch problems
Again, these are potential fixes, not general solutions. More information can be found on the help page about Latch Free Waits |