Entscheidungsgrundlagen für Direct Path Reads für FTS

Tanel Poder erklärt in seinem Blog, dass die für die Verwendung des direct path read Zugriffs im Rahmen von FTS-Operationen entscheidende Information der Anzahl von Blocks im Segment seit 11.2.0.2 aus den Objektstatistiken statt aus dem Segment-Header gezogen werden kann (zur Steuerung des Verhaltens dient der Parameter _direct_read_decision_statistics_driven). Diese Änderung hat auch Auswirkungen auf die Stabilität des Verfahrens:

[...] the direct path read decision is not done by the optimizer anyway and is a runtime decision done during every execution, every time any segment (including individual partitions) is scanned during query runtime. This is true for the old (up to 11.2.0.1) Oracle versions, where a direct path decision is done based on the actual, current block count in the segment header, thus the decision can suddenly change when a segment grows by a few blocks, crossing the _small_table_threshold calculation threshold. Perhaps due to performance stability reasons, this seems to have changed. 

My tests on 11.2.0.2 have so far shown that when using the new statistics-driven direct path read decisions, each segments’ block counts are stored somewhere in the compiled cursor and reused during next executions of it, even if the block count of the segment changes in the optimizer stats later on! This might result in somewhat better stability as long as you don’t gather new stats – and your buffer cache size (and already cached block counts) don’t change. However if the amount of cached blocks of a segment does change (due to other, index-based accesses for example), then the direct path decision can still change during runtime. It’s just the block counts which are stored in the cursor, but the other factors affecting the decision (buffer cache size, cached block counts) can still change.