How Oracle Handles Physical & Logical Reads with Latches and Buffer Pins

Physical and Logical Reads in Oracle

Oracle locates a data block in the buffer cache by hashing the block address, finding the corresponding hash bucket, searching the bucket’s hash chain for a buffer header (BH), and then using the buffer address (BA) to access the buffer. If the BH is not present, the block is read from the data file (physical read) and a new BH is inserted into the hash chain.

Hashing and Bucket Lookup

Compute HASH(FILE#, BLOCK#) → hash value (HV).

Select the hash bucket identified by HV.

Traverse the bucket’s hash chain to locate a BH whose address matches the target block.

Logical Read

When the BH is found, read its buffer_address (BA).

Use BA to fetch the buffer from the buffer cache.

Physical Read

If no BH exists, read the block from the data file (DBF) into a new buffer.

Insert the new BH into the appropriate hash chain.

Access the buffer via its BA.

Concurrency Control: CBC Latch and Buffer Pin

Oracle protects the hash chain and individual buffers with two lightweight synchronization primitives:

CBC latch (Cache Buffer Chain latch) guards modifications to the hash chain. It can be acquired in shared or exclusive mode.

Buffer pin guards the buffer header while the buffer is accessed. It also supports shared (read) and exclusive (write) modes.

When CBC Latch Is Acquired in Shared Mode

Reading root and branch blocks of a non‑unique index (leaf blocks require exclusive latch).

Reading any block of a unique index (root, branch, leaf, and table blocks) because root/branch are read‑heavy and rarely modified.

When CBC Latch Must Be Exclusive

Any operation that modifies data (INSERT, UPDATE, DELETE, index split, etc.).

Read operations not covered by the shared‑mode cases above.

Buffer Pin Modes

Shared : read the buffer via BA.

Exclusive : write the buffer via BA.

No pin : certain reads that do not involve root/branch modifications (e.g., leaf reads of a unique index) can skip pinning.

Queue Lists on a Buffer Header

Users list : processes currently holding the buffer in a compatible mode.

Waiter list : processes blocked because they require an incompatible mode.

Step‑by‑Step Procedures

1. Non‑Unique Index Read (root/branch blocks)

Compute the hash of the target block address.

Locate the hash bucket using the hash value.

Acquire the bucket’s CBC latch in exclusive mode (required for root/branch reads of non‑unique indexes).

Search the hash chain for the matching BH.

Set the buffer pin on the BH to shared mode.

Release the CBC latch.

Use the BH’s BA to locate the buffer in the cache.

Copy the buffer contents to PGA (process global area).

Re‑acquire the CBC latch in exclusive mode to clear the pin.

Reset the buffer pin to no lock (0).

Release the CBC latch.

2. Unique Index Read (any block)

Compute the hash of the target block address.

Locate the corresponding hash bucket.

Acquire the bucket’s CBC latch in shared mode.

Search the hash chain for the BH.

Obtain the buffer via the BH’s BA.

Read the buffer into PGA.

Release the CBC latch.

3. Write Operation (INSERT/UPDATE/DELETE)

Compute the hash of the target block address.

Locate the hash bucket.

Acquire the bucket’s CBC latch in exclusive mode.

Search the hash chain for the BH.

Set the buffer pin on the BH to exclusive mode.

Release the CBC latch.

Locate the buffer via BA.

Generate redo entries for the modification.

Apply the changes to the buffer.

Re‑acquire the CBC latch in exclusive mode.

Reset the buffer pin to no lock (0).

Release the CBC latch.

Illustrations