Your understanding is wrong. The buffer cache is not static, and it is
very unlikely and also unnecessary the buffer cache is as big as the
database. It is unnecessary because some records will be queried often
and others never. However, as the buffer cache is likely smaller,
records will sooner or later get removed from the buffer cache.
This is one of the reasons Oracle executes a consistent get at every
fetch. Oracle gets every record in the state it was at the beginning of
your select statement.
If you select 100 records, and you fetch them one by one, you will
incur 100 consistent gets. If in the mean time the affected blocks in
the cache didn't change at all, you will see no additional physical
reads.
Hth
--
Sybrand Bakker
Senior Oracle DBA
Received on Mon Sep 19 2005 - 03:13:04 CDT
