Re: "direct path read" and "db file sequential read" used for full table scans in 11g
From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Fri, 31 Aug 2012 01:39:52 +0300
Message-ID: <CAMHX9JKOtmt_fG27OsNv86aduaNT+EEANBVJwtx7JzpQLo6DMg_at_mail.gmail.com>
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 (11.2.0.2+ I think) and it should force the direct path read behavior.
Date: Fri, 31 Aug 2012 01:39:52 +0300
Message-ID: <CAMHX9JKOtmt_fG27OsNv86aduaNT+EEANBVJwtx7JzpQLo6DMg_at_mail.gmail.com>
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 (11.2.0.2+ 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:
> http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams179.htm#REFRN10310
> >
> > SQL> show parameter PARALLEL_DEGREE_POLICY
> >
> > NAME TYPE VALUE
> > ------------------------------------ ----------- ------
> > parallel_degree_policy string MANUAL
> >
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-l