Serial direct path reads were first introduced in Oracle 11G which were noticed by many customers (in both positive and negative ways) who upgraded from the earlier releases. Back then I did a quick write up on the subject to try and tackle some of the key variables which affect the behavior.
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.
Since then, many people noticed that 11GR2 seems to be more aggressive on the thresholds so I decided to re-run the tests and see what changed. I'm also going to run the same tests on the Exadata to see whether it changes anything compared to a normal 11GR2 database.
Alex Fatkulin's Blog: Serial direct path reads in 11GR2 and Exadata environments
Starting from Oracle 11.2.0.2, things have changed a little. Instead of making the direct path read decision based on the actual segment block count extracted from the segment header, Oracle actually takes this number from TAB$.BLKCNT (dba_tables.blocks) or TABPART$.BLKCNT, IND$.LEAFCNT etc.
Optimizer statistics-driven direct path read decision for full table scans (_direct_read_decision_statistics_driven) | Tanel Poder: SQL Performance Tuning, System Troubleshooting and Training
