Re: "direct path read" and "db file sequential read" used for full table scans in 11g
Date: Fri, 31 Aug 2012 01:39:52 +0300
The direct path read thingy affects only full table (segment) scans. So even with PX, if you get a parallel index range scan (on partitioned index) for example, you end up with good old buffered single block reads. If you set _serial_direct_read = true, then it's still adaptive. Set it to ALWAYS (18.104.22.168+ I think) and it should force the direct path read behavior.
If you want to learn more about full table scans (including direct path reads), there's a cool little video here :)
Oh, I forgot, consider this reply also as an announcement of the awesome http://Enkitec.TV (no need to watch MTV anymore, youngsters! :P)
-- *Tanel Poder* Blog - http://blog.tanelpoder.com App - http://voic.ee On Fri, Aug 31, 2012 at 1:23 AM, Sayan Malakshinov <xt.and.r_at_gmail.com>wrote:Received on Thu Aug 30 2012 - 17:39:52 CDT
> About 1: have you tried to decrease _small_table_threshold? What size
> of your tables on which you testing?
> About 2: have you tried to flush buffer_cache?
> On Fri, Aug 31, 2012 at 1:47 AM, Allen, Brandon
> <Brandon.Allen_at_oneneck.com> wrote:
> > Question #3 is open again
> > According to the docs the answer I thought I'd found would only explain
> what I'm seeing if I had the parameter parallel_degree_policy=AUTO, but I
> just checked and it is set to MANUAL, so the parallel queries should still
> be using direct path reads according to the doc:
> > SQL> show parameter PARALLEL_DEGREE_POLICY
> > NAME TYPE VALUE
> > ------------------------------------ ----------- ------
> > parallel_degree_policy string MANUAL