Oracle Database Discussions

Does anyone know how to control/predict the use of Adaptive Direct path Reads in 11g ?? For the unitiated, this is a new behavior of 11g, where if Oracle perceives a large amount of data is going to be "scanned", it switches to reading directly into the PGA, bypassing the SGA.

PROS -

-- Direct Path reads are much faster than db scattered reads.
-- These large reads (usually FTS) do not flush useful blocks out of the SGA.

CONS --

-- The results are not cached for the next query accessing them.
-- Multiple large reads of the same data, do not share blocks, and can overwhelm a system.

This last CON is the reason for this question.. I have an application that did 33 concurrent FTS of the same table . Since it did direct path reads for every session, none of the blocks were shared, and each process read the same blocks from disk directly into the PGA. What was the result ??? The storage team reported that the FA's were totally saturated, something they had never seen in their careeer. One little blade with 33 processes overwhelmed our infrastructure with a FTS.

Also, this makes it difficult to utilize Dbreplay. I am trying to compare the performance of the 10g optimizer to the 11g optimizer by changing the Optimizer_features_enabled.. This does NOT stop direct path reads from happening.. The PHV is the same regardless of whether it uses direct path reads or not.. This behavior can affect my results (good or bad), and thus obfuscate the interpretations of the performance when upgrading.