Some concepts about Shared pool, lock and latches

In an interview, I was asked a question about how Oracle protects the shared pool if multiple users need to access and modify same memory location. (the exact question I can not recall, but it is about locks/latches). I failed to explain well.

Here are some reading notes about this topic.

What is "library cache lock"?

Oracle document describes "library cache lock" as one of the wait events as follows:

This event controls the concurrency between clients of the library cache.
It acquires a lock on the object handle so that either:
- One client can prevent other clients from accessing the same object
- The client can maintain a dependency for a long time which does
not allow another client to change the object

This lock is also obtained to locate an object in the library cache.


Library cache
* includes
- shared SQL areas
- private SQL areas
- PL/SQL procedures and packages
- Control structures: locks and library cache handles

Each SQL statment
* represented by
- a shared SQL area
- a private SQL area

Shared SQL area
* contains
- parse tree
- execution plan

Private SQL Area
* divided into
- persistent area: e.g. bind information
- run-time area
* located
- PGA or
- SGA (in the case of shared server)


Here is an article explaining the Oracle latch: http://www.pafumi.net/Oracle_Latches.html


Shared pool latch and libary cache latch

In the v$latch view, there are latch names such as shared pool and library cache:

Someone explained:

- Shared pool latch:

Shared pool latch is acquired to request/release space from the
shared pool free memory area and released immediately after that.

- Library cache latch

Library cache latch is held while searching for a parent cursor and
subsequent child cursor with similar attributes with parent cursor
pinned.