Adaptive Direct path reads

Topics about Databases, Backups, Tuning, Architecture, Systems Management, etc etc.

Post Reply
bgrenn
Posts: 91
Joined: Mon Mar 16, 2009 11:47 am
Location: Rochester, NY
Contact:

Adaptive Direct path reads

Post by bgrenn »

Well as you read the title, you are probably going huh ??  Until you upgrade to 11g, you probably just think direct reads are for reading temp only.. Well it all changes with 11g.  Full table scans of large tables turn from "db scattered read" to "direct path read"..  


What does this mean ?? well the good news is it runs about 4 times faster than the old fasion reads.. How does it do this ??  It bypasses the SGA.. Is this good ? probably.. especially for full table scans.. it doesn't force anything out of your buffer cache, and you get faster reads.  The only concern I would have is if you wanted to read a lot of data into memory, I don't know how to burn things into cache. I tried to disable it to do timings, but no luck.  Even with the optimizer set back to 10g it still does direct path reads.

Here is some great information on it.

http://afatkulin.blogspot.com/2009/01/1 ... at-is.html

http://shallahamer-orapub.blogspot.com/ ... irect.html

Happy tuning.
bgrenn
Posts: 91
Joined: Mon Mar 16, 2009 11:47 am
Location: Rochester, NY
Contact:

More information

Post by bgrenn »

Well, I've posted a second time because I have been busy following up on this feature.  I've posted to different groups, and the most interesting responses I've gotten have been from Jonathon Lewis.. He also has been posting on Direct path reads.. He told me he has seen 2 similar cases where parallel direct path reads have caused issues.. He also posted this yesterday..
http://jonathanlewis.wordpress.com/2010 ... ot-keeping/

He talkes about the Keep pool not working for large objects.. This was a way to keep objects from not doing direct path reads.

I also posted to Exadata news groups to find out how exadata affects direct path reads.  The answer for exadata is that since predicate pushdown is often used, a lot of the queries don't even return blocks.. Think of "select col1  from mytable".. The read for col1 is pushed down, and only col1 is returned rather than the blocks.  also with all the caching it doesn't matter since it will come from cache through infiniband.

I will continue to update as I learn more.
Post Reply