The following question appeared in response to my comments in a posting on Bind Variables:
Shared pool is where the SQL code and the data dictionary lives. Isn’t this structure ‘mostly’ READ_ONLY? Why latch/lock? Why do you care to latch? What will be corrupted in the library cache? Why does Oracle have to “protect” it, as you say above?
It’s a good question, so I’ve decided to write a note about it, rather than replying directly to the comment.
A lot of the work done by Oracle uses linked lists. For example, each “cache buffers chain” is a linked list, and the reason you grab the cache buffers chains latch is to make sure that the chain (linked list) that you want to traverse does not get damaged (by someone else adding or removing an entry) as you walk along it.
So the latching for data blocks has very little to do with updating data, it’s essentially about finding out whether the block you want is in memory, or getting it safely into memory if it isn’t already there.
The latches for the library cache and shared pool are the same. You acquire a library cache latch when you want to walk along a linked list of objects in the library cache to find, or insert, an object. (In the case of the library cache, you tend to talk about library cache hash buckets, rather than library cache chains).
The shared pool latch covers the lists of chunks of free memory; if you need a chunk of free memory you acquire the latch so that you can take a piece of memory off a list and use it. If you free up some memory (by kicking something out of the library cache) you grab the shared pool latch so that you can safely add the free chunk to the correct chain in the shared pool. (Of course, you will previously have grabbed the library cache latch to knock the object out of a library cache hash bucket to free the memory it was using)
There are lots more details and, for a little more information, you might want to look at a couple of the notes that I originally wrote for the Dizwell Knowledge Base on this and related topics.