a new feature of 11G: direct read in case of a table bigger than %2 of buffer cache

An excellent article : http://coskan.wordpress.com/2011/06/24/plan-is-same-wait-profile-is-different/

"From the ouput above, you can see PRD database is doing direct path reads and QA database is using db_file_scattered reads and PRD is 3 times slower for each run. Somehow oracle favours doing 11G direct path implementation for full table scan (Links for direct path Doug Burns,Alex Fatkulin, Dion Cho ) on PROD. If you read the links you can see that this has to do something with the memory the buffer cache and small table threshold (for more information about this threshold Charles Hooper-great discussion with Jonathan Lewis on comments, Tanel Poder )."

There have been changes in 11g in the heuristics to choose between direct path reads or reads through buffer cache for serial table scans.
In 10g, serial table scans for "large" tables used to go through cache (by default) which is not the case anymore in 11g (where this decision to read via direct path or through cache is based on various stats).

Niciun comentariu:

Trimiteți un comentariu