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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 7 Jan 2009 05:33:02 -0500
Message-ID: <619F8445ACCF4F49BE91D6409E8E408D_at_rsiz.com>

oops. pretty sure Tanel is right. The CBO would have already picked fts (or not), not the mechanism to achieve the fts. In my prior message I think if you substitute "fts algorithm" for CBO it essentially repairs the message. Sorry for the brain cramp.  

On the current cache conditions, if I read the thread correctly the poster was talking about a fresh restart. That is not a dispute of Alex's results, just ignoring cache contents for the repetitive restart test conditions.  

Thanks Tanel.  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder
Sent: Wednesday, January 07, 2009 3:43 AM To: martin.a.berger_at_gmail.com; 'Mark W. Farnham' Cc: 'oracle-l'
Subject: RE: different physical access method because of disabling Automated Memory Management?  

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-l

Received on Wed Jan 07 2009 - 04:33:02 CST