RE: different physical access method because of disabling Automated Memory Management?
Date: Wed, 7 Jan 2009 10:42:37 +0200
Message-ID: <D46AB5AB00874EA38CC71B896C4E393E_at_porgand>
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).
Tanel.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 07 2009 - 02:42:37 CST