These statistics may not be entirely accurate, because they are not always updated under the protection of a latch. Nevertheless, they are not only sufficient to show whether a latch contention problem is due to long retention of a latch or high demand for that latch, but they also show exactly which location in the Oracle kernel is causing the problem. Thus, the V$LATCH_MISSES data is an excellent fingerprint for any latch contention problem. Here are two rather unusual examples from tuning assignments that we have completed in the last month:

1. In the first case there was contention for both the shared pool and library cache latches. This combination is very familiar, but in this case much of the problem was at kghupr1 which is very unusual. In kghupr the shared pool latch is taken to unpin a recreatable chunk of memory and add it to the head of one of the shared pool LRU lists. The latch was not being retained for a long time, therefore we concluded that there must have been unusually high demand for the latch at this point. What would cause a carefully written application that makes consistent use of bind variables to discard more chunks of memory than it reuses? The only possibility that we could think of was if the application was consistently rebinding each cursor prior to re-execution. That would cause chunks of bind metadata to be discarded, and never reused. A quick question to the developers revealed that this was indeed the case. The solution was to modify the application to refrain from rebinding unless the location, rather than just the values, of the bind variables had changed.

2. In another case there was contention for the library cache latches. V$LATCH_MISSES showed that there was long retention of these latches at kglic. In kglic the library cache hash chains and segmented arrays are scanned, so it looked like this would be a problem caused by one or more very long linked lists in the library cache. However, we were able to disprove that hypothesis by dumping the contents of the library cache during a latch contention episode. There were no long linked lists! In the absence of high CPU usage that could only mean one thing, namely that parts of the library cache had been paged out by the operating system and that the long latch retention was associated with reading them back in from swap. A reduction in memory usage resolved the problem.