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:
Although these examples are unusual, they nevertheless
illustrate the use of V$LATCH_MISSES to find out whether latch
contention is due to long latch retention or excessive latch demand,
and also to determine where in the Oracle kernel the problem exists.
With that information at hand you are half-way to understanding and
resolving many of the most complex Oracle performance tuning problems.
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.
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.
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.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-82843/，如需转载，请注明出处，否则将追究法律责任。