Index Scan und DB File Scattered Reads

Tanel Poder hat vor kurzem erläutert, wie es dazu kommen kann, dass ein index unique scan (oder auch ein index range scan) statt der erwarteten db file sequential reads das eher für FTS oder IFFS übliche Event db file scattered read hervorruft. Die kurze Antwort darauf lautet: Prefetching. Die etwas längere Erklärung zum Prefetching wäre dann:

it’s for buffer cache warmup, where after a restart or a significant buffer cache size increase Oracle decides to populate the empty buffer cache with any data as fast as possible. Oracle assumes that a random multiblock read doesn’t take that much more time than a random single block read anyway, so why not just read a bunch of additional blocks from around that single block that the index access wanted to read. The read operation will be slightly slower, but it might help to avoid multiple future single block reads as they are already in cache. This should affect things only when you restart the instance or significantly increase the buffer cache size. So on a steady state production system you shouldn’t see it.

Eine sehr viel ältere Erläuterung vom Herrn Poder zum gleichen Thema findet man in Anand's Blog.