Oracle DBA Lesson 8A — Follow a Block Into the Buffer Cache

Oracle DBA · Lesson 8A

Follow a Block Into the Buffer Cache

One request. Two possible read paths. A logical read is buffer access — measure a bounded interval in the same session before you diagnose from a hit ratio.

Watch on YouTube

Oracle DBA Lesson 8A — Follow a Block Into the Buffer Cache

AI Ops Pro

One request, two read paths

A server process needs one block for an employee lookup. It looks in the buffer cache first:

• Cache hit — a usable copy is already in memory → logical read (buffer access).
• Cache miss — no usable copy → physical I/O from the data file into a buffer, then logical access of that buffer.

A logical read describes buffer access. It is not a claim that nothing touched storage on the way there.

Measure a bounded interval in the same session

Before diagnosing, sample the same three counters, run the work, sample again, then take the delta:

• db block gets
• consistent gets
• physical reads

DELTA = AFTER − BEFORE, all in the same session. The sampling query itself adds a little work — keep the interval tight around the statement you care about.

SELECT n.name, m.value
FROM   v$mystat m
JOIN   v$statname n ON n.statistic# = m.statistic#
WHERE  n.name IN (
         'db block gets',
         'consistent gets',
         'physical reads'
       )
ORDER  BY n.name;

Run once (BEFORE), execute the employee lookup, run again (AFTER). Subtract. Never invent live sample numbers — read them from your instance.

Read consistency may need another block image

The current block in cache can hold newer changes than your query snapshot. When that happens, Oracle may rebuild a consistent-read copy from undo so the session sees the older version it needs. A buffer-cache operation can include reconstruction from undo — not only a simple “find the one block and return.”

Not every physical read uses the ordinary cache path

Ordinary buffered reads go data file → buffer cache → server process. Some workloads use direct reads that bypass the ordinary buffer-cache path. Physical reads have multiple causes. Hit ratio ≠ diagnosis — keep the read-path model accurate and read the path together with the evidence.

Can a logical read involve physical I/O?

Yes. On a miss, storage I/O can happen first to bring the block into a buffer; the process then does a logical read of that buffer. Read the path and the evidence together.

Gotchas

• Logical read = buffer access, not “no disk ever involved.”
• Measure BEFORE/AFTER in the same session; deltas beat global ratios for one statement.
• Consistent-read reconstruction from undo is still buffer-cache work.
• Direct reads can bypass the ordinary cache path — do not treat hit ratio as a diagnosis.

Quick quiz

Choose one answer per question, then submit. You’ll see the correct answer and a short why.

1. What does a logical read describe in this lesson?

Only a mechanical disk seek that never touches memory Buffer access in the buffer cache A commit that waits for DBWn A direct path write to the FRA

2. Best way to attribute buffer work to one employee lookup?

Read the instance-wide buffer hit ratio once Flush the buffer cache, then guess BEFORE/AFTER samples of db block gets, consistent gets, and physical reads in the same session; take the delta Only look at db_cache_size

3. Current block is at SCN 240; the query snapshot needs SCN 220. What may happen?

A consistent-read copy can be reconstructed from undo The session must always wait for DBWn to rewrite the data file Logical reads are impossible until the next checkpoint The buffer cache is deleted and rebuilt from scratch

4. Why is “hit ratio ≠ diagnosis” a fair warning here?

Because hit ratio is always exactly 100% Because physical reads never happen on Oracle Because only KEEP pool statistics matter Physical reads have multiple causes (including direct reads); a ratio alone does not name the path

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro