Re: "direct path read" and "db file sequential read" used for full table scans in 11g

  • From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
  • To: xt.and.r@xxxxxxxxx
  • 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 (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 :)

http://enkitec.tv/2012/05/19/oracle-full-table-scans-direct-path-reads-object-level-checkpoints-ora-8103s/

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@xxxxxxxxx>wrote:

> 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@xxxxxxxxxxx> 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
> >
>
>
>
>
>


--
//www.freelists.org/webpage/oracle-l

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 08-2012