RE: different physical access method because of disabling Automated Memory Management?

  • From: "Tanel Poder" <tanel@xxxxxxxxxx>
  • To: <martin.a.berger@xxxxxxxxx>, "'Mark W. Farnham'" <mwf@xxxxxxxx>
  • Date: Wed, 7 Jan 2009 10:42:37 +0200

Btw, as far as I know the serial direct read is not a CBO decision, it's
done at lower level.
The TABLE ACCESS rowsource evaluates the table size (up to HWM) every
execution. Thus it should be possible to get direct reads and cached reads
during different executions of the same child cursor.
Alex Fatkulin's test case indicated that Oracle also takes the number of
blocks already in cache (and dirty) into account. This is probably done by
looking into KCB object queue header and walking the list of buffers there.
In 10.2+ the KCB object queue is externalized in two X$ tables:
X$KCBOQH - KCB Object Queue Header (and there's a NUM_BUF column showing
number of buffers of a particular object currently in cache)
X$KCBOBH - KCB Object Queue Buffer Header (shows an entry of all individual
buffers in KCB object queue).

Other related posts: