direct path read か db file scattered read か

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's Performance & Troubleshooting blog