From what I can figure out, they kick in for Full Table Scans (FTS), and only in certain cases.. You can turn them off with event 10949. They are affected by the size of the buffer cache, how big the table is, and how many blocks for the table are already in memory. Oracle has a "secret sauce" to determine when to kick in. I haven't had any luck finding the "secret sauce", just information from others on what the ingredients are, not the amounts of each.
There are advantages to them
- They are much faster than conventional db file sequential read
- They won't age blocks out of memory that you might want to keep
Disadvantages
- If the same query is executed again, the blocks must be read from disk again.
- Running a FTS on a table in multiple sessions won't share blocks, and can cause a large amount of I/O.
I also found that an object is much more likely to be using direct path reads immediately after Startup, since there are no blocks currently cached (thanks Jonathon Lewis for this tidbit!)
Here is as much additional information as I can find on this topic
- http://oraclue.com/2009/07/17/direct-path-reads-and-serial-table-scans-in-11g/
- http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html
- http://dioncho.wordpress.com/2009/07/21/disabling-direct-path-read-for-the-serial-full-table-scan-11g/
I also stated asking some Exadata experts, on how Direct path reads relate to exadata. It seems that with Exadata, with Caching at both the DB layer, and the storage layer, along with the infiniband connection to storage, Direct path reads are not an issue. Exadata will cache at enough layers, that a Direct path read from the flashcache on the storage is fast enough to make it worthwhile.. It's almost as is Exadata was made to best utilize direct path reads, and other storage configurations will suffer from bottlenecking... Coincidence ?? you decide.
I will let all know how I make out with them.