Cuihua pointed out that there is a mistake in a latch level description in Janathan Lewis’s new book “Oracle Core”. Here is his blog. In page 116 of the section “Loading a Hash Chain” in Chapter 5.

SQL> select     name, level#
  2  from       v$latch
  3  where      name in ('cache buffers lru chain','cache buffers chains')
  4  /

NAME                               LEVEL#
-------------------------------    ------
cache buffers lru chain                 2
cache buffers chains                    1

2 rows selected.

The cache buffers chains latch has a lower level than the cache buffers lru chain latch, so we can’t request the cache buffers lru chain latch in willing-to-wait mode if we’re already holding the cache buffers chains latch. Think about what this means: we’re holding the cache buffers chains latch (which I will call the hash latch for the rest of this subsection) because we’ve just searched the hash chain for a buffer and discovered that, for whatever reason, we need to add another buffer to the chain. So we have to acquire the cache buffers lru chain latch (which I will call the lru latch for the rest of this subsection) to move a buffer from the REPL_AUX list to the midpoint of the REPL_MAIN list; but we can’t request it in willing-to-wait mode because we’re already holding a lower-level latch.

…
But if you can’t get the lru latch with an immediate get, you have to drop the hash latch, get the lru latch, and then get the hash latch again.
…

Continue reading →