In the initial part of this series I've explained some details regarding logical I/O using a Nested Loop Join as example.
To recap I've shown in particular:
- Oracle can re-visit pinned buffers without performing logical I/O
- There are different variants of consistent gets - a "normal" one involving buffer pin/unpin cycles requiring two latch acquisitions and a short-cut variant that visits the buffer while holding the corresponding "cache buffers chains" child latch ("examination") and therefore only requiring a single latch acquisition
- Although two statements use a similar execution plan and produce the same number of logical I/Os one is significantly faster and scales better than the other one