In OTN was once asked whether the optimizer takes the size of the buffer cache into account swhen creating the execution plan . Under the usual disclaimer (it could in the cost review with incorporated), I would say: no. How should the size of the cache be considered anyway? If I have a larger buffer cache, is more likely that a segement X is in the cache, so I’m doing an index access? (I argue here analogous to parameters OPTIMZER_INDEX_CACHING.) That’s not a good logic. The size Buffer caches is no a safe indicator of whether a particular segment actually in the cache or not. As will be seen later, the size of the cache may be even an argument for the full table scan.
To create an execution plan, the selectivity estimate is the key factor.
However, how an existing execution plan is being executed, is another matter. Here the execution enigine makes the final decision.
For example, a full table scan (FTS) is often executed doing “direct path reads”. This means that inevitable a physical I / O will to be done. (For the database, it is a physical I / O even if the result comes from the cache file system.)
But if the execution engine detects that a table is almost fully represented in the buffer cache, it can switch the execution from a “direct path read” to a “scattered read” The “scattered read” in contrast to “direct path read” will benefit from the buffer cache.
Tanel Poder wrote a good, but a somewhat older explanation of this concept : Optimizer statistics-driven direct path read decision for full table scans .
In short, there are two prerequisites that must be fulfilled that the execution engine switches to “scattered reads”.
- The buffer cache must be large enough to cache the segment which is the subject of the FTS
- The segment must be actually almost completely cached
The first point can be so easily checked. The analysis showed the following: The table was about 25GB large in production and in the migration.
The buffer cache was currently 55 GB in production. In the migration it was only 5 GB in size.
Now we can conclude with certainty: In the production, the table colud be cached to 100% of it’s size. Theoretically, the runtime engine can therefore initiate a scan in memory. In the miragtion this is certainly not possible.
Was the table now cached to almost 100% in production?
To clarify this question the below statement, which determines the number of cached blocks of a table can be used.
If the so calculated number is almost identical to the number of blocks the table (according to my tests> 90%, but no guarantee) “scattered reads” will be used.
SELECT sum(num_buf)
FROM X$KCBOQH x, dba_objects o
WHERE x.obj#=o.object_id
AND object_name='my table'
;
After that the FTS was fast also in mirgration.
Some remarks:
Frits Hoogland published a newer, deeper analysis of the subject :
Investigating the full table direct path / buffered decision