What is this 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 |