Concurrency

When you get into big, busy, systems one of the final barriers you have to overcome is the concurrency issue; and after you’ve designed and fiddled and tweaked everything else it’s latch acquisition that is often the final barrier to extreme levels of concurrency.

If two people want to execute the same SQL statement there’s some interference in the library cache as they both hit the relevant “library cache” latch; if they both want to view the same data block there’s some interference in the buffer cache as they both hit the relevant “cache buffers chains” latch.

In 10.2, Oracle introduced their “mutex” mechanism to the library cache to reduce the contention and allow increased concurrency in the library cache.

In 11g, Oracle has introduced a new “consistent get” mechanism which reduces contention in the buffer cache.

If you check the statistics relating to logical I/O, you will find that there is a new “fastpath” mechanism for consistent gets. Here, for example, is a subset of the stats for a query that used to show roughly 100,000 “consistent gets” and 200,000 “buffer is pinned count” in 10g:

consistent gets                           2,886
consistent gets from cache                2,886
consistent gets from cache (fastpath)     2,301
consistent gets - examination               306
buffer is pinned count                    1,421

Most significantly, the dramatic drop in the number of consistent gets resulted in a drop from 200,000 to about 4,000 for the cache buffers chains latch, with a noticeable drop in the CPU usage.

I haven’t tried to figure out what mechanisms have been changed – possibly every buffer header has it’s own mutex; maybe buffers are copied to the session’s PGA if the circumstances seem appropriate.

For extreme systems this enhancement is worth testing carefully, and could be a good reason for thinking seriously about upgrading to 11g.

For more examples of the change, take a look at Alex Fatuklin’s blog.