To recap, the following were observed for 11G:
- Serial direct path reads start at _small_table_threshold*5.
- Serial direct path reads stop when 50% of the table blocks are cached.
- Serial direct path reads stop when 25% of the table blocks are dirty.
To refresh the memory, here is how the methodology works (all code is available in the 11G post).
The function grows a segment in a loop, performing a full table scan and using physical reads direct statistic as an indicator. Every iteration buffer cache is flushed to make sure cached blocks do not affect the numbers.
Stop cached threshold
A loop is used to cache more and more blocks from a table followed by a full table scan. Again, physical reads direct statistic is used to see when serial direct path reads no longer happen. Buffer cache is flushed at the beginning of the test.
Stop dirty threshold
Similar to the above but blocks are updated instead of just being cached.
11GR2 threshold is indeed more aggressive and starts at _small_table_threshold (i.e. five times lower compared to 11G). There seems to be no change to the rest of the numbers. Behavior appears to be the same on both Exadata and non-Exadata systems.
Bottom line is 11GR2 starts doing serial direct path reads earlier and stops doing these at the same time as 11G did.
All testing were done on 18.104.22.168